一个高难度SQL语句，我想了三天三夜，或许根本就没有解

[netcoder]

一个表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

[j9988]

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

[OpenVMS]

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

[OpenVMS]

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

[OpenVMS]

last has a error:

select distinct 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 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

[OpenVMS]

---or
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)

[j9988]

OpenVMS(半知半解) 最后一个很有新意,妙

[pick2103]

这么简单的SQL也想了三天三夜？在csdn里找三小时说不定能找出三千三呐！

[liuzxit]

如果B的取值是固定那几個﹐還是比較好辦﹐確實有不少例子﹐不過B的取值是不確定的話﹐把記錄讀出橫行顯示好象是一個經典的問題﹐雖然有不少的討論﹐但似乎也不甚完美