27,579
社区成员
发帖
与我相关
我的任务
分享
select a.code,a.name,a.quantity,
sum(b.quantity) as [tb2.quantity],
sum(c.quantity) as [tb3.quantity]
from tb1 a , tb2 b,tb3 c
where a.code = b.code
and a.code = c.code
group by a.code,a.name,a.quantity
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([code] varchar(4),[name] varchar(5),[quantity] int)
insert [tb1]
select '0001','名称1',10 union all
select '0002','名称2',20 union all
select '0003','名称3',30 union all
select '0004','名称4',30
go
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2]([code] varchar(4),[quantity] int)
insert [TB2]
select '0001',10 union all
select '0002',10 union all
select '0001',20 union all
select '0001',null
go
if object_id('[TB3]') is not null drop table [TB3]
go
create table [TB3]([code] varchar(4),[quantity] int)
insert [TB3]
select '0002',10 union all
select '0002',10 union all
select '0003',20
go
select a.*,isnull(b.quantity,0) as [tb2quantity],isnull(c.quantity,0) as [tb3quantity]
from tb1 a
left join (select code,sum(quantity) as quantity from tb2 group by code) b on a.code=b.code
left join (select code,sum(quantity) as quantity from tb3 group by code) c on a.code=c.code
/**
code name quantity tb2quantity tb3quantity
---- ----- ----------- ----------- -----------
0001 名称1 10 30 0
0002 名称2 20 10 20
0003 名称3 30 0 20
0004 名称4 30 0 0
(4 行受影响)
**/
select a.*,isnull(b.quantity,0) as [tb2quantity],isnull(c.quantity,0) as [tb3quantity]
from tb1 a
left join (select code,sum(quantity) as quantity from tb2 group by code) b on a.code=b.code
left join (select code,sum(quantity) as quantity from tb3 group by code) c on a.code=c.code
select a.code,a.name,a.quantity,sum(b.quantity) as tb2qty,sum(c.quantity) as tb3qty
from tb1 a left join tb2 b on a.code = b.code
left join tb3 c on a.code = c.code
group by a.code,a.name,a.quantity