求一个sql

Question

表a
orderid, fulfillDate, fulfillQty
1        1/1/2010     100
2        1/1/2011     200
2        1/1/2011     300
3        1/1/2010     400
表b
orderid, shipmentDate, shipmentQty
2        1/1/2011      200
3        1/1/2012      300
4        1/1/2011      400
求写一个sql, 说不清需求，我举个例子吧，给出两个参数startdate=1/1/2011, enddate=1/1/2011
表a中orderid 2在这一天有2条记录, qty总和为200+300=500
表b中orderid 2在这一天有1条记录，qty总和为200, 另外orderid 4有1条记录qty为400
那最后的结果表想要变成这样：
orderid, fulfillQtyTotal, shipmentQtyTotal
2       500               200
4       0                 400

Answer 1

create table #ta(orderid int, fulfillDate datetime, fulfillQty int)
insert into #ta
select 1,'1/1/2010',100
union all select 2,'1/1/2011',200
union all select 2,'1/1/2011',300
union all select 3,'1/1/2010',400create table #tb(orderid int, shipmentDate datetime, shipmentQty int)
insert into #tb
select 2,'1/1/2011',200
union all select 3,'1/1/2012',300
union all select 4,'1/1/2011',400
godeclare @startdate datetime,@enddate datetime
select @startdate='1/1/2011',@enddate='1/1/2011'select isnull(a.orderid,b.orderid) as orderid,a.fulfillQtyTotal,b.shipmentQtyTotal
from (select orderid,sum(fulfillQty) as fulfillQtyTotal from #ta where fulfillDate between @startdate and @enddate group by orderid)a
right join (select orderid,sum(shipmentQty) as shipmentQtyTotal from #tb where shipmentDate between @startdate and @enddate group by orderid)b
on a.orderid=b.orderid
drop table #ta,#tb/*
orderid  fulfillQtyTotal  shipmentQtyTotal
2 500 200
4 NULL 400
*/

Answer 2

[code=sql][code=sql]if object_id('pro_test') is not null
drop proc pro_test
go
create proc pro_test
(
@startdate varchar(10),
@enddate varchar(10)
)select
   isnull(a.orderid,b.orderid) as orderid,
   a.fulfillQtyTotal
   b.shipmentQtyTotal
from
   (select orderid,fulfillDate,isnull(sum(fulfillQty),0) as fulfillQtyTotal group by orderid,fulfillDate)a
full join
    (select orderid,shipmentDate,isnull(sum(shipmentQty),0) as shipmentQtyTotal group by orderid,fulfillDate)b
on
    a.orderid=b.orderid and a.fulfillDate=b.shipmentDate
where 
     isnull(a.fulfillDate,b.shipmentDate)>=@startdate and isnull(a.fulfillDate,b.shipmentDate)<=@enddate[/code][/code]

Answer 3

create table 表a
(orderid int,fulfillDate varchar(15),fulfillQty int)insert into 表a
 select 1,'1/1/2010',100 union all
 select 2,'1/1/2011',200 union all
 select 2,'1/1/2011',300 union all
 select 3,'1/1/2010',400create table 表b
(orderid int,shipmentDate varchar(15),shipmentQty int)insert into 表b
 select 2,'1/1/2011',200 union all
 select 3,'1/1/2012',300 union all
 select 4,'1/1/2011',400
declare @startdate varchar(15),@enddate varchar(15)
select @startdate='1/1/2011',@enddate='1/1/2011'select isnull(a.orderid,b.orderid) 'orderid',
       isnull(a.fulfillQtyTotal,0) 'fulfillQtyTotal',
       isnull(b.shipmentQtyTotal,0) 'shipmentQtyTotal'
from
(select orderid,sum(fulfillQty) 'fulfillQtyTotal'
 from 表a 
 where fulfillDate between @startdate and @enddate
 group by orderid) a
full join
(select orderid,sum(shipmentQty) 'shipmentQtyTotal'
 from 表b 
 where shipmentDate between @startdate and @enddate
 group by orderid) b on a.orderid=b.orderid/*
orderid     fulfillQtyTotal shipmentQtyTotal
----------- --------------- ----------------
2           500             200
4           0               400(2 row(s) affected)
*/

Answer 4

create table #ta(orderid int, fulfillDate datetime, fulfillQty int)
insert into #ta
select 1,'1/1/2010',100
union all select 2,'1/1/2011',200
union all select 2,'1/1/2011',300
union all select 3,'1/1/2010',400create table #tb(orderid int, shipmentDate datetime, shipmentQty int)
insert into #tb
select 2,'1/1/2011',200
union all select 3,'1/1/2012',300
union all select 4,'1/1/2011',400
godeclare @startdate datetime,@enddate datetime
select @startdate='1/1/2011',@enddate='1/1/2011'select a.orderid,isnull(SUM(fulfillQty),0) as fulfillQtyTotal,max(shipmentQty) as  shipmentQtyTotal  from #tb a
left join #ta b  
on a.orderid=b.orderid  where a.shipmentDate>=@startdate 
and a.shipmentDate<=@enddate group by a.orderid    

Answer 5

create table cstb_a(orderid int, fulfillDate datetime,fulfillQty int)
insert into cstb_a
select 1,'1/1/2010',100 union all
select 2,'1/1/2011',200 union all
select 2,'1/1/2011',300 union all
select 3,'1/1/2010',400 union all
select 5,'1/1/2011',500
gocreate table cstb_b(orderid int, shipmentDate datetime,shipmentQty int)
insert into cstb_b
select 2,'1/1/2011',200 union all
select 3,'1/1/2012',300 union all
select 4,'1/1/2011',400
gocreate proc TotalQty
@startdate varchar(10), 
@enddate varchar(10)
as
begin
;with cte_a as
(
select orderid,sum(fulfillQty) fulfillQtyTotal
from cstb_a
where fulfillDate between @startdate and @enddate
group by orderid,fulfillDate
),
cte_b as
(
select orderid,sum(shipmentQty) shipmentQtyTotal
from cstb_b
where shipmentDate between @startdate and @enddate
group by orderid,shipmentDate
)
select 
isnull(a.orderid,b.orderid) orderid,
isnull(a.fulfillQtyTotal,0) fulfillQtyTotal,
isnull(b.shipmentQtyTotal,0) shipmentQtyTotal
from cte_a a full join cte_b b on a.orderid = b.orderid
end/*
exec TotalQty '1/1/2011','1/1/2011'orderid     fulfillQtyTotal shipmentQtyTotal
----------- --------------- ----------------
2           500             200
4           0               400
5           800             0(3 行受影响)
*/

Answer 6

之所以要自己加一条，是因为这里 要用 full join 才能不遗漏