如下表结果 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
解决方案 »
- 求助c#调用API函数RedrawWindow更新指定区域的问题
- 一个面试题,请教如何解决
- 如何过滤掉DataTable中的指定列/字段
- dataGridView的问题
- 如何在DataGridView中添加一行CheckBox?(winForm)
- 如何将备份(数据库名为QQ)的SQL2000的数据库文件(如:ddd.bak)还原成原来的数据库QQ
- 怎么样才能学好C#
- 如下代码所示,如何实现点击button后能取到textblock的值
- datagirdview新增行出现的问题
- 系统托盘图标的问题
- 求助 C#中用serialport控件写入AT指令时有人用WriteAtcommand操作的 这个的命名空间是什么?
- 怎样给repeater中的button绑定事件??
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