美文网首页
SQL Basics: Simple table totalin

SQL Basics: Simple table totalin

作者: 是不及呀 | 来源:发表于2019-04-28 16:27 被阅读0次
    * *
    链接 SQL Basics: Simple table totaling
    难度 6kyu
    状态
    日期 2019-4-28

    题意

    题解1

    select rank () over (order by (total_points) desc)as rank, a.*
    from (
    select distinct (case when (clan ='') then '[no clan specified]' else clan end)as clan,
    sum(points) as total_points,
    count(distinct name) as total_people
    from people
    group by clan)a
    

    题解2-wrong

    select rank () over (order by (total_points) desc)as rank, a.*
    from (
    select distinct replace('clan','','no clan specified') as clan,
    sum(points) as total_points,
    count(distinct name) as total_people
    from people
    group by clan)a
    

    题解-LZY

    select rank() over (order by sum(points) desc) as rank, 
    CASE WHEN clan IS NULL OR clan='' THEN '[no clan specified]' ELSE clan END,
    sum(points) as total_points, count(*) as total_people
    from people
    group by clan
    

    相关文章

      网友评论

          本文标题:SQL Basics: Simple table totalin

          本文链接:https://www.haomeiwen.com/subject/iehjnqtx.html