Join后，再Sum的问题

[ofaint]

任务表 Task
编号 车辆 运费
1 T1 1000
2 T2 1500
3 T2 800

费用表 Fee
编号 任务编号 费用项 金额
1 1 过路费 100
2 1 补胎费 20
3 1 罚款 50
4 2 过路费 80
5 3 过路费 120
6 3 罚款 60

我想得到这样的结果：

车辆 运费 过路费 补胎费 罚款
T1 1000 100 20 50
T2 2300 200 60

[lin_now]

先做沙发

[ofaint]

两个表Join之后，车辆、运费重复行，如何不重复地对运费求和？
后面的费用行转变成列，已经解决

或许，整个思路是错误的？

[gahade]

create table Task(编号 int,车辆 varchar(10),运费 int)
insert into task
select 1,'T1',1000
union all select 2,'T2',1500
union all select 3,'T2',800

create table Fee(编号 int,任务编号 int,费用项 varchar(20),金额 int)
insert into Fee
select 1,1,'过路费',100
union all select 2,1,'补胎费',20
union all select 3,1,'罚款',50
union all select 4,2,'过路费',80
union all select 5,3,'过路费',120
union all select 6,3,'罚款',60

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when 费用项 = '''+费用项+''' then 金额 else 0 end) as '''+费用项+''''
from (select 费用项,sum(金额) as '金额' from Fee,Task where Fee.任务编号=Task.编号 group by 费用项)t
exec('select 车辆,sum(运费) as ''运费'''+@sql+' from Task,Fee where Task.编号=Fee.任务编号 group by 车辆')
/*
车辆 运费 补胎费 罚款 过路费
---------- ----------- ----------- ----------- -----------
T1 3000 20 50 100
T2 3100 0 60 200
*/

[ofaint]

gahade，运费错了

车辆 运费 过路费 补胎费 罚款
T1 1000 100 20 50
T2 2300 200 60

[ofaint]

Join之后，导致任务表的记录重复了，运费求和就不正确了

[paoluo]

Create Table Task(编号 Int,车辆 Varchar(10),运费 Int)
Insert Into task
Select 1,'T1',1000
Union All Select 2,'T2',1500
Union All Select 3,'T2',800

Create Table Fee(编号 Int,任务编号 Int,费用项 Nvarchar(20),金额 Int)
Insert Into Fee
Select 1,1,N'过路费',100
Union All Select 2,1,N'补胎费',20
Union All Select 3,1,N'罚款',50
Union All Select 4,2,N'过路费',80
Union All Select 5,3,N'过路费',120
Union All Select 6,3,N'罚款',60
GO
Declare @S Nvarchar(4000), @S1 Nvarchar(4000)
Select @S='', @S1 = ''
Select @S=@S + N',SUM(Case When 费用项 = N'''+费用项+N''' Then 金额 Else 0 End) As '''+费用项+'''', @S1 = @S1 + ',SUM(B.' + 费用项 + ') As ' + 费用项
From Fee Group By 费用项
EXEC(N'Select A.车辆,SUM(A.运费) As ''运费'''+@S1 + N' From Task A,(Select 任务编号' + @S + N' From Fee Group By 任务编号 ) B where A.编号=B.任务编号 Group By A.车辆' )

Drop Table Task,Fee
--Result
/*
车辆 运费 补胎费 罚款 过路费
T1 1000 50 20 100
T2 2300 60 0 200
*/

[dawugui]

if object_id('pubs..Task') is not null
drop table Task
go

create table Task(编号 int,车辆 varchar(10),运费 int)
insert into Task(编号,车辆,运费) values(1, 'T1', 1000)
insert into Task(编号,车辆,运费) values(2, 'T2', 1500)
insert into Task(编号,车辆,运费) values(3, 'T2', 800)
go

if object_id('pubs..Fee') is not null
drop table Fee
go

create table Fee(编号 int,任务编号 int,费用项 varchar(10),金额 int)
insert into Fee(编号,任务编号,费用项,金额) values(1, 1, '过路费', 100)
insert into Fee(编号,任务编号,费用项,金额) values(2, 1, '补胎费', 20)
insert into Fee(编号,任务编号,费用项,金额) values(3, 1, '罚款 ', 50)
insert into Fee(编号,任务编号,费用项,金额) values(4, 2, '过路费', 80)
insert into Fee(编号,任务编号,费用项,金额) values(5, 3, '过路费', 120)
insert into Fee(编号,任务编号,费用项,金额) values(6, 3, '罚款 ', 60)
go

select 车辆 , sum(运费) 运费 , sum(过路费) 过路费 , sum(补胎费) 补胎费, sum(罚款) 罚款 from
(
select Task.车辆 , task.运费 , t.过路费 , t.补胎费 , t.罚款 from task,
(
select 任务编号,
sum(case when 费用项 = '过路费' then 金额 else 0 end) as 过路费,
sum(case when 费用项 = '补胎费' then 金额 else 0 end) as 补胎费,
sum(case when 费用项 = '罚款' then 金额 else 0 end) as 罚款
from fee
group by 任务编号
) t
where task.编号 = t.任务编号
) m
group by 车辆

drop table Fee
drop table Task

/*
车辆 运费 过路费 补胎费 罚款
---------- ----------- ----------- ----------- -----------
T1 1000 100 20 50
T2 2300 200 0 60

（所影响的行数为 2 行）

*/

[gaoninggao]

select 车辆, sum(运费) 运费 , sum((case when 费用项='过路费' then 金额 else 0 end )) as 过路费,
sum((case when 费用项='补胎费' then 金额 else 0 end )) as 补胎费
,sum((case when 费用项='罚款' then 金额 else 0 end )) as 罚款
from task join fee on task.编号=fee.任务编号 group by 车辆

[ofaint]

gaoninggao，你的语句，会导致运费重复计算。
谢谢paoluo(一天到晚游泳的鱼)dawugui(潇洒老乌龟)，谢谢CCTV

[paoluo]

ofaint() ， 你的費用項是固定只有那麼三種嗎？