81.箱编号连续处理

梦里梦外; 2024-02-19 18:03 38阅读 0赞

/*
    我现在在做一个包装方面的程序，数据表table是这样设计的:
    产品号，产品名，箱号，箱如仓时，就记录箱的编号, 
    现在要求做如下报表：如果A产品已经装了1,2,3,4,5，6，7，8，9号箱，则显示:
    
    产品号	产品名	已装箱数 	箱编排
    ==================================================
    A	AAA	9		1~9
    
    假如第7号箱没入仓，则显示：
    
    产品号	产品名	已装箱数 	箱编排
    ==================================================
    A	AAA	6		1~6
    A	AAA	2		8~9
    
    我想用一个视图(sql 2000)来实现，请教要如果实现
    
    
    */
    --创建数据测试环境
    create table 数据表(产品号 varchar(1),产品名 varchar(10),箱号 int,箱如仓时 datetime,就记录箱的编号 int)
    insert into 数据表
    	select 'A','AAA',1,null,null
    	union all select 'A','AAA',2,null,null
    	union all select 'A','AAA',3,null,null
    	union all select 'A','AAA',4,null,null
    	union all select 'B','AAA',5,null,null
    	union all select 'B','AAA',6,null,null
    	union all select 'A','AAA',7,null,null
    	union all select 'A','AAA',8,null,null
    	union all select 'B','AAA',9,null,null
    	union all select 'C','AAA',10,null,null
    go
    
    --创建自定义函数,得到箱号连续表
    create function f_getseriesbh()
    returns @re table(产品号 varchar(1),开始箱号 int,结束箱号 int)
    as
    begin
    	declare @tb1 table(id int identity(1,1),产品号 varchar(1),箱号 int)
    	declare @tb2 table(id int identity(1,1),产品号 varchar(1),箱号 int)
    
    	insert into @tb1(产品号,箱号) select 产品号,箱号
    		from 数据表 a
    		where not exists(select 1 from 数据表 where 产品号=a.产品号 and 箱号=a.箱号-1)
    		order by 产品号,箱号
    
    	insert into @tb2(产品号,箱号) select 产品号,箱号
    		from 数据表 a
    		where not exists(select 1 from 数据表 where 产品号=a.产品号 and 箱号=a.箱号+1)
    		order by 产品号,箱号
    
    	insert into @re
    	select a.产品号,a.箱号,b.箱号 from @tb1 a inner join @tb2 b on a.id=b.id
    	return
    end
    go
    
    create view v_视图
    as
    select a.产品号,a.产品名
    	,已装箱数=b.结束箱号-b.开始箱号+1
    	,箱编排=cast(b.开始箱号 as varchar)+case b.结束箱号 when b.开始箱号 then '' else '~'+cast(b.结束箱号 as varchar) end
    from (select distinct 产品号,产品名 from 数据表) a 
    	inner join dbo.f_getseriesbh() b on a.产品号=b.产品号
    
    go
    
    select * from v_视图
    go
    
    drop table 数据表--,#tb
    drop function f_getseriesbh
    drop view v_视图