/*
我現在在做一個包裝方面的程序，數據表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_視圖