case 分组汇总

zhoujianfan 2008-11-24 11:10:36
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

语法错误,这样子不行
请教其它办法做分组汇总
...全文
138 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
ChinaJiaBing 2008-11-24
  • 打赏
  • 举报
回复

你的语句,没有else...
case when ... then .. else .. end
-狙击手- 2008-11-24
  • 打赏
  • 举报
回复
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
yinqi025 2008-11-24
  • 打赏
  • 举报
回复
case when 语法...
dawugui 2008-11-24
  • 打赏
  • 举报
回复
应该是这样,

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
fcuandy 2008-11-24
  • 打赏
  • 举报
回复
没有你这种写法。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执行语句。
只能输出可计算的表达式,或者值。

看明白我写的,你的应该怎么改,你自己改下就是了。

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