22,300
社区成员




--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (款式 varchar(1),颜色 varchar(5),尺码 varchar(4),件数 int)
insert into #tb
select 'A','红','X',1 union all
select 'A','红','XL',2 union all
select 'A','红','XXL',3 union all
select 'A','绿','X',4 union all
select 'A','黄','XXL',5 union all
select 'A','黄','XLL',6 union all
select 'B','蓝','120M',7 union all
select 'B','蓝','130M',8 union all
select 'B','紫','120M',9 union all
select 'C','白','X',10 union all
select 'C','白','XL',11
SELECT 字段1='A',字段2='颜色',字段3='X',字段4='XL',字段5='XXL',字段6='XLL',字段7='合计'
UNION ALL
select 款式,颜色=ISNULL(颜色,'合计'),
LTRIM(sum(case when 尺码='X' THEN 件数 else 0 end)),
LTRIM(sum(case when 尺码='XL' THEN 件数 else 0 end)),
LTRIM(sum(case when 尺码='XXL' THEN 件数 else 0 end)),
LTRIM(sum(case when 尺码='XLL' THEN 件数 else 0 end)),
LTRIM(sum(件数))
from #tb
where 款式='A'
GROUP BY 款式,颜色
WITH ROLLUP
HAVING(NOT (GROUPING(款式)=1 AND GROUPING(颜色)=1))
union all
SELECT 'B','颜色','120M','130M','合计','',''
UNION ALL
select 款式,颜色=ISNULL(颜色,'合计'),
LTRIM(sum(case when 尺码='120M' THEN 件数 else 0 end)),
LTRIM(sum(case when 尺码='130M' THEN 件数 else 0 end)),
LTRIM(sum(件数)),'',''
from #tb
where 款式='B'
GROUP BY 款式,颜色
WITH ROLLUP
HAVING(NOT (GROUPING(款式)=1 AND GROUPING(颜色)=1))
UNION ALL
SELECT 'C','颜色','X','XL','合计','',''
UNION ALL
select 款式,颜色 ,
LTRIM(sum(case when 尺码='X' THEN 件数 else 0 end)),
LTRIM(sum(case when 尺码='XL' THEN 件数 else 0 end)),
LTRIM(sum(件数)),'',''
from #tb
where 款式='C'
GROUP BY 款式,颜色
字段1 字段2 字段3 字段4 字段5 字段6 字段7
---- ----- ------------ ------------ ------------ ------------ ------------
A 颜色 X XL XXL XLL 合计
A 红 1 2 3 0 6
A 黄 0 0 5 6 11
A 绿 4 0 0 0 4
A 合计 5 2 8 6 21
B 颜色 120M 130M 合计
B 蓝 7 8 15
B 紫 9 0 9
B 合计 16 8 24
C 颜色 X XL 合计
C 白 10 11 21
(11 行受影响)