-
本版专家分：63结帖率 99.4% -
select mo_no,mat_no,sto_type,
case when type='init' then
sum(qty1) as init1,sum(qty2) as init2,sum(qty3) as init3,
sum(0) as rec1,sum(0) as rec2,sum(0) as rec3,
sum(0) as iss1,sum(0) as iss2,sum(0) as iss3,
sum(0) as rtn1,sum(0) as rtn2,sum(0) as rtn3,
sum(0) as moto1,sum(0) as moto2,sum(0) as moto3,
sum(0) as moti1,sum(0) as moti2,sum(0) as moti3
when type='rec' then
sum(0) as init1,sum(0) as init2,sum(0) as init3,
sum(qty1) as rec1,sum(qty2) as rec2,sum(qty3) as rec3,
sum(0) as iss1,sum(0) as iss2,sum(0) as iss3,
sum(0) as rtn1,sum(0) as rtn2,sum(0) as rtn3,
sum(0) as moto1,sum(0) as moto2,sum(0) as moto3,
sum(0) as moti1,sum(0) as moti2,sum(0) as moti3
when type='iss' then
sum(0) as init1,sum(0) as init2,sum(0) as init3,
sum(0) as rec1,sum(0) as rec2,sum(0) as rec3,
sum(qty1) as iss1,sum(qty2) as iss2,sum(qty3) as iss3,
sum(0) as rtn1,sum(0) as rtn2,sum(0) as rtn3,
sum(0) as moto1,sum(0) as moto2,sum(0) as moto3,
sum(0) as moti1,sum(0) as moti2,sum(0) as moti3
when type='rtn' then
sum(0) as init1,sum(0) as init2,sum(0) as init3,
sum(0) as rec1,sum(0) as rec2,sum(0) as rec3,
sum(0) as iss1,sum(0) as iss2,sum(0) as iss3,
sum(qty1) as rtn1,sum(qty2) as rtn2,sum(qty3) as rtn3,
sum(0) as moto1,sum(0) as moto2,sum(0) as moto3,
sum(0) as moti1,sum(0) as moti2,sum(0) as moti3
when type='moto' then
sum(0) as init1,sum(0) as init2,sum(0) as init3,
sum(0) as rec1,sum(0) as rec2,sum(0) as rec3,
sum(0) as iss1,sum(0) as iss2,sum(0) as iss3,
sum(0) as rtn1,sum(0) as rtn2,sum(0) as rtn3,
sum(qty1) as moto1,sum(qty2) as moto2,sum(qty3) as moto3,
sum(0) as moti1,sum(0) as moti2,sum(0) as moti3
when type='moti' then
sum(0) as init1,sum(0) as init2,sum(0) as init3,
sum(0) as rec1,sum(0) as rec2,sum(0) as rec3,
sum(0) as iss1,sum(0) as iss2,sum(0) as iss3,
sum(0) as rtn1,sum(0) as rtn2,sum(0) as rtn3,
sum(0) as moto1,sum(0) as moto2,sum(0) as moto3,
sum(qty1) as moti1,sum(qty2) as moti2,sum(qty3) as moti3
end
into #balance
from #tmp
group by mo_no,mat_no,sto_type
语法错误,这样子不行
请教其它办法做分组汇总![]()
-
本版专家分：68909
-
- 金牌 2007年11月 总版技术专家分月排行榜第一
-
-
蓝花
2008年11月 MS-SQL Server大版内专家分月排行榜第三
2008年10月 MS-SQL Server大版内专家分月排行榜第三
2006年7月 MS-SQL Server大版内专家分月排行榜第三
-
-
没有你这种写法。case when 输出值。
select case when a=1 then 'aa' when a=2 then 'bb' as x end,
case when a=1 then 111 when a=2 then 222 as y end,..
而不能
select case when a=1 then 'aa' x ,111 y when a=2 then 'bb' x,222 y end ...
case when 不能输出sql执行语句。
只能输出可计算的表达式，或者值。
看明白我写的，你的应该怎么改，你自己改下就是了。
-
本版专家分：440973
-
- 名人 年度总版至少三次排名前十即授予名人勋章
-
- 榜眼 2008年 总版技术专家分年内排行榜第二
-
-
进士
2010年 总版技术专家分年内排行榜第四
2009年 总版技术专家分年内排行榜第五
2007年 总版技术专家分年内排行榜第六
-
-
金牌
2009年3月 总版技术专家分月排行榜第一
2008年12月 总版技术专家分月排行榜第一
2008年3月 总版技术专家分月排行榜第一
2007年12月 总版技术专家分月排行榜第一
2007年10月 总版技术专家分月排行榜第一
-
-
应该是这样,
select mo_no,mat_no,sto_type,
sum(case type when 'init' then qty1 then 0 end) [init],
sum(case type when 'rec' then qty1 then 0 end) [rec],
...
sum(case type when 'moti' then qty1 then 0 end) [moti]
into #balance
from #tmp
group by mo_no,mat_no,sto_type
-
本版专家分：140746
-
- 进士 2008年 总版技术专家分年内排行榜第六
-
- 银牌 2007年12月 总版技术专家分月排行榜第二
-
-
微软MVP
2011年7月 荣获微软MVP称号
2009年7月 荣获微软MVP称号
2010年7月 荣获微软MVP称号
2008年7月 荣获微软MVP称号
-
-
黄花
2008年4月 MS-SQL Server大版内专家分月排行榜第二
2008年3月 MS-SQL Server大版内专家分月排行榜第二
2008年1月 MS-SQL Server大版内专家分月排行榜第二
2007年12月 MS-SQL Server大版内专家分月排行榜第二
-
-
case when type='init' then
sum(qty1) as init1,sum(qty2) as init2,sum(qty3) as init3,
sum(0) as rec1,sum(0) as rec2,sum(0) as rec3,
sum(0) as iss1,sum(0) as iss2,sum(0) as iss3,
sum(0) as rtn1,sum(0) as rtn2,sum(0) as rtn3,
sum(0) as moto1,sum(0) as moto2,sum(0) as moto3,
sum(0) as moti1,sum(0) as moti2,sum(0) as moti3
--
sum(case when type='init' then qty1 else 0 end) as init1
-
本版专家分：21129 -
你的语句,没有else...
case when ... then .. else .. end
![]()
