一个表sample,有两个字段A 、B(B只有三种取值1、2、3)：要求，当A=a1 或A=a2时，求出 B 取每一个可能值的记录数量
sample表如下
A B
----------
a1 1
----------
a2 2
----------
a1 1
----------
a1 1
-----------
上例：A=a1时，B=1的记录数是3，B=2记录数是0，B=3的记录数是0……
输出要求：
a1,3,0，0
a2,0,0, 1
---------------------------------------------------------------
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
---------------------------------------------------------------
select 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 X.A, isnull(count(T.B), 0), isnull(count(Y.B), 0), isnull(count(Z.B), 0) from
(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