设有表格如下：
A——B
a——0
a——0
a——1
b——0
b——1
b——1
b——1
要求得到：
A——B0——B1
a——2 ——1
b——1 ——3
即：汇总出列［A］各种值分别出现列B=0和列B=1的次数。
如果不使用子查询，能否实现？
比如:
select [A],... from TABLE Group by [A]
谢谢！
------解决方案--------------------
- SQL code
select A , (select count(1) from test where B=0 and t.A = test.A) as B0 , (select count(1) from test where B=1 and t.A = test.A) as B1 from test t group by A
------解决方案--------------------
- SQL code
declare @t table ( A char(1),B tinyint)insert into @tselect 'a',0 union allselect 'a',0 union allselect 'a',1 union allselect 'b',0 union allselect 'b',1 union allselect 'b',1 union allselect 'b',1 select A,SUM(case when B = 0 THEN 1 ELSE 0 END ) AS B0, SUM(case when B = 1 THEN 1 ELSE 0 END ) AS B1from @t group by A-----------------------------------------(7 行受影响)A B0 B1---- ----------- -----------a 2 1b 1 3(2 行受影响)