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 total a0 a1 a2 b1 b2 b3 b4 b5
F01 246.00 0.00 0.00 0.00 78.00 0.00 78.00 0.00 102.00
F02 0.00 0.00 0.00 0.00 78.00 0.00 78.00 0.00 102.00
F03 12.00 0.00 0.00 0.00 78.00 0.00 78.00 0.00 102.00
F04 0.00 0.00 0.00 0.00 78.00 0.00 78.00 0.00 102.00
F05 0.00 0.00 0.00 0.00 78.00 0.00 78.00 0.00 102.00
F06 0.00 0.00 0.00 0.00 78.00 0.00 78.00 0.00 102.00
F07 0.00 0.00 0.00 0.00 78.00 0.00 78.00 0.00 102.00
F070107 258.00 0.00 0.00 0.00 78.00 0.00 78.00 0.00 102.00



这个结果集是有问题的
其实就只有F01,F03有数据,F070107 是合计行
正确的应该是:

subjectCode total a0 a1 a2 b1 b2 b3 b4 b5
F01 246.00 0.00 0.00 0.00 78.00 0.00 78.00 0.00 102.00
F02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
F03 12.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 12.00
F04 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
F05 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
F06 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
F07 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
F070107 258.00 0.00 0.00 0.00 78.00 0.00 78.00 0.00 102.00



我想问题就出在group by 那里

我本来想select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'B5' and subjectCode = 'Z' 只要F01有值,但 group by 后 整列 有值了 (包括F02,F03等)

不知道说清楚了没有

看明白的帮忙改下？谢谢！

------解决方案--------------------
看不清楚，列出表结构和简单原始数据
------解决方案--------------------
最好把原始表发出来，这样真看不懂
------解决方案--------------------

SQL code

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 B4
from subject
inner join DetailBudget on subject.subjectCode = DetailBudget.subjectCode and subjectCode = 'Z' 
group by DetailBudget.subjectCode  
order by DetailBudget.subjectCode