22,199
社区成员
发帖
与我相关
我的任务
分享
create table tb1(OrderNo varchar(10),Name varchar(10),Color varchar(10), AtAmount int, Place varchar(10))
insert into tb1 values('A01', 'AA', 'Red' , 60 , 'A1')
insert into tb1 values('A01', 'AA', 'Yellow', 500 , 'A2')
insert into tb1 values('A02', 'BB', 'Bule' , 0 , 'A3')
insert into tb1 values('A03', 'CC', 'Black' , 300 , 'A4')
insert into tb1 values('A04', 'DD', 'White' , 400 , 'A5')
create table tb2(OrderNo varchar(10),Name varchar(10),Color varchar(10),InAmount int, Place varchar(10))
insert into tb2 values('A01' , 'AA' , 'Red ' ,50 , 'A1 ')
insert into tb2 values('A01' , 'AA' , 'Red ' ,50 , 'A1 ')
insert into tb2 values('A01' , 'AA' , 'Yellow' ,500 , 'A2 ')
insert into tb2 values('A02' , 'BB' , 'Bule ' ,100 , 'A3 ')
insert into tb2 values('A03' , 'CC' , 'Black ' ,300 , 'A4 ')
insert into tb2 values('A04' , 'DD' , 'White ' ,400 , 'A5 ')
create table tb3(OrderNo varchar(10) ,Name varchar(10),Color varchar(10),OutAmount int, Place varchar(10))
insert into tb3 values('A01' , 'AA' , 'Red ' , 20 , 'A1 ')
insert into tb3 values('A01' , 'AA' , 'Red ' , 20 , 'A1 ')
insert into tb3 values('A02' , 'BB' , 'Bule' , 100 , 'A3 ')
go
select OrderNo, Name ,Color, sum(InAmount) InAmount , sum(AtAmount) AtAmount , sum(OutAmount) OutAmount, Place from
(
select OrderNo, Name ,Color , InAmount =0 , AtAmount , OutAmount = 0, Place from tb1
union all
select OrderNo, Name ,Color , InAmount , AtAmount =0 , OutAmount = 0, Place from tb2
union all
select OrderNo, Name ,Color , InAmount =0, AtAmount =0 , OutAmount , Place from tb3
) t
group by OrderNo, Name ,Color, Place
drop table tb1,tb2,tb3
/*
OrderNo Name Color InAmount AtAmount OutAmount Place
---------- ---------- ---------- ----------- ----------- ----------- ----------
A01 AA Red 100 60 40 A1
A01 AA Yellow 500 500 0 A2
A02 BB Bule 100 0 100 A3
A03 CC Black 300 300 0 A4
A04 DD White 400 400 0 A5
(所影响的行数为 5 行)
*/
if object_id('tb1') is not null drop table tb1
go
create table tb1(OrderNo varchar(10),Name varchar(10), Color varchar(10), AtAmount int, Place varchar(10))
insert into tb1 select
'A01', 'AA', 'Red' , 60 , 'A1' union all select
'A01', 'AA', 'Yellow', 500 , 'A2' union all select
'A02', 'BB', 'Bule' , 0 , 'A3' union all select
'A03', 'CC', 'Black' , 300 , 'A4' union all select
'A04', 'DD', 'White' , 400 , 'A5'
if object_id('tb2') is not null drop table tb2
go
create table tb2(OrderNo varchar(10),Name varchar(10), Color varchar(10),inAmount int, Place varchar(10))
insert into tb2 select
'A01', 'AA', 'Red' , 50 , 'A1' union all select
'A01', 'AA', 'Red' , 50 , 'A1' union all select
'A01', 'AA' , 'Yellow', 500 , 'A2' union all select
'A02', 'BB' , 'Bule' , 100 , 'A3' union all select
'A03', 'CC' , 'BLACK' , 300 , 'A4' union all select
'A04', 'DD' , 'White', 400 , 'A5'
if object_id('tb3') is not null drop table tb3
go
create table tb3(OrderNo varchar(10),Name varchar(10), Color varchar(10), outAmount int, Place varchar(10))
insert into tb3 select
'A01', 'AA' , 'Red', 20 , 'A1' union all select
'A01', 'AA' , 'Red' , 20 , 'A1' union all select
'A02', 'BB', 'Bule' , 100 , 'A3'
select a.OrderNo,a.Name,a.Color,
InAmount=isnull(InAmount,0),
AtAmount=isnull(AtAmount,0),
OutAmount=isnull(OutAmount,0),a.Place
from tb1 a
left join
(select OrderNo, Name,Color,InAmount=sum(InAmount)
from tb2 group by OrderNo, Name,Color) b
on a.OrderNo=b.OrderNo and a.Name=b.Name and a.Color=b.Color
left join
(select OrderNo, Name,Color,outAmount=sum(outAmount)
from tb3 group by OrderNo, Name,Color)c
on a.OrderNo=c.OrderNo and a.Name=c.Name and a.Color=c.Color
OrderNo Name Color InAmount AtAmount OutAmount Place
---------- ---------- ---------- ----------- ----------- ----------- ----------
A01 AA Red 100 60 40 A1
A01 AA Yellow 500 500 0 A2
A02 BB Bule 100 0 100 A3
A03 CC Black 300 300 0 A4
A04 DD White 400 400 0 A5
(5 行受影响)