A表
ID NAME MEMO total
123 FF WW 88
123 FF WG 45
456 FF AD 77
123 SS DG 54
456 FF AS 67
123 SS AE 54
123 SS EE 100
123 kk EF 66
......
现在想得到如下结果
ID FF SS OTHER
123 2 3 1
456 2 0 0
分组后数量问题
select id,sum(case name when 'FF' then 1 else 0 end) FF,
sum(case name when 'SS' then 1 else 0 end) SS,
sum(case when name not in('FF','SS') then 1 else 0 end) OTHER
from a group by id;
分组后得到每组前几行
select * from (
select tt.*,row_number() over (partition by tt.ID order by tt.total DESC)rn from A tt
)t
where rn <3
结果如下:
123 SS EE 100
123 FF WW 88
456 FF AD 77
456 FF AS 67