sql 语句请教
昨天发了个帖，没说明白今天详细说下
sql语句:
select
DetailBudget.subjectCode,
sum(DetailBudget.total) as total,
(select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'A0' and subjectCode = 'Z' ) as a0,
(select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'A1' and subjectCode = 'Z' ) as a1,
(select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'A2' and subjectCode = 'Z' ) as a2,
(select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'B1' and subjectCode = 'Z' ) as b1,
(select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'B2' and subjectCode = 'Z' ) as b2,
(select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'B3' and subjectCode = 'Z' ) as b3,
(select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'B4' and subjectCode = 'Z' ) as b4,
(select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'B5' and subjectCode = 'Z' ) as b5
from subject
inner join DetailBudget on subject.subjectCode = DetailBudget.subjectCode
group by DetailBudget.subjectCode
order by DetailBudget.subjectCode ;
这个sql查询出来的结果集:
subjectCode totala0a1a2b1b2b3b4b5
F01246.000.000.000.0078.000.0078.000.00102.00
F020.000.000.000.0078.000.0078.000.00102.00
F0312.000.000.000.0078.000.0078.000.00102.00
F040.000.000.000.0078.000.0078.000.00102.00
F050.000.000.000.0078.000.0078.000.00102.00
F060.000.000.000.0078.000.0078.000.00102.00
F070.000.000.000.0078.000.0078.000.00102.00
F070107258.000.000.000.0078.000.0078.000.00102.00
这个结果集是有问题的
其实就只有F01,F03有数据,F070107 是合计行
正确的应该是:
subjectCode totala0a1a2b1b2b3b4b5
F01246.000.000.000.0078.000.0078.000.00102.00
F020.000.000.000.000.000.000.000.000.00
F0312.000.000.000.000.000.000.000.0012.00
F040.000.000.000.000.000.000.000.000.00
F050.000.000.000.000.000.000.000.000.00
F060.000.000.000.000.000.000.000.000.00
F070.000.000.000.000.000.000.000.000.00
F070107258.000.000.000.0078.000.0078.000.00102.00
我想问题就出在group by 那里
我本来想select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'B5' and subjectCode = 'Z' 只要F01有值,但 group by 后 整列 有值了 (包括F02,F03等)
不知道说清楚了没有
看明白的帮忙改下？谢谢！
[解决办法]
看不清楚，列出表结构和简单原始数据
[解决办法]
最好把原始表发出来，这样真看不懂
[解决办法]
select DetailBudget.subjectCode,sum(DetailBudget.total) as total,sum(case substring(projectCode,4,2) when 'A0' then DetailBudget.total else 0) as a0,sum(case substring(projectCode,4,2) when 'A1' then DetailBudget.total else 0) as a1,sum(case substring(projectCode,4,2) when 'A2' then DetailBudget.total else 0) as a2,sum(case substring(projectCode,4,2) when 'B1' then DetailBudget.total else 0) as B1,sum(case substring(projectCode,4,2) when 'B2' then DetailBudget.total else 0) as B2,sum(case substring(projectCode,4,2) when 'B3' then DetailBudget.total else 0) as B3,sum(case substring(projectCode,4,2) when 'B4' then DetailBudget.total else 0) as B4from subjectinner join DetailBudget on subject.subjectCode = DetailBudget.subjectCode and subjectCode = 'Z' group by DetailBudget.subjectCode order by DetailBudget.subjectCode
