sql 行转列 问题

[huangwenquan123]

--测试代码--
create table product(id int not null primary key identity(1,1),name varchar(50),
jiage varchar(50),changdi varchar(50),leibie varchar(50))

insert into product(name,jiage,changdi,leibie) values('苹果','5元','浙江','水果')
insert into product(name,jiage,changdi,leibie) values('香蕉','20元','上海','水果')
insert into product(name,jiage,changdi,leibie) values('黄瓜','1元','浙江','蔬菜')
insert into product(name,jiage,changdi,leibie) values('西红柿','2元','浙江','蔬菜')

--我的代码--
select
sum(case leibie when '水果' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 水果,
sum(case leibie when '蔬菜' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 蔬菜
from product group by leibie
/*
水果     蔬菜
NULL	3
25	NULL
--怎么把null去掉 就只有一行，不要两行呢？--
*/

[SQL77]

select
sum(case leibie when '水果' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 水果,
sum(case leibie when '蔬菜' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 蔬菜
from product --group by leibie

[huangwenquan123]

[Quote=引用 1 楼 sql77 的回复:]
select
sum(case leibie when '水果' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 水果,
sum(case leibie when '蔬菜' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 蔬菜
from product --……
[/Quote]额= =我傻了，没想到我改了都不知道
之前是这样的

select
case leibie when '水果' then sum(cast(substring(jiage,1,len(jiage)-1) as int)) end as 水果,
case leibie when '蔬菜' then sum(cast(substring(jiage,1,len(jiage)-1) as int)) end as 蔬菜
from product
--然后出错   选择列表中的列 'product.leibie' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。--

[huangwenquan123]

[Quote=引用 1 楼 sql77 的回复:]
select
sum(case leibie when '水果' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 水果,
sum(case leibie when '蔬菜' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 蔬菜
from product --……
[/Quote]请叫77哥，这两个有什么差？

[xyj052]

用pivot不是更好吗

[huangwenquan123]

因为sum是聚合函数，要放在外面执行？

[AcHerat]

select
max(case leibie when '水果' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 水果,
max(case leibie when '蔬菜' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 蔬菜
from product group by leibie

[AcHerat]

select
sum(isnull(case leibie when '水果' then cast(substring(jiage,1,len(jiage)-1) as int) end),0) as 水果,
sum(isnull(case leibie when '蔬菜' then cast(substring(jiage,1,len(jiage)-1) as int) end),0) as 蔬菜
from product group by leibie

[快溜]

[Quote=引用 5 楼 huangwenquan123 的回复:]
因为sum是聚合函数，要放在外面执行？
[/Quote]
group by leibie表示按leibie分组。

[huangwenquan123]

恩 我知道group by leibie是按leibie分组，而leibie有水果和蔬菜，所以会有两行，这个是刚疏忽了

现在想问的是

select
case leibie when '水果' then sum(cast(substring(jiage,1,len(jiage)-1) as int)) end as 水果,
case leibie when '蔬菜' then sum(cast(substring(jiage,1,len(jiage)-1) as int)) end as 蔬菜
from product

select
sum(case leibie when '水果' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 水果,
sum(case leibie when '蔬菜' then cast(substring(jiage,1,len(jiage)-1) as int) end) as 蔬菜
from 
/*
这两个一个sum在then后面 一个sum在case外面，在then后面的执行不成功，
是因为sum是聚合函数，在里面的话统计不了值，因为是一行一行执行的。是这个原因吗？还是什么原因？
*/

[SQL77]

[Quote=引用 9 楼 huangwenquan123 的回复:]
恩 我知道group by leibie是按leibie分组，而leibie有水果和蔬菜，所以会有两行，这个是刚疏忽了

现在想问的是


SQL code

select
case leibie when '水果' then sum(cast(substring(jiage,1,len(jiage)-1) as int)) end as 水果,
case leibie when……
[/Quote]
你在SUM外边肯定不行啦，未包含在聚合里边

[AcHerat]

[Quote=引用 9 楼 huangwenquan123 的回复:]

恩 我知道group by leibie是按leibie分组，而leibie有水果和蔬菜，所以会有两行，这个是刚疏忽了

现在想问的是

SQL code

select
case leibie when '水果' then sum(cast(substring(jiage,1,len(jiage)-1) as int)) end as 水果,
case leibie when '蔬菜……
[/Quote]

case when ... then (select sum() from tb group by ...) end 这样可以，不然没有聚合的！

[huangwenquan123]

tank you