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',800create 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,'罚款',60declare @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 */
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',800Create 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 */
if object_id('pubs..Task') is not null drop table Task gocreate 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) goif object_id('pubs..Fee') is not null drop table Fee gocreate 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) goselect 车辆 , 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 行)*/
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 车辆
insert into task
select 1,'T1',1000
union all select 2,'T2',1500
union all select 3,'T2',800create 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,'罚款',60declare @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
*/
T1 1000 100 20 50
T2 2300 200 60
Insert Into task
Select 1,'T1',1000
Union All Select 2,'T2',1500
Union All Select 3,'T2',800Create 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
*/
drop table Task
gocreate 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)
goif object_id('pubs..Fee') is not null
drop table Fee
gocreate 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)
goselect 车辆 , 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 行)*/
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 车辆
谢谢paoluo(一天到晚游泳的鱼)dawugui(潇洒老乌龟),谢谢CCTV