A表
ID NAME MEMO
123 FF WW
123 FF WG
456 FF AD
123 SS DG
456 FF AS
123 SS AE
123 SS EE
123 kk EF
......
现在想得到如下结果
ID FF SS OTHER
123 2 3 1
456 2 0 0
ID NAME MEMO
123 FF WW
123 FF WG
456 FF AD
123 SS DG
456 FF AS
123 SS AE
123 SS EE
123 kk EF
......
现在想得到如下结果
ID FF SS OTHER
123 2 3 1
456 2 0 0
count(case name when 'SS' then 1 else 0 end) SS,
count(case when name not in('FF','SS') then 1 else 0 end) OTHER
from a group by id;
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;