sql 语句请教

Question

select DetailBudget.subjectCode,(select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'B5' and subjectCode = 'Z' )  as b5from subjectinner join DetailBudget on subject.subjectCode = DetailBudget.subjectCodegroup by DetailBudget.subjectCode order by DetailBudget.subjectCode查询结果如下:subjectCode total b5
F01 168.00 90.00
F02 0.00 90.00
F03 0.00 90.00
F04 0.00 90.00
F05 0.00 90.00
F06 0.00 90.00
F07 0.00 90.00
F070107 168.00 90.00我想实现如下结果
subjectCode total b5
F01 168.00 90.00
F02 0.00 00.00
F03 0.00 00.00
F04 0.00 00.00
F05 0.00 00.00
F06 0.00 00.00
F07 0.00 00.00
F070107 168.00 90.00
sql语句怎么写 请教？主要是group by 的问题

Answer 1

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([subjectCode] varchar(7),[total] numeric(5,2),[b5] numeric(4,2))
insert [test]
select 'F01',168.00,90.00 union all
select 'F02',0.00,90.00 union all
select 'F03',0.00,90.00 union all
select 'F04',0.00,90.00 union all
select 'F05',0.00,90.00 union all
select 'F06',0.00,90.00 union all
select 'F07',0.00,90.00 union all
select 'F070107',168.00,90.00with t
as(
select px=ROW_NUMBER()over(partition by len([subjectCode]) order by (select 1)),
* from test
)
select [subjectCode],[total],
case when exists(select 1 from t b 
where a.px=b.px+1 and LEN(a.subjectCode)=LEN(b.subjectCode)) 
then 0 else [b5] end as [b5]
from t a
order by 1/*
subjectCode total b5
---------------------------
F01 168.00 90.00
F02 0.00 0.00
F03 0.00 0.00
F04 0.00 0.00
F05 0.00 0.00
F06 0.00 0.00
F07 0.00 0.00
F070107 168.00 90.00
*/

Answer 2

查询结果中的total是怎么来的？你的SQL语句中没有这个。

Answer 3

select DetailBudget.subjectCode,sum(DetailBudget.total) as total,(select sum(DetailBudget.total) from DetailBudget where substring(projectCode,4,2) = 'B5' and subjectCode = 'Z' )  as b5from subjectinner join DetailBudget on subject.subjectCode = DetailBudget.subjectCodegroup by DetailBudget.subjectCode order by DetailBudget.subjectCode