22,210
社区成员
发帖
与我相关
我的任务
分享
;WITH Tab1(ID,Nums) AS (
SELECT 'HS001',100
),
Tab2(InDate,ID,Nums) AS(
SELECT '2015-07-24','HS001',40 UNION ALL
SELECT '2015-07-25','HS001',60 UNION ALL
SELECT '2015-07-27','HS001',55 UNION ALL
SELECT '2015-07-29','HS001',100
),
Tab3(OutDate,ID,Nums) AS(
SELECT '2015-07-25','HS001',40 UNION ALL
SELECT '2015-07-26','HS001',60 UNION ALL
SELECT '2015-07-27','HS001',30 UNION ALL
SELECT '2015-07-28','HS001',40
)
SELECT
a.ID,
a.Nums,
bb.Date,
bb.InNums,
bb.OutNum
FROM Tab1 a INNER JOIN
(
SELECT
ISNULL(b.ID,c.ID) AS ID,
ISNULL(b.InDate,c.OutDate) AS [Date],
b.Nums AS InNums,
c.Nums AS OutNum
FROM
Tab2 b
FULL JOIN Tab3 c ON b.ID = c.ID AND b.InDate = c.OutDate
) bb ON bb.ID = a.ID
ORDER BY
bb.Date
select
a.产品编码,
a.数量 as 订单数量,
b.日期,
sum(b.入库数量) as 入库数量,
sum(b.出库数量) as 出库数量
from 订单表 a ,(
select 入库日期 as 日期,产品编码,数量 as 入库数量,0 as 出库数量 from 入库表
union all
select 出库日期 as 日期,产品编码,0 as 入库数量,数量 as 出库数量 from 出库表)b
where a.产品编码=b.产品编码
group by a.产品编码,a.数量,b.日期