a1,3,0,0
a2,0,0, 1 ?? 应是:a2,0,1,0 (a2 2)
select A,
sum(case when B=1 then 1 else 0 end) as B1,
sum(case when B=2 then 1 else 0 end) as B2,
sum(case when B=3 then 1 else 0 end) as B3,
from tablename group by A
a2,0,0, 1 ?? 应是:a2,0,1,0 (a2 2)
select A,
sum(case when B=1 then 1 else 0 end) as B1,
sum(case when B=2 then 1 else 0 end) as B2,
sum(case when B=3 then 1 else 0 end) as B3,
from tablename group by A
max(case B=1 then count(B) else 0 end) '1',
max(case B=2 then count(B) else 0 end) '2',
max(case B=3 then count(B) else 0 end) '3'
from sample group by A
max(case B=1 then count(B) else 0 end) '1',
max(case B=2 then count(B) else 0 end) '2',
max(case B=3 then count(B) else 0 end) '3'
from sample group by A
(select count(*) FROM sample where B=1 and A=a.A) '1',
(select count(*) FROM sample where B=2 and A=a.A) '2',
(select count(*) FROM sample where B=3 and A=a.A) '3'
from sample a
(select distinct A from sample ) as X
left join sample as T on X.A = T.A and T.B = 1
left join sample as Y on X.A = Y.A and Y.B = 2
left join sample as Z on X.A = Z.A and Z.B = 3
group by X.A
select A,
count(case when B=1 then B end) '1',
count(case when B=2 then B end) '2',
count(case when B=3 then B end) '3'
from sample group by A-- results:
A 1 2 3
----- ----------- ----------- -----------
a1 3 0 0
a2 0 1 0(2 row(s) affected)