--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:[TB] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') AND type in (N'U')) DROP TABLE [TB] GO---->建表 create table [TB]([成品] varchar(1),[原料] varchar(3),[用量] int) insert [TB] select 'A','A01',5 union all select 'A','A02',12 union all select 'B','B01',1 union all select 'B','B02',8 union all select 'B','B03',1 GO--> 查询结果 SELECT CASE WHEN (GROUPING([成品]) = 1) THEN '总计' ELSE ISNULL([成品], 'UNKNOWN') END AS [成品], CASE WHEN (GROUPING([原料]) = 1) THEN [成品]+'小计' ELSE ISNULL([原料], 'UNKNOWN') END AS [原料], SUM([用量]) AS [用量] FROM [TB] GROUP BY [成品],[原料] WITH ROLLUP --> 删除表格 --DROP TABLE [TB]
select a, b, c, sum( d ) from t group by rollup(a, b, c);等效于select * from ( select a, b, c, sum( d ) from t group by a, b, c union all select a, b, null, sum( d ) from t group by a, b union all select a, null, null, sum( d ) from t group by a union all select null, null, null, sum( d ) from t )
借用楼上测试数据SELECT isnull([成品],'合计'),ISNULL([原料],'小计'),SUM([用量]) FROM TB GROUP BY [成品],[原料] WITH ROLLUP ------------- (无列名) (无列名) (无列名) A A01 5 A A02 12 A 小计 17 B B01 1 B B02 8 B B03 1 B 小计 10 合计 小计 27
小计,合计我都是喜欢自己union all,再按照某个排序来搞....
create table [TB]([成品] varchar(10),[原料] varchar(10),[用量] int) insert [TB] select 'A','A01',5 union all select 'A','A02',12 union all select 'B','B01',1 union all select 'B','B02',8 union all select 'B','B03',1 GOselect isnull(成品 , '合计') 成品, isnull(原料,'小计') 原料 , sum(用量) 用量 from tb group by 成品 , 原料 with rollupdrop table tb/*成品 原料 用量 ---------- ---------- ----------- A A01 5 A A02 12 A 小计 17 B B01 1 B B02 8 B B03 1 B 小计 10 合计 小计 27(所影响的行数为 8 行)*/
create table t1 ( cid varchar(5), cyuanliao varchar(10), iyongliang int ) insert into t1 select 'A', 'A01', 5 union all select 'A', 'A02', 12 union all select 'B', 'B01', 1 union all select 'B', 'B02', 8 union all select 'B', 'B03', 1select cid,cyuanliao,iyongliang from t1 union all select cid,'小计' as cyuanliao,sum(iyongliang) as iyongliang from t1 group by cid order by cid,cyuanliao
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([成品] varchar(1),[原料] varchar(3),[用量] int)
insert [TB]
select 'A','A01',5 union all
select 'A','A02',12 union all
select 'B','B01',1 union all
select 'B','B02',8 union all
select 'B','B03',1
GO--> 查询结果
SELECT CASE WHEN (GROUPING([成品]) = 1) THEN '总计'
ELSE ISNULL([成品], 'UNKNOWN')
END AS [成品],
CASE WHEN (GROUPING([原料]) = 1) THEN [成品]+'小计'
ELSE ISNULL([原料], 'UNKNOWN')
END AS [原料],
SUM([用量]) AS [用量]
FROM [TB]
GROUP BY [成品],[原料] WITH ROLLUP
--> 删除表格
--DROP TABLE [TB]
from t
group by rollup(a, b, c);等效于select * from (
select a, b, c, sum( d ) from t group by a, b, c
union all
select a, b, null, sum( d ) from t group by a, b
union all
select a, null, null, sum( d ) from t group by a
union all
select null, null, null, sum( d ) from t
)
GROUP BY [成品],[原料] WITH ROLLUP
-------------
(无列名) (无列名) (无列名)
A A01 5
A A02 12
A 小计 17
B B01 1
B B02 8
B B03 1
B 小计 10
合计 小计 27
insert [TB]
select 'A','A01',5 union all
select 'A','A02',12 union all
select 'B','B01',1 union all
select 'B','B02',8 union all
select 'B','B03',1
GOselect isnull(成品 , '合计') 成品, isnull(原料,'小计') 原料 , sum(用量) 用量 from tb
group by 成品 , 原料 with rollupdrop table tb/*成品 原料 用量
---------- ---------- -----------
A A01 5
A A02 12
A 小计 17
B B01 1
B B02 8
B B03 1
B 小计 10
合计 小计 27(所影响的行数为 8 行)*/
(
cid varchar(5),
cyuanliao varchar(10),
iyongliang int
)
insert into t1
select 'A', 'A01', 5 union all
select 'A', 'A02', 12 union all
select 'B', 'B01', 1 union all
select 'B', 'B02', 8 union all
select 'B', 'B03', 1select cid,cyuanliao,iyongliang from t1 union all
select cid,'小计' as cyuanliao,sum(iyongliang) as iyongliang from t1 group by cid order by cid,cyuanliao