表:Test,字段DwId,单位ID;字段Yqjb,预警级别,值可能为1、2、3CREATE TABLE [dbo].[Test] (
[DwId] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[Yqjb] [smallint] NULL
) ON [PRIMARY]测试数据:
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3)要求按单位编码统计级别1、2、3分别有多少个,显示格式要求如下:
结果:
编码 级别1个数 级别2个数 级别3个数
01 3 0 2
02 1 4 0
03 2 0 2谢谢
[DwId] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[Yqjb] [smallint] NULL
) ON [PRIMARY]测试数据:
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3)要求按单位编码统计级别1、2、3分别有多少个,显示格式要求如下:
结果:
编码 级别1个数 级别2个数 级别3个数
01 3 0 2
02 1 4 0
03 2 0 2谢谢
[DwId] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[Yqjb] [smallint] NULL
) INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3) select dwid,
sum(case when yqjb=1 then 1 else 0 end) [级数1个数],
sum(case when yqjb=2 then 1 else 0 end) [级数2个数],
sum(case when yqjb=3 then 1 else 0 end) [级数3个数]
from test
group by dwiddrop table test/*
dwid 级数1个数 级数2个数 级数3个数
---- ----------- ----------- -----------
01 3 0 2
02 1 4 0
03 2 0 2(3 行受影响)*/
sum(case when [Yqjb]=1 then 1 else 0 end ) as 级别1个数,
sum(case when [Yqjb]=2 then 1 else 0 end ) as 级别2个数,
sum(case when [Yqjb]=3 then 1 else 0 end ) as 级别3个数
from Test
group by DwId
[DwId] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[Yqjb] [smallint] NULL
) INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3) --动态的
declare @sql varchar(8000)
set @sql='select dwid'
select @sql=@sql+',sum(case when yqjb='+rtrim(yqjb)+' then 1 else 0 end) [级数'+rtrim(yqjb)+'个数]'
from test group by yqjbexec (@sql+' from test group by dwid')drop table test/*
dwid 级数1个数 级数2个数 级数3个数
---- ----------- ----------- -----------
01 3 0 2
02 1 4 0
03 2 0 2(3 行受影响)*/
CREATE TABLE [dbo].[Test] (
[DwId] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[Yqjb] [smallint] NULL
) ON [PRIMARY]
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3)
declare @sql varchar(8000)
set @sql='select dwid,'select @sql=@sql+'sum(case when [Yqjb]='+cast([Yqjb] as varchar(10))+' then 1 else 0 end) [级别'+cast([Yqjb] as varchar(10))+'个数],' from
(select distinct [Yqjb] from test ) a
set @sql=left(@sql,len(@sql)-1)+' from test group by dwid order by dwid'exec(@sql)
drop table test
set nocount off
/*
dwid 级别1个数 级别2个数 级别3个数
---- ----------- ----------- -----------
01 3 0 2
02 1 4 0
03 2 0 2*/
[DwId] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[Yqjb] [smallint] NULL
) ON [PRIMARY] INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('01',3)
INSERT INTO Test([DwId],[Yqjb]) VALUES('02',2)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',1)
INSERT INTO Test([DwId],[Yqjb]) VALUES('03',3) --静态SQL,指yqjb只有1,2,3
select dwid 编码,
sum(case yqjb when 1 then 1 else 0 end) 级别1个数,
sum(case yqjb when 2 then 1 else 0 end) 级别2个数,
sum(case yqjb when 3 then 1 else 0 end) 级别3个数
from test
group by dwid--动态SQL,指yqjb不止1,2,3
declare @sql varchar(8000)
set @sql = 'select dwid 编码 '
select @sql = @sql + ' , sum(case yqjb when ''' + cast(yqjb as varchar) + ''' then 1 else 0 end) [级别' + cast(yqjb as varchar) + '个数]'
from (select distinct yqjb from test) as a
set @sql = @sql + ' from test group by dwid'
exec(@sql) drop table test/*
编码 级别1个数 级别2个数 级别3个数
---- ----------- ----------- -----------
01 3 0 2
02 1 4 0
03 2 0 2
*/