表t1(code1,name1)
1 t1n1
2 t1n2
3 t1n3
表t2(code2,name2,price)
1 t2n1 5
2 t2n2 10
3 t2n3 30
表t1_2(code1,code2,amount)
1 1 1
1 2 2
2 3 1想得到结果(fee为price*amount)
code1 name1 fee
1 t1n1 25
2 t1n2 30
3 t1n3 NULL
1 t1n1
2 t1n2
3 t1n3
表t2(code2,name2,price)
1 t2n1 5
2 t2n2 10
3 t2n3 30
表t1_2(code1,code2,amount)
1 1 1
1 2 2
2 3 1想得到结果(fee为price*amount)
code1 name1 fee
1 t1n1 25
2 t1n2 30
3 t1n3 NULL
a.code1,a.name1,sum(isnull(b.price,0)*isnull(c.amount,0)) as fee
from
t1 a
left join
t1_2 c
on
a.code1=c.code1
left join
t2 b
on
b.code2=c.code2
if object_id('[t1]') is not null drop table [t1]
create table [t1]([code1] int,[name1] varchar(4))
go
insert [t1]
select 1,'t1n1' union all
select 2,'t1n2' union all
select 3,'t1n3'
--> 测试数据:[t2]
if object_id('[t2]') is not null drop table [t2]
create table [t2]([code2] int,[name2] varchar(4),[price] int)
go
insert [t2]
select 1,'t2n1',5 union all
select 2,'t2n2',10 union all
select 3,'t2n3',30
--> 测试数据:[t1_2]
if object_id('[t1_2]') is not null drop table [t1_2]
create table [t1_2]([code1] int,[code2] int,[amount] int)
go
insert [t1_2]
select 1,1,1 union all
select 1,2,2 union all
select 2,3,1select t.code1,t.name1,sum(r.amount*h.price) as price
from [t1] t left join [t1_2] r
on t.code1 = r.code1 left join [t2] h
on r.code2 = h.code2
group by t.code1,t.name1
code1 name1 price
----------- ----- -----------
1 t1n1 25
2 t1n2 30
3 t1n3 NULL(所影响的行数为 3 行)警告: 聚合或其它 SET 操作消除了空值。
a.code1,a.name1,sum(isnull(b.price,0)*isnull(c.amount,0)) as fee
from
t1 a
left join
t1_2 c
on
a.code1=c.code1
left join
t2 b
on
b.code2=c.code2
group by
a.code1,a.name1
if object_id('[t1]') is not null drop table [t1]
create table [t1]([code1] int,[name1] varchar(4))
insert [t1]
select 1,'t1n1' union all
select 2,'t1n2' union all
select 3,'t1n3'select * from [t1]
--> 测试数据:[t2]
if object_id('[t2]') is not null drop table [t2]
create table [t2]([code2] int,[name2] varchar(4),[price] int)
insert [t2]
select 1,'t2n1',5 union all
select 2,'t2n2',10 union all
select 3,'t2n3',30select * from [t2]
--> 测试数据:[t1_2]
if object_id('[t1_2]') is not null drop table [t1_2]
create table [t1_2]([code1] int,[code2] int,[amount] int)
insert [t1_2]
select 1,1,1 union all
select 1,2,2 union all
select 2,3,1select a.*,sum(b.[amount]*c.[price]) from [t1] a
left join [t1_2] b on a.[code1]=b.[code1]
left join [t2] c on b.[code2]=c.[code2]
group by a.code1,a.name1
/*
code1 name1
----------- ----- -----------
1 t1n1 25
2 t1n2 30
3 t1n3 NULL
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
*/
(select b.code1, a.code2,a.price*b.amount as amount from t2 a left join t1_2 b on a.code2=b.code2) b
on a.code1= b.code1
group by a.code1,a.name1