22,209
社区成员
发帖
与我相关
我的任务
分享
DECLARE @T TABLE(date DATETIME,name varchar(30),price int)
INSERT INTO @T
SELECT '2010-01-01','张三',10 UNION ALL
SELECT '2010-01-01','李四',20 UNION ALL
SELECT '2010-01-03','王五',30
SELECT CASE WHEN GROUPING(a.date)=1 THEN '合计' ELSE CONVERT(VARCHAR(10),a.date,120) END date,
SUM(a.[张三]),SUM(a.[李四]),SUM(a.[王五]),
CASE WHEN GROUPING(a.date)=1 THEN NULL ELSE SUM(b.hj) END '合计' FROM
(
SELECT * FROM @T PIVOT (SUM(price) FOR [NAME] IN([张三],[李四],[王五])) as t
)a INNER JOIN (SELECT date,SUM(price) hj FROM @t GROUP BY date) b
ON a.date=b.date
GROUP BY a.date WITH ROLLUP
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-07-08 19:03:01
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([date] [datetime],[name] [nvarchar](10),[price] [int])
INSERT INTO [tb]
SELECT '2010-1-1','张三','10' UNION ALL
SELECT '2010-1-1','李四','20' UNION ALL
SELECT '2010-1-3','王五','30'
--SELECT * FROM [tb]
-->SQL查询如下:
DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(name),
@s1=ISNULL(@s1+',','')+'SUM(ISNULL('+name+',0)) ['+name+']'
FROM tb
GROUP BY name
ORDER BY MIN(date)
EXEC('
SELECT ISNULL(CONVERT(VARCHAR(10), date, 23),''合计'') 日期,'+@s1+',MAX(合计) 合计
FROM (
SELECT *,SUM(price)OVER(PARTITION BY date) 合计
FROM tb
) a
PIVOT(SUM(price) FOR name IN('+@s+'))b
GROUP BY CONVERT(VARCHAR(10), date, 23)
WITH ROLLUP
')
/*
日期 李四 张三 王五 合计
---------- ----------- ----------- ----------- -----------
2010-01-01 20 10 0 30
2010-01-03 0 0 30 30
合计 20 10 30 30
(3 行受影响)
*/
05的--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([date] datetime,[name] varchar(4),[num] int)
insert [tb]
select '2010-1-1','张三',10 union all
select '2010-1-1','李四',20 union all
select '2010-1-3','王五',30
--------------------------------查询开始------------------------------
declare @sql varchar(8000)
set @sql = 'select isnull(convert(varchar(10),[date],120),''合计'') '
select @sql = @sql + ' , max(case [name] when ''' + [name] + ''' then [num] else 0 end) [' + [name] + ']'
from (select distinct [name] from tb) as a
set @sql = @sql + ',sum([num]) as 合计 from tb group by [date] with rollup'
exec(@sql)
/*
李四 王五 张三 合计
---------- ----------- ----------- ----------- -----------
2010-01-01 20 0 10 30
2010-01-03 0 30 0 30
合计 20 30 10 60
(3 行受影响)
*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([date] datetime,[name] varchar(4),[num] int)
insert [tb]
select '2010-1-1','张三',10 union all
select '2010-1-1','李四',20 union all
select '2010-1-3','王五',30
--------------------------------查询开始------------------------------
declare @sql varchar(8000)
set @sql = 'select isnull(convert(varchar(10),[date],120),''合计'') '
select @sql = @sql + ' , max(case [name] when ''' + [name] + ''' then [num] else 0 end) [' + [name] + ']'
from (select distinct [name] from tb) as a
set @sql = @sql + ' from tb group by [date] with rollup'
exec(@sql)
/*
date 李四 王五 张三
----------------------- ----------- ----------- -----------
2010-01-01 20 0 10
2010-01-03 0 30 0
合计 20 30 10
(3 行受影响)
*/
use PracticeDB
go
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (date date,name varchar(5), price int)
insert into tb
select '2010-1-1', '张三', 10 union all
select '2010-1-1', '李四', 20 union all
select '2010-1-3', '王五', 30
select isnull(cast (date as varchar(10)),'合计') date,张三=sum(case name when '张三' then price else 0 end),
李四=sum(case name when '李四' then price else 0 end),
王五=sum(case name when '王五' then price else 0 end),
合计=sum(price)
from tb
group by date with rollup
date 张三 李四 王五 合计
2010-01-01 10 20 0 30
2010-01-03 0 0 30 30
合计 10 20 30 60
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([date] datetime,[name] varchar(4),[price] int)
insert [tb]
select '2010-1-1','张三',10 union all
select '2010-1-1','李四',20 union all
select '2010-1-3','王五',30
---查询:第1步
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when name='''+name+''' then price else 0 end) as ['+name+']'
from
(select distinct name from tb) t
exec('select convert(varchar(10),[date],120) as [date],'
+@sql
+' from tb group by convert(varchar(10),[date],120)'
)
/**
date 李四 王五 张三
---------- ----------- ----------- -----------
2010-01-01 20 0 10
2010-01-03 0 30 0
(2 行受影响)
**/
---查询:第2步
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when name='''+name+''' then price else 0 end) as ['+name+']'
from
(select distinct name from tb) t
exec('select isnull(convert(varchar(10),[date],120),''合计'') as [date],'
+@sql
+',sum(price) as 合计 from tb group by convert(varchar(10),[date],120) with rollup'
)
/**
date 李四 王五 张三 合计
---------- ----------- ----------- ----------- -----------
2010-01-01 20 0 10 30
2010-01-03 0 30 0 30
合计 20 30 10 60
(3 行受影响)
**/