34,590
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb..#SellTable') is not null --如果临时表存在则删除
BEGIN
DROP TABLE #SellTable
END
create Table #SellTable(部门名称 varchar(100),职员名称 varchar(100),存货名称 varchar(100),销售额 money)
insert #SellTable
select '销售部','张三','A',100
union select '销售部','李四','A',120
union select '销售部','王五','A',130
union select '生产部','张三','A',140
union select '行政部','李四','A',150
union select '生产部','王五','A',160
union select '行政部','张三','A',140
union select '生产部','李四','B',150
union select '销售部','王五','A',160
union select '生产部','张三','B',140
union select '行政部','李四','A',150
union select '生产部','王五','C',160
union select '行政部','张三','A',140
union select '生产部','李四','A',150
union select '销售部','王五','C',160
Select 部门名称,职员名称,CASE WHEN (GROUPING(存货名称)=1)
THEN '[小计]'
ELSE 存货名称 END AS 存货名称,
SUM(销售额) AS 销售额
From #SellTable
GROUP BY 部门名称,职员名称,存货名称 WITH ROLLUP
DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)
INSERT @t SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red', -23
UNION ALL SELECT 'bb','Cup' ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red', -90
--汇总显示
SELECT Groups=CASE
WHEN GROUPING(Color)=0 THEN Groups
WHEN GROUPING(Groups)=1 THEN '总计'
ELSE '' END,
Item=CASE
WHEN GROUPING(Color)=0 THEN Item
WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
ELSE '' END,
Color=CASE
WHEN GROUPING(Color)=0 THEN Color
WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
ELSE '' END,
Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
/*--结果
Groups Item Color Quantity
-------- ---------------- ---------------------- -----------
aa Chair Blue 101
aa Chair Red -90
Chair 小计 11
aa Table Blue 124
Table 小计 124
aa 合计 135
bb Cup Green -23
Cup 小计 -23
bb Table Red -23
Table 小计 -23
bb 合计 -46
总计 89
--*/
Select
case when 部门名称 is not null and 职员名称 is null then '[小计]'
when 部门名称 is null and 职员名称 is null then '[总计]'
else 部门名称
end as 部门名称,
case when 职员名称 is null then ltrim(cast(SUM(销售额) as float))
when 职员名称 is not null and 存货名称 is null then '[小计]'
else 职员名称
end as 职员名称,
case
when 存货名称 is null then ''
else 存货名称 end as 存货名称,
case when 职员名称 is null then ''
else ltrim(cast(SUM(销售额) as float))
end AS 销售额
From
#SellTable
GROUP BY
部门名称,职员名称,存货名称
WITH ROLLUP
/**
部门名称 职员名称 存货名称 销售额
---------- ---------------------- ---------- ----------------------
生产部 李四 A 150
生产部 李四 B 150
生产部 [小计] 300
生产部 王五 A 160
生产部 王五 C 160
生产部 [小计] 320
生产部 张三 A 140
生产部 张三 B 140
生产部 [小计] 280
[小计] 900
销售部 李四 A 120
销售部 [小计] 120
销售部 王五 A 290
销售部 王五 C 160
销售部 [小计] 450
销售部 张三 A 100
销售部 [小计] 100
[小计] 670
行政部 李四 A 150
行政部 [小计] 150
行政部 张三 A 140
行政部 [小计] 140
[小计] 290
[总计] 1860
(所影响的行数为 24 行)
**/
Select
case when 部门名称 is not null and 职员名称 is null then '[小计]'
when 部门名称 is null and 职员名称 is null then '[总计]'
else 部门名称
end as 部门名称,
case when 职员名称 is null then ltrim(cast(SUM(销售额) as float))
else 职员名称
end as 职员名称,
case when 职员名称 is not null and 存货名称 is null then '[小计]'
when 职员名称 is null and 存货名称 is null then ''
else 存货名称 end as 存货名称,
case when 职员名称 is null then ''
else ltrim(cast(SUM(销售额) as float))
end AS 销售额
From
#SellTable
GROUP BY
部门名称,职员名称,存货名称
WITH ROLLUP
/**
部门名称 职员名称 存货名称 销售额
---------- ---------------------- ---------- ----------------------
生产部 李四 A 150
生产部 李四 B 150
生产部 李四 [小计] 300
生产部 王五 A 160
生产部 王五 C 160
生产部 王五 [小计] 320
生产部 张三 A 140
生产部 张三 B 140
生产部 张三 [小计] 280
[小计] 900
销售部 李四 A 120
销售部 李四 [小计] 120
销售部 王五 A 290
销售部 王五 C 160
销售部 王五 [小计] 450
销售部 张三 A 100
销售部 张三 [小计] 100
[小计] 670
行政部 李四 A 150
行政部 李四 [小计] 150
行政部 张三 A 140
行政部 张三 [小计] 140
[小计] 290
[总计] 1860
(所影响的行数为 24 行)
**/
Select
case when grouping(部门名称)=1 then '[合计]' when grouping(职员名称)=1 then '[小计]' else 部门名称 end 部门名称,
case when grouping(职员名称)=1 then ltrim(SUM(销售额)) when grouping(存货名称)=1 then '[小计]' else 职员名称 end 职员名称,
case when grouping(职员名称)=1 or grouping(部门名称)=1 then '' when grouping(存货名称)=1 then ltrim(SUM(销售额)) else 存货名称 end 存货名称,
case when grouping(职员名称)=1 or grouping(部门名称)=1 or grouping(存货名称)=1 then '' else ltrim(SUM(销售额)) end 销售额
From #SellTable
GROUP BY 部门名称,职员名称,存货名称
WITH ROLLUP
/*
部门名称 职员名称 存货名称 销售额
--------------------------------
生产部 李四 A 150.00
生产部 李四 B 150.00
生产部 [小计] 300.00
生产部 王五 A 160.00
生产部 王五 C 160.00
生产部 [小计] 320.00
生产部 张三 A 140.00
生产部 张三 B 140.00
生产部 [小计] 280.00
[小计] 900.00
销售部 李四 A 120.00
销售部 [小计] 120.00
销售部 王五 A 290.00
销售部 王五 C 160.00
销售部 [小计] 450.00
销售部 张三 A 100.00
销售部 [小计] 100.00
[小计] 670.00
行政部 李四 A 150.00
行政部 [小计] 150.00
行政部 张三 A 140.00
行政部 [小计] 140.00
[小计] 290.00
合计 1860.00
(24 行受影响)
*/
if object_id('tempdb..#SellTable') is not null --如果临时表存在则删除
BEGIN
DROP TABLE #SellTable
END
create Table #SellTable(部门名称 varchar(100),职员名称 varchar(100),存货名称 varchar(100),销售额 money)
insert #SellTable
select '销售部','张三','A',100
union select '销售部','李四','A',120
union select '销售部','王五','A',130
union select '生产部','张三','A',140
union select '行政部','李四','A',150
union select '生产部','王五','A',160
union select '行政部','张三','A',140
union select '生产部','李四','B',150
union select '销售部','王五','A',160
union select '生产部','张三','B',140
union select '行政部','李四','A',150
union select '生产部','王五','C',160
union select '行政部','张三','A',140
union select '生产部','李四','A',150
union select '销售部','王五','C',160
go
Select
case when grouping(部门名称)=1 then '合计' when grouping(职员名称)=1 then '[小计]' else 部门名称 end 部门名称,
case when grouping(职员名称)=1 then ltrim(SUM(销售额)) when grouping(存货名称)=1 then '[小计]' else 职员名称 end 职员名称,
case when grouping(职员名称)=1 or grouping(部门名称)=1 then '' when grouping(存货名称)=1 then ltrim(SUM(销售额)) else 存货名称 end 存货名称,
case when grouping(职员名称)=1 or grouping(部门名称)=1 or grouping(存货名称)=1 then '' else ltrim(SUM(销售额)) end 销售额
From #SellTable
GROUP BY 部门名称,职员名称,存货名称
WITH ROLLUP
NND,调试了挺久的。