联合查询的方法
现有以下两个表：
表A：
product machine model mdate aqty
pa 5 1 2013-6-3 1000
pa 6 1 2013-6-4 3000
pa 6 1 2013-6-3 1500
表B：
product machine model mdate bqty
pa 6 1 2013-6-3 5000
pa 6 1 2013-6-4 4000
要得到表C：
product machine model mdate cqty1 cqty2
pa 5 1 2013-6-3 1000 0
pa 6 1 2013-6-3 1500 5000
pa 6 1 2013-6-4 3000 4000
--------------------
请问各位如何编写SQL句子？谢谢。
-------------------
[解决办法]
select 'pa' as product,5 as machine,1 as model,'2013-6-3' as mdate,1000 as aqty into #A
union select 'pa' as product,6 as machine,1 as model,'2013-6-4' as mdate,3000 as aqty
union select 'pa' as product,6 as machine,1 as model,'2013-6-3' as mdate,1500 as aqty
select 'pa' as product,6 as machine,1 as model,'2013-6-3' as mdate,5000 as bqty into #B
union select 'pa' as product,6 as machine,1 as model,'2013-6-4' as mdate,4000 as bqty
select * from #A
select * from #B
select a.product,a.machine,a.model,a.mdate,a.aqty,b.bqty from #A a
left join #B b on a.product=b.product and a.machine=b.machine and a.model=b.model and a.mdate=b.mdate
union
select a.product,a.machine,a.model,a.mdate,b.aqty,a.bqty from #B a
left join #A b on a.product=b.product and a.machine=b.machine and a.model=b.model and a.mdate=b.mdate
------解决方案--------------------
没看懂 aqty bqty cqty1 之间的关系
[解决办法]
select A.product,
A.machine,
A.model,
A.mdate,
cqty1=case when A.aqty is null then 0 else A.aqty
cqty2=case when B.bqty is null then 0 else B.bqty
from A
left join B
on A.product=B.product
AND A.machine=B.machine
AND A.model=B.model
AND A.mdate=B.mdate
[解决办法]
if(OBJECT_ID('dbo.ta') is not null)
drop table dbo.ta
GO
create table ta
(
product varchar(10) not null,
machine int,
model int,
mdate smalldatetime,
aqty int
)
--
insert into ta
select 'pa',5,1,'2013-6-3',1000 union
select 'pa',6,1,'2013-6-4',3000 union
select 'pa',6,1,'2013-6-3',1500
if(OBJECT_ID('dbo.tb') is not null)
drop table dbo.tb
GO
create table tb
(
product varchar(10) not null,
machine int,
model int,
mdate smalldatetime,
bqty int
)
insert into tb
select 'pa',6,1,'2013-6-3',5000 union
select 'pa',6,1,'2013-6-4',4000
--要得到表C：
-- product machine model mdate cqty1 cqty2
-- pa 5 1 2013-6-3 1000 0
-- pa 6 1 2013-6-3 1500 5000
-- pa 6 1 2013-6-4 3000 4000
select ta.product,ta.machine,ta.model,ta.mdate,ta.aqty as cqty1,ISNULL(tb.bqty,0) as cqty2
from ta left join tb on ta.product=tb.product and ta.mdate=tb.mdate and ta.machine= tb.machine
--product machine model mdate cqty1 cqty2
------------ ----------- ----------- ----------------------- ----------- -----------
--pa 5 1 2013-06-03 00:00:00 1000 0
--pa 6 1 2013-06-03 00:00:00 1500 5000
--pa 6 1 2013-06-04 00:00:00 3000 4000
--(3 行受影响)
