SQL Basics: Simple table totalin
2019-04-28 本文已影响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
