求高手解决 一个问题，分组统计的问题！

Question

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ShopSaleinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ShopSaleinfo]
GOCREATE TABLE [dbo].[Saleinfo] (
    [billno] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [prodcode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [prodsize] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [prodcolor] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [prodprice] [money] NULL ,
    [prodcostprice] [money] NULL ,
    [discount] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [prodcount] [int] NULL ,
    [prodname] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
    [realmoney] [money] NULL 
) ON [PRIMARY]
GO
 /*
字段解释   billno  编号    prodcode》款号   prodsize》尺码 prodcolor》 颜色 prodprice》价格  discount》折扣  prodcount》数量  podname》名称   
*/
inser into Saleinfo（billno,prodcode,pordsize,prodcolor,prodprice,prodcount,discount,prodname）values('20100920','20301','00','012',1588,10,80,' 上衣')
inser into Saleinfo（billno,prodcode,pordsize,prodcolor,prodprice,prodcount,discount,prodname）values('20100920','20301','01','012',1588,7,80,' 上衣')
inser into Saleinfo（billno,prodcode,pordsize,prodcolor,prodprice,prodcount,discount,prodname）values('20100920','20301','03','012',1588,2,80,' 上衣')
inser into Saleinfo（billno,prodcode,pordsize,prodcolor,prodprice,prodcount,discount,prodname）values('20100920','20301','04','012',1588,10,80,' 上衣')
inser into Saleinfo（billno,prodcode,pordsize,prodcolor,prodprice,prodcount,discount,prodname）values('20100920','20301','00','020',1588,10,80,' 上衣')
inser into Saleinfo（billno,prodcode,pordsize,prodcolor,prodprice,prodcount,discount,prodname）values('20100920','20301','01','020',1588,10,80,' 上衣')
注：prodsize 尺码转为00-S 01-M 03-L 04-XL结果想得到：
款号 色号 S M L XL 价格 折扣 数量合计 金额（数量*价格*折扣）
2031 012 10 20 10 5 1588 80 50 900
2031 020 
合计 null 50 100 30 20 null null 500 100000

Answer 1

sqlcenter   树人大哥   dawugui   你们帮我写一下啊  谢谢了啊   

Answer 2

我要的不仅仅是行专列 还有替换 （比如把我的prodsize 替换成X  S M L）
还有就是一些无关的一些数据也要分组统计中 体现出来啊

Answer 3

http://topic.csdn.net/u/20101019/16/d7f53c70-eb39-4b6d-bbf2-094806ab65a6.html参考,你们是同一个人??

Answer 4

改成UNION ALL 来就行了

Answer 5

如果他写的语句正确的话 我也不用从昨天一直问到今天啊    关键是语句不正确啊 
他用一个max  只能分组char类型的数据，如果是int 就不对了啊 

Answer 6

CREATE TABLE tb (billno varchar(50),prodcode varchar(50),prodsize char(10),
    prodcolor char(10),prodprice money,prodcostprice money,prodcount int,discount int,
    prodname varchar(20),realmoney money)
GO
 /*
字段解释   billno  编号    prodcode》款号   prodsize》尺码 prodcolor》 
颜色 prodprice》价格  discount》折扣  prodcount》数量  podname》名称   
*/
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) 
values('20100920','20301','00','012',1588,10,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','01','012',1588,7,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','03','012',1588,2,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','04','012',1588,10,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','00','020',1588,10,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','01','020',1588,10,80,' 上衣')select isnull(prodcode,'合计') prodcode, isnull(prodcolor,'') prodcolor,
       sum(case prodsize when '00' then prodcount else 0 end) [S],
       sum(case prodsize when '01' then prodcount else 0 end) [M],
       sum(case prodsize when '03' then prodcount else 0 end) [L],
       sum(case prodsize when '04' then prodcount else 0 end) [XL],
       sum(prodprice) 价格,
       sum(discount) 折扣,
       sum(prodcount) 数量合计,
       sum(prodcount*prodprice*discount) 金额
from tb
group by prodcode , prodcolor
with rollupdrop table tb/*
prodcode                                           prodcolor  S           M           L           XL          价格                    折扣          数量合计        金额                    
-------------------------------------------------- ---------- ----------- ----------- ----------- ----------- --------------------- ----------- ----------- --------------------- 
20301                                              012        10          7           2           10          6352.0000             320         29          3684160.0000
20301                                              020        10          10          0           0           3176.0000             160         20          2540800.0000
20301                                                         20          17          2           10          9528.0000             480         49          6224960.0000
合计                                                            20          17          2           10          9528.0000             480         49          6224960.0000（所影响的行数为 4 行）
*/

Answer 7

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Saleinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Saleinfo]
GOCREATE TABLE [dbo].[Saleinfo] (
    [billno] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [prodcode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [prodsize] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [prodcolor] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [prodprice] [money] NULL ,
    [prodcostprice] [money] NULL ,
    [discount] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    [prodcount] [int] NULL ,
    [prodname] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
    [realmoney] [money] NULL 
) ON [PRIMARY]
GO
 /*
字段解释   billno  编号    prodcode》款号   prodsize》尺码 prodcolor》 颜色 prodprice》价格  discount》折扣  prodcount》数量  podname》名称   
*/
insert into Saleinfo(billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname)values('20100920','20301','00','012',1588,10,80,N'上衣')
insert into Saleinfo(billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname)values('20100920','20301','01','012',1588,7,80,N'上衣')
insert into Saleinfo(billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname)values('20100920','20301','03','012',1588,2,80,N'上衣')
insert into Saleinfo(billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname)values('20100920','20301','04','012',1588,10,80,N'上衣')
insert into Saleinfo(billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname)values('20100920','20301','00','020',1588,10,80,N'上衣')
insert into Saleinfo(billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname)values('20100920','20301','01','020',1588,10,80,N'上衣')select 款号=case when grouping(prodcode)=1 and grouping(prodcolor)=1 then N'合计' else prodcode end,
色号=case when grouping(prodcode)=0 and grouping(prodcolor)=1 then N'小计' else prodcolor end,
S=sum(case when prodsize='00' then prodcount else 0 end),
M=sum(case when prodsize='01' then prodcount else 0 end),
L=sum(case when prodsize='03' then prodcount else 0 end),
XL=sum(case when prodsize='04' then prodcount else 0 end),
价格=sum(prodprice*prodcount)/sum(prodcount),
折扣=sum(discount*prodcount)/sum(prodcount),
数量合计=sum(prodcount),
[金额（数量*价格*折扣）]=sum((prodprice-discount)*prodcount)
from Saleinfo
group by prodcode,prodcolor with rollup

Answer 8

select 
  prodcode as 款号,
  prodcolor as 色号,
  sum(case when prodsize='00' then 1 else 0 end) as S, 
  sum(case when prodsize='01' then 1 else 0 end) as M, 
  sum(case when prodsize='03' then 1 else 0 end) as L, 
  sum(case when prodsize='04' then 1 else 0 end) as XL, 
  prodprice as 价格,
  discount as 折扣,
  sum(prodcount) as 数量合计,
  cast(sum(prodcount*prodprice*discount/100.0) as dec(18,2)) as 金额
from 
  saleinfo
group by 
  prodcode,
  prodcolor,
  prodprice,
  discount
union all
select 
  '合计',
  null,
  sum(case when prodsize='00' then 1 else 0 end) as S, 
  sum(case when prodsize='01' then 1 else 0 end) as M, 
  sum(case when prodsize='03' then 1 else 0 end) as L, 
  sum(case when prodsize='04' then 1 else 0 end) as XL, 
  null,
  null,
  sum(prodcount) as 数量合计,
  cast(sum(prodcount*prodprice*discount/100.0) as dec(18,2)) as 金额
from 
  saleinfo/**
款号                                                 色号         S           M           L           XL          价格                    折扣         数量合计        金额
-------------------------------------------------- ---------- ----------- ----------- ----------- ----------- --------------------- ---------- ----------- ---------------------------------------
20301                                              012        1           1           1           1           1588.00               80         29          36841.60
20301                                              020        1           1           0           0           1588.00               80         20          25408.00
合计                                                 NULL       2           2           1           1           NULL                  NULL       49          62249.60(3 行受影响)
**/

Answer 9

CREATE TABLE tb (billno varchar(50),prodcode varchar(50),prodsize char(10),
    prodcolor char(10),prodprice money,prodcostprice money,prodcount int,discount int,
    prodname varchar(20),realmoney money)
GO
 /*
字段解释   billno  编号    prodcode》款号   prodsize》尺码 prodcolor》 
颜色 prodprice》价格  discount》折扣  prodcount》数量  podname》名称   
*/
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) 
values('20100920','20301','00','012',1588,10,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','01','012',1588,7,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','03','012',1588,2,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','04','012',1588,10,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','00','020',1588,10,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','01','020',1588,10,80,' 上衣')select prodcode, prodcolor,
       sum(case prodsize when '00' then prodcount else 0 end) [S],
       sum(case prodsize when '01' then prodcount else 0 end) [M],
       sum(case prodsize when '03' then prodcount else 0 end) [L],
       sum(case prodsize when '04' then prodcount else 0 end) [XL],
       max(prodprice) 价格,
       max(discount) 折扣,
       sum(prodcount) 数量合计,
       sum(prodcount*prodprice*discount) 金额
from tb
group by prodcode , prodcolor
union all
select '合计' prodcode, ''prodcolor,
       sum(case prodsize when '00' then prodcount else 0 end) [S],
       sum(case prodsize when '01' then prodcount else 0 end) [M],
       sum(case prodsize when '03' then prodcount else 0 end) [L],
       sum(case prodsize when '04' then prodcount else 0 end) [XL],
       null 价格,
       null 折扣,
       sum(prodcount) 数量合计,
       sum(prodcount*prodprice*discount) 金额
from tbdrop table tb/*
prodcode                                           prodcolor  S           M           L           XL          价格                    折扣          数量合计        金额                    
-------------------------------------------------- ---------- ----------- ----------- ----------- ----------- --------------------- ----------- ----------- --------------------- 
20301                                              012        10          7           2           10          1588.0000             80          29          3684160.0000
20301                                              020        10          10          0           0           1588.0000             80          20          2540800.0000
合计                                                            20          17          2           10          NULL                  NULL        49          6224960.0000（所影响的行数为 3 行）
*/

Answer 10

CREATE TABLE tb (billno varchar(50),prodcode varchar(50),prodsize char(10),
    prodcolor char(10),prodprice money,prodcostprice money,prodcount int,discount int,
    prodname varchar(20),realmoney money)
GO
 /*
字段解释   billno  编号    prodcode》款号   prodsize》尺码 prodcolor》 
颜色 prodprice》价格  discount》折扣  prodcount》数量  podname》名称   
*/
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) 
values('20100920','20301','00','012',1588,10,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','01','012',1588,7,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','03','012',1588,2,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','04','012',1588,10,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','00','020',1588,10,80,' 上衣')
insert into tb (billno,prodcode,prodsize,prodcolor,prodprice,prodcount,discount,prodname) values('20100920','20301','01','020',1588,10,80,' 上衣')select prodcode, prodcolor,
       sum(case prodsize when '00' then prodcount else 0 end) [S],
       sum(case prodsize when '01' then prodcount else 0 end) [M],
       sum(case prodsize when '03' then prodcount else 0 end) [L],
       sum(case prodsize when '04' then prodcount else 0 end) [XL],
       max(prodprice) 价格,
       max(discount) 折扣,
       sum(prodcount) 数量合计,
       sum(prodcount*prodprice*discount) 金额
