高手帮帮忙啊，我卡了很久了。关于交叉表查询汇总的问题。见解给分

Question

SELECT sehao,yanse,
SUM(CASE chicun WHEN 'XS' THEN shuliang ELSE 0 END) AS XS,
SUM(CASE chicun WHEN 'XL' THEN shuliang ELSE 0 END) AS XL,
SUM(CASE chicun WHEN 'S' THEN shuliang ELSE 0 END) AS S,
SUM(CASE chicun WHEN 'M' THEN shuliang ELSE 0 END) AS M,
SUM(shuliang) AS Total
FROM cp_hwmx
GROUP BY sehao,yanse
UNION ALL
SELECT CAST(COUNT(DISTINCT sehao)  AS VARCHAR(10)),
CAST(COUNT(DISTINCT YANSE)  AS VARCHAR(10)),
SUM(CASE chicun WHEN 'XS' THEN shuliang ELSE 0 END) AS XS,
SUM(CASE chicun WHEN 'XL' THEN shuliang ELSE 0 END) AS XL,
SUM(CASE chicun WHEN 'S' THEN shuliang ELSE 0 END) AS S,
SUM(CASE chicun WHEN 'M' THEN shuliang ELSE 0 END) AS M,
SUM(shuliang) AS Total
FROM cp_hwmx

Answer 1

结果：sehao                yanse                XS          XL          S           M           Total       
-------------------- -------------------- ----------- ----------- ----------- ----------- ----------- 
Y01                  YELLOW               88          88          0           0           176
Y02                  YELLOW               0           0           90          100         190
2                    1                    88          88          90          100         366（所影响的行数为 3 行）

Answer 2

create table cp_hwmx(liushui varchar(20),xianghao varchar(20),ddno varchar(20),yanse varchar(20),sehao varchar(20),chicun varchar(10),shuliang int)
insert cp_hwmx select 'F06050001','1A','CP001','YELLOW','Y01','XS',88
union all select 'F06050001','1A','CP001','YELLOW','Y01','XL',88
union all select 'F06050001','1A','CP001','YELLOW','Y02','S',90
union all select 'F06050001','1A','CP002','YELLOW','Y02','M',100declare @sql varchar(8000)
set @sql='select sehao,yanse'
select @sql=@sql+',['+chicun+']=sum(case chicun when '''+chicun+''' then shuliang else 0 end)' from cp_hwmx group by chicun
exec(@sql+',sum(shuliang) as Total into t from cp_hwmx group by sehao,yanse')
declare @sql1 varchar(8000)
set @sql1='select * from t union all select cast(count(distinct sehao) as varchar) as [(色号数)],cast(count(distinct yanse) as varchar) as [(颜色数)]'
select @sql1=@sql1+',sum(['+name+']) as ['+name+']' from syscolumns where id=object_id('t') and name not in('sehao','yanse') order by colid
exec(@sql1+' from t')drop table cp_hwmx,t

Answer 3

-- 2005新增的 pivot 在这种处理中没有什么优势SELECT sehao, yanse, 
[XS] = ISNULL([XS], 0),
[XL] = ISNULL([XL], 0),
[S] = ISNULL([S], 0),
[M] = ISNULL([M], 0),
total = ISNULL([XS], 0) + ISNULL([XL], 0) + ISNULL([S], 0) + ISNULL([M], 0)
FROM(
SELECT sehao, yanse, chicun, shuliang
FROM cp_hwmx
)DATA
PIVOT(
SUM(shuliang)
FOR chicun IN([XS], [XL], [S], [M])
)P
UNION ALL
SELECT 
CAST(COUNT(DISTINCT sehao)  AS VARCHAR(10)),
CAST(COUNT(DISTINCT YANSE)  AS VARCHAR(10)),
SUM(CASE chicun WHEN 'XS' THEN shuliang ELSE 0 END) AS XS,
SUM(CASE chicun WHEN 'XL' THEN shuliang ELSE 0 END) AS XL,
SUM(CASE chicun WHEN 'S' THEN shuliang ELSE 0 END) AS S,
SUM(CASE chicun WHEN 'M' THEN shuliang ELSE 0 END) AS M,
SUM(shuliang) AS Total
FROM cp_hwmx

Answer 4

動態語句，可以只定義一個@SDeclare @S Varchar(8000)
Set @S=''
Select @S=@S+',SUM(Case chicun When '''+chicun+''' Then shuliang Else 0 End) As '+chicun
From cp_hwmx Group By chicun
Select @S='Select sehao,yanse' +@S+',SUM(shuliang) As Total From cp_hwmx Group By sehao,yanse Union All Select Rtrim(Count(Distinct sehao)) As sehao,Rtrim(Count(Distinct yanse)) As yanse'+@S+',SUM(shuliang) As Total From cp_hwmx'
--Select @S
EXEC(@S)

Answer 5

仔细看了一下，你那个尺寸那几项是直接给定的，能不能用什么方法为动态获得有哪些chicun。就像我写的另一个汇总
declare @s varchar(8000)
set @s='select liushui,xianghao,ddno,yanse,sehao'
select @s=@s+',['+chicun+']=sum(case chicun when '''+chicun+''' then shuliang else 0 end)' from cp_hwmx group by chicunexec(@s+' from cp_hwmx group by liushui,xianghao,ddno,yanse,sehao')
这里面的chicun就是动态获得的
还有你的SQL语句在我机上包错：
服务器: 消息 409，级别 16，状态 2，行 1
sum or average aggregate 运算不能以 varchar 数据类型作为参数。
你是否贴错了

Answer 6

--建立測試環境
create table cp_hwmx(liushui varchar(20),xianghao varchar(20),ddno varchar(20),yanse varchar(20),sehao varchar(20),chicun varchar(10),shuliang int)
insert cp_hwmx select 'F06050001','1A','CP001','YELLOW','Y01','XS',88
union all select 'F06050001','1A','CP001','YELLOW','Y01','XL',88
union all select 'F06050001','1A','CP001','YELLOW','Y02','S',90
union all select 'F06050001','1A','CP002','YELLOW','Y02','M',100
--測試
Declare @S Varchar(8000)
Set @S=''
Select @S=@S+',SUM(Case chicun When '''+chicun+''' Then shuliang Else 0 End) As '+chicun
From cp_hwmx Group By chicun
Select @S='Select sehao,yanse' +@S+',SUM(shuliang) As Total From cp_hwmx Group By sehao,yanse Union All Select Rtrim(Count(Distinct sehao)) As sehao,Rtrim(Count(Distinct yanse)) As yanse'+@S+',SUM(shuliang) As Total From cp_hwmx'
--Select @S
EXEC(@S)
--刪除測試環境
Drop Table cp_hwmx
--結果
/*
sehao yanse M S XL XS Total
Y01 YELLOW 0 0 88 88 176
Y02 YELLOW 100 90 0 0 190
2 1 100 90 88 88 366
*/

Answer 7

为什么你们的语句我拿去查询分析器都会报错？我是SQL2000
服务器: 消息 409，级别 16，状态 2，行 1
sum or average aggregate 运算不能以 varchar 数据类型作为参数。

Answer 8

你实际表的shuliang字段的数据类型是不是VARCHAR的？