求人回答,再顶上去

declare @test table
(
BOM int identity(1,1),
T_ITEM char(4),
BEGIN_DATE datetime,
END_DATE datetime
)
insert into @test
select '2001','2006-08-29','2006-09-03' union all
select '2002','2006-08-30','2006-09-02' union all
select '2001','2006-09-03','2006-09-10' union all
select '2001','2006-09-12','2006-09-18' union all
select '2003','2006-08-29','2006-09-18' union all
select '2004','2006-09-29','2006-10-03' union all
select '2001','2006-09-18','2006-10-03' union all
select '2001','2006-10-10','2006-11-01'

select * from @test t
where BEGIN_DATE!=(select END_DATE from @test where T_ITEM=t.T_ITEM and BOM=(select max(BOM) from @test where T_ITEM=t.T_ITEM and BOM<t.BOM))
or END_DATE!=(select BEGIN_DATE from @test where T_ITEM=t.T_ITEM and BOM=(select min(BOM) from @test where T_ITEM=t.T_ITEM and BOM>t.BOM))


/*
BOM T_ITEM BEGIN_DATE END_DATE
----------- ------ ------------------------------------------------------ ------------------------------------------------------
3 2001 2006-09-03 00:00:00.000 2006-09-10 00:00:00.000
4 2001 2006-09-12 00:00:00.000 2006-09-18 00:00:00.000
7 2001 2006-09-18 00:00:00.000 2006-10-03 00:00:00.000
8 2001 2006-10-10 00:00:00.000 2006-11-01 00:00:00.000
*/

我用存储过程做了一下,不过真麻烦
还是斑竹的简单
drop table s
create table s
(
T_ITEM int,
BEGIN_DATE datetime,
END_DATE datetime
)
insert s select 2001,'2006-08-29','2006-09-03' union all
select 2002,'2006-08-30','2006-09-02' union all
select 2001,'2006-09-03','2006-09-10' union all
select 2001,'2006-09-12','2006-09-18' union all
select 2003,'2006-08-29','2006-09-18' union all
select 2004,'2006-09-29','2006-10-03' union all
select 2001,'2006-09-18','2006-10-03' union all
select 2001,'2006-10-10','2006-11-01'
drop procedure pro_s
创建存储过程
create procedure pro_s
(@i int)
as
declare @j int
set @j=1
select identity(int,1,1) as bom ,* into #s1 from s where T_ITEM=@i
declare @begin_time datetime
declare @end_time datetime
while @j<=(select count(1) from #s1)-1
begin
select @end_time=end_date from #s1 where bom=@j
select @begin_time=begin_date from #s1 where bom=@j+1
if @begin_time<>@end_time
begin
print ' not filled'
select * from #s1 where T_ITEM=@i
set @j=(select count(1) from #s1)-1
end
set @j=@j+1
end
用临时表来保存T_ITEM
drop table #s
create table #s
(
i int identity(1,1),
T_ITEM int
)
insert into #s select distinct T_ITEM from s
用循环调用存储过程
declare @j int
declare @T_ITEM int
set @j=1
while @j<=(select count(1) from #s )
begin
select @T_ITEM=T_ITEM from #s where @j=i
exec pro_s @T_ITEM
set @j=@j+1
end

[此贴子已经被作者于2006-12-25 20:57:50编辑过]

老大们加点注释呀?看不明白哦!

我老大给你的这段程序你看懂了没啊

问题基本得到解决,谢谢大家!
不过还没有完全弄明白原理,呵呵