订单表 OrderOderID(订单编号) OrderCount(订单数量)
A001 200
A002 500
A003 700
出货表 OutOderID(订单编号) OutCount(出货数量)
A001 50
A001 70
A002 300
A002 150希望得到的结果OderID(订单编号) OrderCount(订单数量) SumCount(累计出货) difference(差额)
A001 200 120 80
A002 500 450 50
A003 700 0 700其实问题就在订单A003上,因为没有A003这个订单的出货记录,查询查询结果总是少了AOO3的记录。
请赐教!!!
A001 200
A002 500
A003 700
出货表 OutOderID(订单编号) OutCount(出货数量)
A001 50
A001 70
A002 300
A002 150希望得到的结果OderID(订单编号) OrderCount(订单数量) SumCount(累计出货) difference(差额)
A001 200 120 80
A002 500 450 50
A003 700 0 700其实问题就在订单A003上,因为没有A003这个订单的出货记录,查询查询结果总是少了AOO3的记录。
请赐教!!!
create table #Order
(
OderID varchar(4),
OrderCount int
)create table #Out
(
OderID varchar(4),
OutCount int
)insert into #Order
select 'A001',200 union all
select 'A002',500 union all
select 'A003',700insert into #Out
select 'A001',50 union all
select 'A001',70 union all
select 'A002',300 union all
select 'A002',150SELECT t2.OderID,
t2.OrderCount,
t2.SumCount,
DIFFERENCE = t2.OrderCount -t2.SumCount
FROM (
SELECT *,
SumCount = (
SELECT ISNULL(SUM(OutCount), 0)
FROM #Out
WHERE t1.OderID = OderID
)
FROM #Order t1
) t2
OderID OrderCount SumCount DIFFERENCE
------ ----------- ----------- -----------
A001 200 120 80
A002 500 450 50
A003 700 0 700
from Order a left join Out b on a.OderID=b.OderID
group by a.OderID,a.OrderCount
drop table [Order]
go
if object_id('Out') is not null
drop table [Out]
go
create table [Order]
(
OderID varchar(4),
OrderCount int
)create table [Out]
(
OderID varchar(4),
OutCount int
)insert into [Order]
select 'A001',200 union all
select 'A002',500 union all
select 'A003',700insert into [Out]
select 'A001',50 union all
select 'A001',70 union all
select 'A002',300 union all
select 'A002',150select a.OderID,a.OrderCount,isnull(sum(b.OutCount),0) as SumCount,isnull(a.OrderCount-sum(b.OutCount),a.OrderCount) as 差额
from [Order] a left join [Out] b on a.OderID=b.OderID
group by a.OderID,a.OrderCount
from [order] a left join
(select Oderid,sum(OutCount) OutCount from out group by Oderid) b
on a.oderid=b.orderid
(
OderID varchar(4),
OrderCount int
)create table #Out
(
OderID varchar(4),
OutCount int
)insert into #Order
select 'A001',200 union all
select 'A002',500 union all
select 'A003',700insert into #Out
select 'A001',50 union all
select 'A001',70 union all
select 'A002',300 union all
select 'A002',150SELECT t2.OderID,
t2.OrderCount,
t2.SumCount,
DIFFERENCE = t2.OrderCount -t2.SumCount
FROM (
SELECT *,
SumCount = (
SELECT ISNULL(SUM(OutCount), 0)
FROM #Out
WHERE t1.OderID = OderID
)
FROM #Order t1
) t2
OderID OrderCount SumCount DIFFERENCE
------ ----------- ----------- -----------
A001 200 120 80
A002 500 450 50
A003 700 0 700