34,576
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-28 14:22:53
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([物料代码] varchar(5),[物料名称] varchar(5),[规格型号] varchar(5),[单位] varchar(2),[数量] int,[单价] int,[金额] int)
insert [tb]
select '代码1','名称1','规格1','个',10,3,30 union all
select '代码2','名称2','规格2','个',20,4,80 union all
select '代码3','名称3','规格3','个',30,5,150 union all
select '代码4','名称4','规格4','个',40,6,240 union all
select '代码5','名称5','规格5','个',50,7,350 union all
select '代码6','名称6','规格6','个',60,8,480
--------------开始查询--------------------------
select 物料代码,物料名称,规格型号,数量,单价,金额 from tb
union all
select
case when grouping([物料代码])=1 then '合计' else cast([物料代码] as varchar) end [物料代码],
[物料名称],[规格型号],sum([数量]) as [数量],[单价], sum([金额]) as [金额]
from
tb
group by
[物料代码],[物料名称],[单价],[规格型号]
with rollup
having grouping([物料代码])=1 --and grouping([物料名称])=0 and grouping([单价])=0 and grouping([规格型号])=0
----------------结果----------------------------
/*物料代码 物料名称 规格型号 数量 单价 金额
------------------------------ ----- ----- ----------- ----------- -----------
代码1 名称1 规格1 10 3 30
代码2 名称2 规格2 20 4 80
代码3 名称3 规格3 30 5 150
代码4 名称4 规格4 40 6 240
代码5 名称5 规格5 50 7 350
代码6 名称6 规格6 60 8 480
合计 NULL NULL 210 NULL 1330
(7 行受影响)
*/
create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7)
insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, sum(c) as c,sum(d) as d,sum(e) as e
from
#t
group by
a,b
with rollup
having grouping(b)=0 or grouping(a)=1
/*a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
2 2 14 16 26
2 3 15 16 26
合计 NULL 53 64 104
(所影响的行数为 5 行)
create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7)
insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, sum(c) as c,sum(d) as d,sum(e) as e
from
#t
group by
a,b
with rollup
having grouping(b)=0 or grouping(a)=1
/*a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
2 2 14 16 26
2 3 15 16 26
合计 NULL 53 64 104
(所影响的行数为 5 行)
union all
或者 with rollup, 把不想要的记录剔除
select sum(数量) 数量,sum(金额) 金额 from tb