设有表格如下: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]谢谢!
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]谢谢!
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
A
, sum(case when B=0 then 1 else 0 end) as B0
, sum(case when B=1 then 1 else 0 end) as B1
from test t
group by A
declare @t table ( A char(1),B tinyint)
insert into @t
select 'a',0 union all
select 'a',0 union all
select 'a',1 union all
select 'b',0 union all
select 'b',1 union all
select 'b',1 union all
select '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 B1
from @t group by A
-----------------------------------------
(7 行受影响)
A B0 B1
---- ----------- -----------
a 2 1
b 1 3(2 行受影响)