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'

没有#Import表喔.

[雷肿么了]

抱歉, 表名忘记改过来了..

------- 流水号记录表 ---
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'

[唐诗三百首]

------- 流水号记录表 ---
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'

--> 报错: Msg 213, Level 16, State 1, Line 1 Column name or number of supplied values does not match table definition.

[雷肿么了]

------- 流水号记录表 ---
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

[雷肿么了]

我把 表名 #Import 更换成了 #FlowInfo ,有几个地方忘记改过来了.

[唐诗三百首]

不明白结果中的FlowNo是怎么来的? FlowNo --------- 0099-0100 0080-0097 0061-0079 0060-0060 0061-0100 0051-0090

[雷肿么了]

#tmp 表中有个NUM 是指点要出的流水号的个数 #FlowInfo 表中FlowNo (流水号)是坏品(ttype=0) 或者 已出库的(ttype=1) 比如: #tmp表中的第一条数据, SKU:EAY102(black) ,流水号范围是: '0061' 到 '0100' ,货架号是: 'D127',出流水号的个数是39 个 因为在 0061 - 0100中有一个坏品, 表#FlowInfo(流水号信息表) 中: ImportID:13,FLowNo:0098,Ttype:0 已体现 #tOrder 订单表中 订单: Ref001,SKU: EAY102(black), 数量: 20 订单: Ref002,SKU: EAY102(black), 数量: 20 为了记录每个订单出库的流水号(坏品跟已经出库的流水号,要排除),以及货架号. 所以订单显示: Ref001,EAY102(black),0099-0100,2,D127 (因为0098 是坏品所以不记录) Ref001,EAY102(black),0080-0097,18,D127 Ref002,EAY102(black),0061-0079,19,D127 Ref002,EAY102(black),0060-0060,1,D126

[雷肿么了]

e, 没人啊。。

[雷肿么了]

结贴先， 等会再贴个完整嘚