来了，来了， SQL 难题， 求指点 。
-------- 订单表 ----
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 显示
-------RefNumSKUFlowNoNumLocation
-------Ref001EAY102(black)0099-01002D127
-------Ref001EAY102(black)0080-009718D127
drop table #tmp
drop table #FlowInfo
drop table #tOrder
-------- 订单表 ----
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 显示
-------RefNumSKUFlowNoNumLocation
-------Ref001EAY102(black)0099-01002D127
-------Ref001EAY102(black)0080-009718D127
if(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 null
if(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 #tOrder
open skuCursor
declare @RefNum varchar(50);
declare @SKU varchar(50);
declare @NUM int;
fetch next from skuCursor into @RefNum,@SKU,@NUM;
while @@FETCH_STATUS=0
begin
insert 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
end
close skuCursor;
deallocate skuCursor;
select *
from #Result
--drop table #tmp
--drop table #FlowInfo
--drop table #tOrder
--drop table #Result
;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
