联合查询的方法
现有以下两个表：
表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 行受影响)

[解决办法]
select product,machine,model,mdate,sum(cqty1) as cqty1,sum(cqty2) as cqty2 From
(select product,machine,model,mdate,aqty as cqty1,'' as cqty2 From A
union
select product,machine,model,mdate,'' as cqty1,bqty as cqty2 From B ) as tem
group by product,machine,model,mdate