from tb
group by prodcode , prodcolor
union all
select '合计' prodcode, ''prodcolor,
       sum(case prodsize when '00' then prodcount else 0 end) [S],
       sum(case prodsize when '01' then prodcount else 0 end) [M],
       sum(case prodsize when '03' then prodcount else 0 end) [L],
       sum(case prodsize when '04' then prodcount else 0 end) [XL],
       null 价格,
       null 折扣,
       sum(prodcount) 数量合计,
       sum(prodcount*prodprice*discount/100) 金额 --这里需要除100不？
from tbdrop table tb/*
prodcode                                           prodcolor  S           M           L           XL          价格                    折扣          数量合计        金额                    
-------------------------------------------------- ---------- ----------- ----------- ----------- ----------- --------------------- ----------- ----------- --------------------- 
20301                                              012        10          7           2           10          1588.0000             80          29          3684160.0000
20301                                              020        10          10          0           0           1588.0000             80          20          2540800.0000
合计                                                            20          17          2           10          NULL                  NULL        49          62249.6000（所影响的行数为 3 行）
*/

Answer 11

修正数量
select 
  prodcode as 款号,
  prodcolor as 色号,
  sum(case when prodsize='00' then prodcount else 0 end) as S, 
  sum(case when prodsize='01' then prodcount else 0 end) as M, 
  sum(case when prodsize='03' then prodcount else 0 end) as L, 
  sum(case when prodsize='04' then prodcount else 0 end) as XL, 
  prodprice as 价格,
  discount as 折扣,
  sum(prodcount) as 数量合计,
  cast(sum(prodcount*prodprice*discount/100.0) as dec(18,2)) as 金额
