业务背景:ERP系统中,包含货品表(每个货品一个ID),货品尺码定义表(每个货品均有自己的尺码范围),库存表(每个货品每个尺码一行记录),如果某个货品,连续 3个尺码都有库存,则不算做断码,现在要根据货品尺码定义和实际库存计算出那些货品没有断码。
--构造数据
if OBJECT_ID('tempdb..#Material') IS NOT NULL drop table #Material
--货品表
create table #Material(MaterialID int,MaterialName nvarchar(20) primary key (MaterialID))
insert into #Material
select 1,N'安踏男鞋'
union all
select 2,N'鸿星尔克球鞋'
union all
select 3,N'NK球鞋'
--货品尺码表
if OBJECT_ID('tempdb..#MaterialSize') IS NOT NULL drop table #MaterialSize
create table #MaterialSize(MaterialID int,Size varchar(20) primary key(MaterialID,Size))
insert into #MaterialSize(MaterialID,Size)
select 1,'33'
union all
select 1,'34'
union all
select 1,'35'
union all
select 2,'36'