SQL 疑难问题, 求教, 在线等
想知道每个订单所出的SKU流水号以及货架信息.
根据ImportID, 按流水号从大到小计算...
表语句如下:
----- 出库SKU 详情 ----
CREATE TABLE #tmp
(
ImportID INT, ----#Import 表ID
SKU VARCHAR(20), ----SKU 编号
FlowNo_From VARCHAR(10),----流水号起始
FLowNo_To VARCHAR(10), ----流水号截止
Location VARCHAR(10), ----货架号
Num INT ----出货数量
)
INSERT INTO #tmp SELECT 13,'EAY102(black)','0061','0100','D127',39
INSERT INTO #tmp SELECT 13,'EAY102(black)','0031','0060','D126',1
INSERT INTO #tmp SELECT 14,'EAY102(blue)','0001','0100','D124',40
INSERT INTO #tmp SELECT 15,'EAY102(green)','0051','0090','D123',40
------- 流水号记录表 ---
CREATE TABLE #FlowInfo
(
ImportID INT, --- ID
FlowNo VARCHAR(10),--- 流水号
Ttype INT --- 0 坏品, 1 出库使用
)
INSERT INTO #Import SELECT 13,'0098'
INSERT INTO #Import SELECT 15,'0010'
INSERT INTO #Import SELECT 15,'0012'
-------- 订单表 ----
CREATE TABLE #tOrder
(
RefNum VARCHAR(20), --订单编号
SKU VARCHAR(20), --SKU 编号
NUM INT --SKU 数量
)
INSERT INTO #tOrder SELECT 'Ref001','EAY102(black)',20
INSERT INTO #tOrder SELECT 'Ref002','EAY102(black)',20
INSERT INTO #tOrder SELECT 'Ref003','EAY102(blue)',40
INSERT INTO #tOrder SELECT 'Ref004','EAY102(green)',40
SELECT * FROM #Import
SELECT * FROM #tmp
SELECT * FROM #tOrder
--------------- 最终效果(执行查看),最后将使用了的FlowNo(流水号,插入#FlowInfo 表,Ttype=1) --------------
SELECT 'Ref001' AS RefNum,'EAY102(black)' AS SKU,'0099-0100' AS FlowNo,2 AS Num,'D127' AS Location
UNION ALL
SELECT 'Ref001','EAY102(black)','0080-0097',18,'D127'
UNION ALL
SELECT 'Ref002','EAY102(black)','0061-0079',19,'D127'
UNION ALL
SELECT 'Ref002','EAY102(black)','0060-0060',1,'D126'
UNION ALL
SELECT 'Ref003','EAY102(blue)','0061-0100',40,'D124'
UNION ALL
SELECT 'Ref004','EAY102(green)','0051-0090',40,'D123'
INSERT INTO #Import SELECT 13,'0098'
INSERT INTO #Import SELECT 15,'0010'
INSERT INTO #Import SELECT 15,'0012'
------- 流水号记录表 ---
CREATE TABLE #FlowInfo
(
ImportID INT, --- ID
FlowNo VARCHAR(10), --- 流水号
Ttype INT --- 0 坏品, 1 出库使用
)
INSERT INTO #FlowInfo SELECT 13,'0098'
INSERT INTO #FlowInfo SELECT 15,'0010'
INSERT INTO #FlowInfo SELECT 15,'0012'
