写SQL语句,请教。(50分)

H

Hecules

Unregistered / Unconfirmed
GUEST, unregistred user!
有个数据表,表名:scb
表结构为:
mc,js,zl,gg,sfzd
(名称,件数,重量,规格,是否自带)
数据内容为:
mc js zl gg sfzd
---------------------------------
青菜 1 1.5 大 是
白菜 3 2 小 否
青菜 2 3 大 是
白菜 4 3 小 是
白菜 6 10 大 是
....
要的统计结果为:
mc gg zjs zzl zdjs zdzl fzdjs fzdzl
---------------------------------------------------
青菜 大 3 4.5 3 4.5 0 0
白菜 小 7 4 4 3 3 2
白菜 大 6 10 6 10 0 0
....
说明,统计是:是按名称和规格统计的.统计出:
总件数,和,总重量,
自带件数,和,自带重量.
非自带件数,和,非自带重量,
(总件数-自自带件数=非自带件数
总重量-自带重量=非自带重量)
请教,用一条SQL,如何实现。谢谢。
 
使用CASE
简单一点,用一例,没验证。
SELECT mc,gg,SUM(CASE WHEN sfzd='是' then
zjs else
0 END) AS ZJS,
.......
FROM scb
GROUP BY mc,gg
 
select mc,gg,sum(js) as js,sum(zl) as zl,
sum(Case when sfzd="是" js else
0 end ) as zdjs,
sum(case when sfzd="是" zl else
0 end )as zdzl,
sum(Case when sfzd="否" js else
0 end ) as fzdjs
sum(case when sfzd="否" zl else
0 end )as fzdzl,
from scb
group by mc,gg
没有调试
 
不好意思,刚才写错了
select mc,gg,sum(js) as js,sum(zl) as zl,
sum(Case when sfzd="是" then
js else
0 end ) as zdjs,
sum(case when sfzd="是" then
zl else
0 end )as zdzl,
sum(Case when sfzd="否" then
js else
0 end ) as fzdjs
sum(case when sfzd="否" then
zl else
0 end )as fzdzl,
from scb
group by mc,gg
 
SELECT mc,gg,sum(js) as zjs,sum(zl) as zzl,sum(case when sfzd='是' then
js else
0 end) as zdjs
,sum(case when sfzd='是' then
zl else
0 end) as zdzl,sum(js)-sum(case when sfzd='是' then
js else
0 end) as fzdjs,
sum(zl)-sum(case when sfzd='是' then
zl else
0 end) as fzdzl
FROM scb
GROUP BY mc,gg
已经过验证!!
 
好像还是晚了一步哦!!:)
 
多人接受答案了。
 
顶部