两张表查询 插入 怎么写

[karxiu]

create table S1(pname nvarchar(10),amt decimal(18,1))

insert into S1  (pname,amt) values ('01',10)
insert into S1  (pname,amt) values ('01',20)
insert into S1  (pname,amt) values ('02',30)
insert into S1  (pname,amt) values ('01',40)
insert into S1  (pname,amt) values ('05',40)
insert into S1  (pname,amt) values ('05',40)

create table S2(pname nvarchar(10),paid decimal(18,1))

insert into S2  (pname,paid ) values ('01',10)
insert into S2  (pname,paid ) values ('02',20)
insert into S2  (pname,paid ) values ('02',30)
insert into S2  (pname,paid ) values ('03',40)
insert into S2  (pname,paid ) values ('04',40)

create table S3(pname nvarchar(10),amt decimal(18,1),paid decimal(18,1))



查询S1与S2然后 结果写入 S3

pname	amt	paid 
01	70	10
02	30	50
03	0	40
04	0	40
05	80	0

S3显示所有的PNAME，如果S1或者S2中没有，相应的数据为0。因为S1与S2是变化的 所有S1与S2中的PNAME 是不确定的

[karxiu]

S3 中 AMT PAID 是S1 与S2中的相应数据的累加

[开着拖拉机泡妞]

create table S1(pname nvarchar(10),amt decimal(18,1))

insert into S1  (pname,amt) values ('01',10)
insert into S1  (pname,amt) values ('01',20)
insert into S1  (pname,amt) values ('02',30)
insert into S1  (pname,amt) values ('01',40)
insert into S1  (pname,amt) values ('05',40)
insert into S1  (pname,amt) values ('05',40)

create table S2(pname nvarchar(10),paid decimal(18,1))

insert into S2  (pname,paid ) values ('01',10)
insert into S2  (pname,paid ) values ('02',20)
insert into S2  (pname,paid ) values ('02',30)
insert into S2  (pname,paid ) values ('03',40)
insert into S2  (pname,paid ) values ('04',40)

create table S3(pname nvarchar(10),amt decimal(18,1),paid decimal(18,1))

insert S3
select apname as pname,isnull(amt,0) as amt,paid from(
select isnull(a.pname,b.pname) as apname,SUM(a.amt) as amt,isnull(b.pname,a.pname) as bpname,
isnull(b.paid,0) paid
 from S1 a full join S2 b on a.pname=b.pname 
 group by a.pname,b.pname,b.paid
)c order by pname

select * from S3

/*
pname	amt	paid
01	70.0	10.0
02	30.0	20.0
02	30.0	30.0
03	0.0	40.0
04	0.0	40.0
05	80.0	0.0
*/

[开着拖拉机泡妞]

create table S1(pname nvarchar(10),amt decimal(18,1))

insert into S1  (pname,amt) values ('01',10)
insert into S1  (pname,amt) values ('01',20)
insert into S1  (pname,amt) values ('02',30)
insert into S1  (pname,amt) values ('01',40)
insert into S1  (pname,amt) values ('05',40)
insert into S1  (pname,amt) values ('05',40)

create table S2(pname nvarchar(10),paid decimal(18,1))

insert into S2  (pname,paid ) values ('01',10)
insert into S2  (pname,paid ) values ('02',20)
insert into S2  (pname,paid ) values ('02',30)
insert into S2  (pname,paid ) values ('03',40)
insert into S2  (pname,paid ) values ('04',40)

create table S3(pname nvarchar(10),amt decimal(18,1),paid decimal(18,1))

insert S3
select apname as pname,sum(isnull(amt,0))  as amt,sum(paid) as paid from(
select isnull(a.pname,b.pname) as apname,SUM(a.amt) as amt,isnull(b.pname,a.pname) as bpname,
isnull(b.paid,0) paid
 from S1 a full join S2 b on a.pname=b.pname 
 group by a.pname,b.pname,b.paid
)c group by apname order by pname 

select * from S3

/*
pname	amt	paid
01	70.0	10.0
02	60.0	50.0
03	0.0	40.0
04	0.0	40.0
05	80.0	0.0
*/

更正一下

[karxiu]

好像是达到要求了 谢谢

[开着拖拉机泡妞]

create table S1(pname nvarchar(10),amt decimal(18,1))

insert into S1  (pname,amt) values ('01',10)
insert into S1  (pname,amt) values ('01',20)
insert into S1  (pname,amt) values ('02',30)
insert into S1  (pname,amt) values ('01',40)
insert into S1  (pname,amt) values ('05',40)
insert into S1  (pname,amt) values ('05',40)

create table S2(pname nvarchar(10),paid decimal(18,1))

insert into S2  (pname,paid ) values ('01',10)
insert into S2  (pname,paid ) values ('02',20)
insert into S2  (pname,paid ) values ('02',30)
insert into S2  (pname,paid ) values ('03',40)
insert into S2  (pname,paid ) values ('04',40)

create table S3(pname nvarchar(10),amt decimal(18,1),paid decimal(18,1))

insert S3
select isnull(ap,bp) as pname,isnull(amt,0) as amt,
isnull(paid,0) as paid from(
select * from(
select pname as ap,SUM(amt) as amt from s1
group by pname)a
full join (select pname as bp,SUM(paid) as paid from s2
group by pname)b on a.ap=b.bp)c
select * from S3
/*
pname	amt	paid
01	70.0	10.0
02	30.0	50.0
03	0.0	40.0
04	0.0	40.0
05	80.0	0.0
*/
楼上作废