from 
  saleinfo
group by 
  prodcode,
  prodcolor,
  prodprice,
  discount
union all
select 
  '合计',
  null,
  sum(case when prodsize='00' then 1 else 0 end) as S, 
  sum(case when prodsize='01' then 1 else 0 end) as M, 
  sum(case when prodsize='03' then 1 else 0 end) as L, 
  sum(case when prodsize='04' then 1 else 0 end) as XL, 
  null,
  null,
  sum(prodcount) as 数量合计,
  cast(sum(prodcount*prodprice*discount/100.0) as dec(18,2)) as 金额
from 
  saleinfo/**
款号                                                 色号         S           M           L           XL          价格                    折扣         数量合计        金额
-------------------------------------------------- ---------- ----------- ----------- ----------- ----------- --------------------- ---------- ----------- ---------------------------------------
20301                                              012        10          7           2           10          1588.00               80         29          36841.60
20301                                              020        10          10          0           0           1588.00               80         20          25408.00
合计                                                 NULL       2           2           1           1           NULL                  NULL       49          62249.60(3 行受影响)**/

Answer 12

动态的不行,因为你的
注：prodsize 尺码转为00-S 01-M 03-L 04-XL
对应转换的.做不到.除非你另外弄个转换表才行.

Answer 13

select prodcode, prodcolor,
       sum(case prodsize when '00' then prodcount else 0 end) [S],
       sum(case prodsize when '01' then prodcount else 0 end) [M],
       sum(case prodsize when '03' then prodcount else 0 end) [L],
       sum(case prodsize when '04' then prodcount else 0 end) [XL],
       max(prodprice) 价格,
       max(discount) 折扣,
       sum(prodcount) 数量合计,
       sum(prodcount*prodprice*discount/100) 金额 --金额 --这里需要除100不？23楼少了这里
from tb
group by prodcode , prodcolor
union all
select '合计' prodcode, ''prodcolor,
       sum(case prodsize when '00' then prodcount else 0 end) [S],
       sum(case prodsize when '01' then prodcount else 0 end) [M],
       sum(case prodsize when '03' then prodcount else 0 end) [L],
       sum(case prodsize when '04' then prodcount else 0 end) [XL],
       null 价格,
       null 折扣,
       sum(prodcount) 数量合计,
       sum(prodcount*prodprice*discount/100) 金额 --这里需要除100不？
from tb