如下表结果 id name status
1 xxxx 1
2 yyyy 2
3 zzzz 3
4 ffff 1
5 erer 1
6 gffg 3
7 xdfd 1
8 fdvd 1
9 ffff 2
10 rttr 1我想要这样的结果 Sum S1 S2 S3
10 6 2 2
1 xxxx 1
2 yyyy 2
3 zzzz 3
4 ffff 1
5 erer 1
6 gffg 3
7 xdfd 1
8 fdvd 1
9 ffff 2
10 rttr 1我想要这样的结果 Sum S1 S2 S3
10 6 2 2
from
(select count(id) as k1 from test where TS = "1") a,
(select count(id) as k2 from test where TS ="2") b,
(select count(id) as k3 from test where TS ="2") c;个人感觉这样的写法很不好。不知道有没有更优雅的办法。
create table #t (id int identity,name varchar(10),status int)insert into #t(name,status)values('xxxx',1)
insert into #t(name,status)values('yyyy',2)
insert into #t(name,status)values('zzzz',3)
insert into #t(name,status)values('ffff',1)
insert into #t(name,status)values('erer',1)
insert into #t(name,status)values('gffg',3)
insert into #t(name,status)values('xdfd',1)
insert into #t(name,status)values('fdvd',1)
insert into #t(name,status)values('ffff',2)
insert into #t(name,status)values('rttr',1)select count(id) as count,status into #d from #t group by status
select
(select sum(count) from #d )as sum,
(select count from #d where status = 1) as s1,
(select count from #d where status = 2) as s2 ,
(select count from #d where status = 3) as s3
drop table #d
顶一下
create table #t (id int identity,name varchar(10),status int)insert into #t(name,status)values('xxxx',1)
insert into #t(name,status)values('yyyy',2)
insert into #t(name,status)values('zzzz',3)
insert into #t(name,status)values('ffff',1)
insert into #t(name,status)values('erer',1)
insert into #t(name,status)values('gffg',3)
insert into #t(name,status)values('xdfd',1)
insert into #t(name,status)values('fdvd',1)
insert into #t(name,status)values('ffff',2)
insert into #t(name,status)values('rttr',1)select count(id) as 'SUM'
, sum(case status when 1 then 1 else 0 end) as 'S1'
, sum(case status when 2 then 1 else 0 end) as 'S2'
, sum(case status when 3 then 1 else 0 end) as 'S3'
from #t
select @sql='select count(id) as ''SUM'''
select @sql = @sql+
', sum(case status when '''+ Convert(varchar(2), status) +''' then 1 else 0 end) [S'+ Convert(varchar(2), status) +']'
from (select distinct status from #t) a
select @sql = @sql + ' from #t'
exec sp_executesql @sql
insert into #t(name,status)values('yyyy',2)
insert into #t(name,status)values('zzzz',3)
insert into #t(name,status)values('ffff',1)
insert into #t(name,status)values('erer',1)
insert into #t(name,status)values('gffg',3)
insert into #t(name,status)values('xdfd',1)
insert into #t(name,status)values('fdvd',1)
insert into #t(name,status)values('ffff',2)
insert into #t(name,status)values('rttr',1)
--静态
select count(1) as '总数',
sum(case when status=1 then 1 else 0 end) as s1,
sum(case when status=2 then 1 else 0 end) as s2,
sum(case when status=3 then 1 else 0 end) as s3
from #t
--动态
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'sum(case when status='''+ltrim(status)+''' then 1 else 0 end) as [s'+ltrim(status)+']'
from (select distinct status from #t)tp
exec('select count(1) as ''总数'','+@sql+' from #t')
10 6 2 2