来了，来了， SQL 难题， 求指点 。

Question

-------- 订单表 ----
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
SELECT 'Ref001','EAY102(blue)','0091-0100',10,'D124'
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)','0051-0090',40,'D124'
UNION ALL
SELECT 'Ref004','EAY102(green)','0051-0090',40,'D123'------ 下面说下，这结果是怎么来的 
------ 按每个订单，记录所出的SKU所对应的流水号以及货架号（Location） ，流水号从大往小获取
------ 如果流水号范围内有坏品 （#tmp 表ImportID 关联 #FlowInfo 表，Ttype=0），则不记录坏评的流水号
------ 比如 #FlowInfo 表中ImportID=13 有个 流水号：0098 是坏品，所以最后结果 0099-0100 ， 0080-0097 显示
-------RefNum SKU FlowNo Num Location
-------Ref001 EAY102(black) 0099-0100 2 D127
-------Ref001 EAY102(black) 0080-0097 18 D127drop table #tmp
drop table #FlowInfo
drop table #tOrder

Answer 1

#FlowInfo表Ttype的值都是0,0代表是坏品,那结果是个空集,是不是你给值错了?

Answer 2

#FlowInfo表Ttype的值都是0,0代表是坏品，  1就是代表已经出库。哪个结果是个空集 ？

Answer 3

-------- 订单表 ----
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------ 下面说下，这结果是怎么来的 
------ 按每个订单，记录所出的SKU所对应的流水号以及货架号（Location） ，流水号从大往小获取
------ 如果流水号范围内有坏品 （#tmp 表ImportID 关联 #FlowInfo 表，Ttype=0），则不记录坏评的流水号
------ 比如 #FlowInfo 表中ImportID=13 有个 流水号：0098 是坏品，所以最后结果 0099-0100 ， 0080-0097 显示
-------RefNum SKU FlowNo Num Location
-------Ref001 EAY102(black) 0099-0100 2 D127
-------Ref001 EAY102(black) 0080-0097 18 D127if(OBJECT_ID('tempdb..#goodSKU') is not null)
drop table tempdb..#goodSKU;select number,t.*
into #goodSKU
from master.dbo.spt_values sp
join #tmp t
on sp.number between t.FlowNo_From and t.FLowNo_To and [type]='p'
left join #FlowInfo f
on t.ImportID=f.ImportID and sp.number=f.FlowNo and f.Ttype=0
where f.FlowNo is nullif(OBJECT_ID('tempdb..#result') is not null)
drop table tempdb..#result;
create table #result
(RefNum varchar(50),SKU varchar(50),FlowNo varchar(50),Num int,Location varchar(50))declare skuCursor Cursor for
select RefNum,SKU,NUM from #tOrderopen skuCursor
declare @RefNum varchar(50);
declare @SKU varchar(50);
declare @NUM int;fetch next from skuCursor into @RefNum,@SKU,@NUM;
while @@FETCH_STATUS=0
begininsert into #result
select @RefNum,@SKU,'00'+convert(varchar(20),MIN(number))+'-'+'00'+convert(varchar(20),MAX(number))FLowNo
,MAX(number)-MIN(number)+1 as num,Location
from
(
select number,SKU,Location,FlowNo_From,FlowNo_To,ROW_NUMBER() over(order by number desc) rn
,ROW_NUMBER() over(order by number ) rn2
from #goodSKU
where SKU=@SKU 
)t
where rn<=@NUM
group by Location,number-rn2;delete  
from #goodSKU 
where SKU=@SKU 
and number in
(
select number
from
(
select number,SKU,Location,ROW_NUMBER() over(order by number desc) rn
from #goodSKU
where SKU=@SKU 
)t
where rn<=@NUM
)
fetch next from skuCursor into @RefNum,@SKU,@NUM
endclose skuCursor;
deallocate skuCursor;
select *
from #Result
--drop table #tmp
--drop table #FlowInfo
--drop table #tOrder
--drop table #Result

Answer 4

不知道不使用个游标，是否可以实现。另外我想把#tmp 表数据，弄成最后查询的结果那样，不知可否实现
想把 OrderNum 的数量分配到对应的流水号 区域内（FlowNo_From，FLowNo_To），其中Num 是指这个区域内可用的流水号数量。 CREATE TABLE #tmp
(
   ImportID INT,           ----#Import 表ID 
   SKU VARCHAR(20),        ----SKU 编号
   FlowNo_From VARCHAR(10),----流水号起始
   FLowNo_To VARCHAR(10),  ----流水号截止
   Location VARCHAR(10),   ----货架号
   Num INT,                ----有效流水号数量 
   RefNum varchar(10),     ----订单号
   OrderNum int            ----订单扣数、
   
)
 
INSERT INTO #tmp SELECT 13,'EAY102(black)','0061','0100','D127',39,'Ref001',40
INSERT INTO #tmp SELECT 13,'EAY102(black)','0061','0100','D127',39,'Ref002',20
INSERT INTO #tmp SELECT 13,'EAY102(black)','0031','0060','D126',30,'Ref001',0
INSERT INTO #tmp SELECT 13,'EAY102(black)','0031','0060','D126',30,'Ref002',0
INSERT INTO #tmp SELECT 14,'EAY102(blue)','0001','0100','D124',40,'Ref003',40
INSERT INTO #tmp SELECT 15,'EAY102(green)','0051','0090','D123',40,'Ref004',40
select * From #tmpSELECT 13 as ImportID,'EAY102(black)' AS SKU,'0061' AS FlowNo_From,'0100' AS FLowNo_To,'D127' AS Location ,39 AS Num,'Ref001' AS RefNum,40 AS OrderNum, 39 AS Result
union all
SELECT 13,'EAY102(black)','0061','0100','D127',39,'Ref002',20,0
union all
SELECT 13,'EAY102(black)','0031','0060','D126',30,'Ref001',0,1
union all
SELECT 13,'EAY102(black)','0031','0060','D126',30,'Ref002',0,20
union all
SELECT 14,'EAY102(blue)','0001','0100','D124',40,'Ref003',40,40
union all
SELECT 15,'EAY102(green)','0051','0090','D123',40,'Ref004',40,40

Answer 5

;with t as(
select  a.importid,a.sku,a.location,b.number,row_number()over(partition by sku order by location desc,b.number desc) as ord from #tmp a inner join master..spt_values b
on convert(int,a.FlowNo_From)<=b.number
and convert(int,a.FlowNo_to)>=b.number 
where b.type='P'
and b.number>0
and not exists(select 1 from #FlowInfo c
where convert(int,c.FlowNo)=b.number
and a.importid=c.importid
and c.Ttype=0)),
t1 as
(select a.refnum,sku,a.num,row_number()over(partition by sku order by sku,refnum asc) as ord from #tOrder a inner join master..spt_values b
on a.num>=b.number
where b.type='P'
and b.number>0)
select t1.refnum,t1.sku,t.location,min(t.number),max(t.number),count(1) from t right join t1
on t.sku=t1.sku
and t1.ord=t.ord
group by t1.refnum,t1.sku,t.location,refnum+convert(nvarchar,t.number+t.ord)
order by t1.refnum,t1.sku,t.location