表A 3个字段A1 A2 A3
S001 001 T
S002 001 F
S003 001 T
S004 002 T
S005 002 F
S006 001 T
S007 003 T要求统计A1的记录
代号 所有数据 为T数据 为F数据
001 4 3 1
002 2 1 1
003 1 1 0如果A2有其它不同值的继续显示
S001 001 T
S002 001 F
S003 001 T
S004 002 T
S005 002 F
S006 001 T
S007 003 T要求统计A1的记录
代号 所有数据 为T数据 为F数据
001 4 3 1
002 2 1 1
003 1 1 0如果A2有其它不同值的继续显示
count(1) [所有数据]
sum(case a3 when 'T' then 1 else 0 end) [为T数据],
sum(case a3 when 'F' then 1 else 0 end) [为F数据]
from a
group by a2
insert into a values('S001', '001', 'T')
insert into a values('S002', '001', 'F')
insert into a values('S003', '001', 'T')
insert into a values('S004', '002', 'T')
insert into a values('S005', '002', 'F')
insert into a values('S006', '001', 'T')
insert into a values('S007', '003', 'T')
goselect A2 ,
count(1) [所有数据],
sum(case a3 when 'T' then 1 else 0 end) [为T数据],
sum(case a3 when 'F' then 1 else 0 end) [为F数据]
from a
group by a2
drop table a/*
A2 所有数据 为T数据 为F数据
---------- ----------- ----------- -----------
001 4 3 1
002 2 1 1
003 1 1 0(所影响的行数为 3 行)
*/
if object_id('[T1]') is not null drop table [T1]
create table [T1]([A1] varchar(4),[A2] varchar(3),[A3] varchar(1))
insert [T1]
select 'S001','001','T' union all
select 'S002','001','F' union all
select 'S003','001','T' union all
select 'S004','002','T' union all
select 'S005','002','F' union all
select 'S006','001','T' union all
select 'S007','003','T'
declare @str varchar(1000)
set @str=''
select @str=@str+','+'为'+[A3]+'数据'+'=sum(case when [A3]='
+QUOTENAME([A3],'''')+' then 1 else 0 end)' from [T1] group by [A3]
print @str
set @str='select [A2] as 代号,count(*) as 所有数据'
+@str+' from [T1] group by [A2]'
exec(@str)/*
代号 所有数据 为T数据 为F数据
001 4 3 1
002 2 1 1
003 1 1 0
*/
select
A2 as '代号'
, count(A2) as '所有数据'
, sum(case when A3 ='T' then 1 else 0 end) as '为T数据'
, sum(case when A3 ='F' then 1 else 0 end) as '为F数据'
FROM a
group by A2代号 所有数据 为T数据 为F数据
---------- ----------- ----------- -----------
001 4 3 1
002 2 1 1
003 1 1 0(3 行受影响)