-------- 订单表 ----

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 'Ref001','EAY102(blue)',10

INSERT INTO #tOrder SELECT 'Ref002','EAY102(black)',20

INSERT INTO #tOrder SELECT 'Ref003','EAY102(blue)',40

INSERT INTO #tOrder SELECT 'Ref004','EAY102(green)',40





----- SKU 流水号信息 ----

CREATE TABLE #tmp

(

   ImportID INT,           ----关联#FlowInfo 表 ImportID

   SKU VARCHAR(20),        ----SKU 编号

   FlowNo_From VARCHAR(10),----流水号起始

   FLowNo_To VARCHAR(10),  ----流水号截止

   Location VARCHAR(10)    ----货架号 

)



INSERT INTO #tmp SELECT 13,'EAY102(black)','0061','0100','D127'

INSERT INTO #tmp SELECT 13,'EAY102(black)','0031','0060','D126'

INSERT INTO #tmp SELECT 13,'EAY102(black)','0001','0030','D125'

INSERT INTO #tmp SELECT 14,'EAY102(blue)','0001','0100','D124'

INSERT INTO #tmp SELECT 15,'EAY102(green)','0051','0090','D123'

INSERT INTO #tmp SELECT 15,'EAY102(green)','0001','0050','D122'



------- 流水号记录表 ---

CREATE TABLE #FlowInfo

(

   ImportID INT,        --- ID

   FlowNo VARCHAR(10),	--- 流水号

   Ttype INT            --- 0 坏品, 1 已出库

)



INSERT INTO #FlowInfo SELECT 13,'0098',0

INSERT INTO #FlowInfo SELECT 15,'0010',0

INSERT INTO #FlowInfo SELECT 15,'0012',0





SELECT * FROM #tOrder

SELECT * FROM #tmp

SELECT * FROM #FlowInfo







--------------- 最终效果(执行查看), --------------

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