这样?SELECT zxjelb,SUM(yingzxje)yingzxje,SUM(yzxje)yzxje FROM ( SELECT '上交财政金额' AS zxjelb , SUM(CASE WHEN zxjelb = '上交财政税金' OR zxjelb = '上交财政罚没' OR zxjelb = '上交财政其他' THEN yingzxje ELSE 0 END) AS yingzxje , SUM(CASE WHEN zxjelb = '上交财政税金' OR zxjelb = '上交财政罚没' OR zxjelb = '上交财政其他' THEN yzxje ELSE 0 END) AS yzxje FROM sjjdzxb , sjxmxxb , sjxmzgb , dwjcb WHERE sjjdzxb.myid = sjxmzgb.id AND sjxmzgb.myid = sjxmxxb.id AND dwjcb.dwmc = sjxmxxb.bsjdw AND ( sjjdzxb.sjzt <> 1 OR sjjdzxb.sjzt IS NULL ) AND ( sjxmxxb.sjzt <> 1 OR sjxmxxb.sjzt IS NULL ) AND ( sjxmzgb.sjzt <> 1 OR sjxmzgb.sjzt IS NULL ) AND sjxmxxb.xmsjnd <= '2014' AND sjxmxxb.xmsjnd >= '2001' GROUP BY zxjelb )a GROUP BY zxjelb
还是这样?SELECT zxjelb,SUM(yingzxje)+SUM(yzxje) total FROM ( SELECT '上交财政金额' AS zxjelb , SUM(CASE WHEN zxjelb = '上交财政税金' OR zxjelb = '上交财政罚没' OR zxjelb = '上交财政其他' THEN yingzxje ELSE 0 END) AS yingzxje , SUM(CASE WHEN zxjelb = '上交财政税金' OR zxjelb = '上交财政罚没' OR zxjelb = '上交财政其他' THEN yzxje ELSE 0 END) AS yzxje FROM sjjdzxb , sjxmxxb , sjxmzgb , dwjcb WHERE sjjdzxb.myid = sjxmzgb.id AND sjxmzgb.myid = sjxmxxb.id AND dwjcb.dwmc = sjxmxxb.bsjdw AND ( sjjdzxb.sjzt <> 1 OR sjjdzxb.sjzt IS NULL ) AND ( sjxmxxb.sjzt <> 1 OR sjxmxxb.sjzt IS NULL ) AND ( sjxmzgb.sjzt <> 1 OR sjxmzgb.sjzt IS NULL ) AND sjxmxxb.xmsjnd <= '2014' AND sjxmxxb.xmsjnd >= '2001' GROUP BY zxjelb )a GROUP BY zxjelb
把 group by zxjelb 去掉! 明显内部已经有一个 zxjelb 字段了,还要 select '上交财政金额' as zxjelb 就是作死了。还有 case when zxjelb='上交财政税金' or zxjelb='上交财政罚没' or zxjelb='上交财政其他' then yingzxje else 0 end, 直接把条件放到 WHERE 语句中,不符合的就不参与统计,比起统计 0 简单又快捷。
想根据 zxjelb 分类.. 后面的数据列相加.
FROM (
SELECT '上交财政金额' AS zxjelb ,
SUM(CASE WHEN zxjelb = '上交财政税金'
OR zxjelb = '上交财政罚没'
OR zxjelb = '上交财政其他' THEN yingzxje
ELSE 0
END) AS yingzxje ,
SUM(CASE WHEN zxjelb = '上交财政税金'
OR zxjelb = '上交财政罚没'
OR zxjelb = '上交财政其他' THEN yzxje
ELSE 0
END) AS yzxje
FROM sjjdzxb ,
sjxmxxb ,
sjxmzgb ,
dwjcb
WHERE sjjdzxb.myid = sjxmzgb.id
AND sjxmzgb.myid = sjxmxxb.id
AND dwjcb.dwmc = sjxmxxb.bsjdw
AND ( sjjdzxb.sjzt <> 1
OR sjjdzxb.sjzt IS NULL
)
AND ( sjxmxxb.sjzt <> 1
OR sjxmxxb.sjzt IS NULL
)
AND ( sjxmzgb.sjzt <> 1
OR sjxmzgb.sjzt IS NULL
)
AND sjxmxxb.xmsjnd <= '2014'
AND sjxmxxb.xmsjnd >= '2001'
GROUP BY zxjelb
)a
GROUP BY zxjelb
FROM (
SELECT '上交财政金额' AS zxjelb ,
SUM(CASE WHEN zxjelb = '上交财政税金'
OR zxjelb = '上交财政罚没'
OR zxjelb = '上交财政其他' THEN yingzxje
ELSE 0
END) AS yingzxje ,
SUM(CASE WHEN zxjelb = '上交财政税金'
OR zxjelb = '上交财政罚没'
OR zxjelb = '上交财政其他' THEN yzxje
ELSE 0
END) AS yzxje
FROM sjjdzxb ,
sjxmxxb ,
sjxmzgb ,
dwjcb
WHERE sjjdzxb.myid = sjxmzgb.id
AND sjxmzgb.myid = sjxmxxb.id
AND dwjcb.dwmc = sjxmxxb.bsjdw
AND ( sjjdzxb.sjzt <> 1
OR sjjdzxb.sjzt IS NULL
)
AND ( sjxmxxb.sjzt <> 1
OR sjxmxxb.sjzt IS NULL
)
AND ( sjxmzgb.sjzt <> 1
OR sjxmzgb.sjzt IS NULL
)
AND sjxmxxb.xmsjnd <= '2014'
AND sjxmxxb.xmsjnd >= '2001'
GROUP BY zxjelb
)a
GROUP BY zxjelb
明显内部已经有一个 zxjelb 字段了,还要 select '上交财政金额' as zxjelb 就是作死了。还有 case when zxjelb='上交财政税金' or zxjelb='上交财政罚没' or zxjelb='上交财政其他' then yingzxje else 0 end,
直接把条件放到 WHERE 语句中,不符合的就不参与统计,比起统计 0 简单又快捷。