求按日期分组后组内按条件输出的sql语句
假设有表
时间 省份 数据
2013-8-1 陕西 50
2013-8-2 陕西 50
2013-8-3 陕西 50
2013-8-3 河南 50
2013-8-4 陕西 50
2013-8-5 陕西 50
2013-8-5 河南 50
2013-8-5 山西 50
要求输出结果为
时间 全国 陕西 山西 河南
2013-8-1 50 50 0 0
2013-8-2 50 50 0 0
2013-8-3 100 50 0 50
2013-8-4 50 50 0 0
2013-8-5 150 50 50 50
[解决办法]
create table tb(时间 datetime,省份 varchar(20), 数据 int)
insert into tb
select '2013-8-1', '陕西', 50 union all
select '2013-8-2', '陕西', 50 union all
select '2013-8-3', '陕西', 50 union all
select '2013-8-3', '河南', 50 union all
select '2013-8-4', '陕西', 50 union all
select '2013-8-5', '陕西', 50 union all
select '2013-8-5', '河南', 50 union all
select '2013-8-5', '山西', 50
go
declare @sql varchar(8000)
set @sql = ',sum(数据) as [全国]'
select @sql = @sql + ',sum(case when 省份='''+省份+''' then 数据 else 0 end) ['+省份+']'
from tb
group by 省份
order by 省份 desc
select @sql = 'select 时间'+@sql +
'from tb group by 时间'
exec( @sql)
/*
时间全国陕西山西河南
2013-08-01 00:00:00.000505000
2013-08-02 00:00:00.000505000
2013-08-03 00:00:00.00010050050
2013-08-04 00:00:00.000505000
2013-08-05 00:00:00.000150505050
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-22 14:07:39
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([时间] date,[省份] varchar(4),[数据] int)
insert [huang]
select '2013-8-1','陕西',50 union all
select '2013-8-2','陕西',50 union all
select '2013-8-3','陕西',50 union all
select '2013-8-3','河南',50 union all
select '2013-8-4','陕西',50 union all
select '2013-8-5','陕西',50 union all
select '2013-8-5','河南',50 union all
select '2013-8-5','山西',50
--------------开始查询--------------------------
IF OBJECT_ID('tempdb..#t','U')IS NOT NULL
DROP TABLE #t
SELECT * INTO #t
FROM (
SELECT *
FROM huang
UNION ALL
SELECT [时间],'全国',SUM([数据])[数据]
FROM huang
GROUP BY [时间])a
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(省份)+'=max(case when [省份]='+quotename(省份,'''')+' then [数据] else 0 end)'
from #t group by 省份
exec('select [时间]'+@s+' from #t group by [时间] ')
----------------结果----------------------------
/*
时间 河南 全国 山西 陕西
---------- ----------- ----------- ----------- -----------
2013-08-01 0 50 0 50
2013-08-02 0 50 0 50
2013-08-03 50 100 0 50
2013-08-04 0 50 0 50
2013-08-05 50 150 50 50
*/
select 时间,
sum(数据) as [全国],
sum(case when 省份='陕西' then 数据 else 0 end) [陕西],
sum(case when 省份='山西' then 数据 else 0 end) [山西],
sum(case when 省份='河南' then 数据 else 0 end) [河南]
from tb
group by 时间
/*
时间全国陕西山西河南
2013-08-01 00:00:00.000505000
2013-08-02 00:00:00.000505000
2013-08-03 00:00:00.00010050050
2013-08-04 00:00:00.000505000
2013-08-05 00:00:00.000150505050
*/
