IBM Rational 系统开发最佳实践工具包	WebSphere MQ 最佳实践 TOP 15

请求一个复杂动态汇总问题(刚刚学习)

楼主pippo_301（泥人）2006-03-06 10:50:00 在 MS-SQL Server / 基础类提问

类型颜色规格数量单价金额
11 红 X 2 3 6
11 红 L 1 3 3
11 绿 XXL 1 3 3
11 红 X 1 3 3
22 绿 XXL 1 4 4
22 绿 XXXL 1 4 4
22 黑 L 1 4 4
;; ;; ;; ; ; ;
我想得到一个这样的汇总
类型颜色 X L XXL XXXL .... (小计)数量单价金额
11 红 3 1 4 3 12
11 绿 1 1 3 3
小计 5 15
22 绿 1 1 2 4 8
22 黑 1 1 4 4
.... ..... .......
...... ...... .......
小计 3 12
合计 8 27

请高手指点一下,谢谢!!!!! 问题点数：100、回复次数：9Top

1 楼lsqkeke（可可）回复于 2006-03-06 10:55:41 得分 0

如果不加上小计，合计就好写Top

2 楼happyflystone（无枪的狙击手）回复于 2006-03-06 11:04:41 得分 5

declare @s varchar(8000)
set @s = 'select 类型,颜色'
select @s= @s +',['+规格+'] = sum(case 规格 when '''+规格+''' then 数量 else 0 end)'
from (select 规格 from table group by 规格 group by 规格) a

exec(@s+',单价'+ ' from table group by 类型,颜色,单价 WITH CUBE')
Top

3 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2006-03-06 11:12:30 得分 70

create table t(类型 varchar(10),颜色 varchar(10),规格 varchar(10),数量 int,单价 int,金额 int)
insert into t select '11','红','X ',2,3,6
insert into t select '11','红','L ',1,3,3
insert into t select '11','绿','XXL ',1,3,3
insert into t select '11','红','X ',1,3,3
insert into t select '22','绿','XXL ',1,4,4
insert into t select '22','绿','XXXL',1,4,4
insert into t select '22','黑','L ',1,4,4

declare @s varchar(8000)
set @s='select (case when 类型 is null then ''合计'' when 颜色 is null then ''小计'' else 类型 end) as 类型,isnull(颜色,'''') as 颜色'
select @s=@s+',['+规格+']=sum(case 规格 when '''+规格+''' then 数量 else 0 end)'
from t group by 规格 order by 规格
set @s=@s+',[数量(小计)]=sum(数量),单价=max(单价),金额=sum(金额) from t group by 类型,颜色 with rollup'
exec(@s)

/*
类型颜色 L X XXL XXXL 数量(小计) 单价金额
---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
11 红 1 3 0 0 4 3 12
11 绿 0 0 1 0 1 3 3
小计 1 3 1 0 5 3 15
22 黑 1 0 0 0 1 4 4
22 绿 0 0 1 1 2 4 8
小计 1 0 1 1 3 4 12
合计 2 3 2 1 8 4 27
*/

drop table tTop

4 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2006-03-06 11:19:15 得分 0

create table t(类型 varchar(10),颜色 varchar(10),规格 varchar(10),数量 int,单价 int,金额 int)
insert into t select '11','红','X ',2,3,6
insert into t select '11','红','L ',1,3,3
insert into t select '11','绿','XXL ',1,3,3
insert into t select '11','红','X ',1,3,3
insert into t select '22','绿','XXL ',1,4,4
insert into t select '22','绿','XXXL',1,4,4
insert into t select '22','黑','L ',1,4,4

declare @s varchar(8000)
set @s='select (case when 类型 is null then ''合计'' when 颜色 is null then ''小计'' else 类型 end) as 类型,颜色'
select @s=@s+',['+规格+']=sum(case 规格 when '''+规格+''' then 数量 else 0 end)'
from t group by 规格 order by 规格
set @s=@s+',[数量(小计)]=sum(数量),单价,金额=sum(金额) from t group by 类型,颜色,单价 with rollup having grouping(单价)=0 or grouping(颜色)=1'
exec(@s)

/*
类型颜色 L X XXL XXXL 数量(小计) 单价金额
---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
11 红 1 3 0 0 4 3 12
11 绿 0 0 1 0 1 3 3
小计 NULL 1 3 1 0 5 NULL 15
22 黑 1 0 0 0 1 4 4
22 绿 0 0 1 1 2 4 8
小计 NULL 1 0 1 1 3 NULL 12
合计 NULL 2 3 2 1 8 NULL 27
*/

drop table tTop

5 楼pippo_301（泥人）回复于 2006-03-06 12:14:12 得分 0

to:libin_ftsafe(子陌红尘)
咋不行啊,
说什么:
不能使用空白的对象或列名。如果必要，请使用一个空格。Top

6 楼pippo_301（泥人）回复于 2006-03-06 12:24:21 得分 0

好了,谢谢,如果写成存储过程怎么写啊,先把每一条明细插入然后再查询,谢谢帮帮我,我刚刚学习这个.Top

7 楼zjcxc（邹建）回复于 2006-03-06 12:37:48 得分 25

--写存储过程的话, 只需要加个头, 在查询分析器中执行一下, 就建立好存储过程了.
--以后就可以用: exec 存储过程名来调用

create proc 存储过程名
as
set nocount on
declare @s varchar(8000)
set @s='select (case when 类型 is null then ''合计'' when 颜色 is null then ''小计'' else 类型 end) as 类型,颜色'
select @s=@s+',['+规格+']=sum(case 规格 when '''+规格+''' then 数量 else 0 end)'
from t group by 规格 order by 规格
set @s=@s+',[数量(小计)]=sum(数量),单价,金额=sum(金额) from t group by 类型,颜色,单价 with rollup having grouping(单价)=0 or grouping(颜色)=1'
exec(@s)Top

8 楼pippo_301（泥人）回复于 2006-03-06 13:20:26 得分 0

谢谢,各位大哥,结贴了Top

9 楼zl1980（友情暂住）回复于 2006-05-01 00:28:31 得分 0

ipTop