declare @test table(level int,parent varchar(10),child varchar(10), sdate varchar(10), edate varchar(10))
insert @test
select 1,'A001','B001','2005-12-13','2040-12-31' union all
select 1,'A001','B002','2005-12-13','2040-12-31' union all
select 1,'A001','B003','2005-12-13','2040-12-31' union allselect 2,'B001','C001','2005-12-05','2040-12-31' union all
select 2,'B002','C002','2005-12-05','2009-12-31' union all
select 2,'B003','C003','2005-12-05','2007-12-31' union allselect 3,'C001','D001','2005-12-05','2040-12-31' union all
select 3,'C002','D002','2005-12-05','2040-12-31' union all
select 3,'C003','D003','2005-12-05','2040-12-31' ----------------------------------------------
level parent child sdate edate
1 A001 B001 2005-12-13 2040-12-31
1 A001 B002 2005-12-13 2040-12-31
1 A001 B003 2005-12-13 2040-12-31
2 B001 C001 2005-12-05 2040-12-31
2 B002 C002 2005-12-05 2009-12-31
2 B003 C003 2005-12-05 2007-12-31
3 C001 D001 2005-12-05 2040-12-31
3 C002 D002 2005-12-05 2040-12-31
3 C003 D003 2005-12-05 2040-12-31加入一个日期参数@date='2010-10-15',判断是否在sdate和edate之间,是则输出,不是则不输出。
难点在于,要考虑层级level,比如第2级 B002和B003不符合日期条件,那么他们以及他们的儿子C002和C003都不输出
尽管C002和C003符合日期条件,但是他们的老爸B002和B003不符合,所以他们也不输出。
就是说某一级不符合日期条件,则他和他的所有相关下级都不输出
结果如下:
level parent child sdate edate
1 A001 B001 2005-12-13 2040-12-31
1 A001 B002 2005-12-13 2040-12-31
1 A001 B003 2005-12-13 2040-12-31
2 B001 C001 2005-12-05 2040-12-31
3 C001 D001 2005-12-05 2040-12-31
;with cte as
(
select * from @test where sdate>'2010-10-15' or edate<'2010-10-15'
union all
select a.* from @test a,cte b where b.child=a.parent
)
2 B003 C003 2005-12-05 2007-12-312010-10-15不在这两个区间之内
;with cte as
(select * from @test where sdate<'2010-10-15' and edate>'2010-10-15'
union all
select a.* from @test a,cte b where b.child=a.parent and a.sdate<'2010-10-15' and a.edate>'2010-10-15' )select distinct *
from cte--结果:
level parent child sdate edate
1 A001 B001 2005-12-13 2040-12-31
1 A001 B002 2005-12-13 2040-12-31
1 A001 B003 2005-12-13 2040-12-31
2 B001 C001 2005-12-05 2040-12-31
3 C001 D001 2005-12-05 2040-12-31
3 C002 D002 2005-12-05 2040-12-31
3 C003 D003 2005-12-05 2040-12-31
from cte
之所以要加 distinct 是因为这个CTE选择的入口没有唯一性,结果union后有一些会有重复的两条记录
(
select * from @test where sdate>'2010-10-15' or edate<'2010-10-15'
union all
select a.* from @test a, cte b where b.child=a.parent
)select * from @test a
where not exists(select 1 from cte b where a.parent=b.parent and a.child=b.child and a.level=b.level and a.sdate=b.sdate and a.edate=b.edate)