OK,现在只剩下日期问题了

[streetclm]

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 all

select 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 all

select 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

[streetclm]

;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
)

[SQL2088]

第2条记录日期怎么不符合了？

[streetclm]

2 B002 C002 2005-12-05 2009-12-31
2 B003 C003 2005-12-05 2007-12-31

2010-10-15不在这两个区间之内
[Quote=引用 2 楼 sql2088 的回复:]

第2条记录日期怎么不符合了？
[/Quote]

[chen8410]

最后两条C002、C003也是要出来的吧，结果如下：

;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

[chen8410]

select distinct *
from cte
之所以要加 distinct 是因为这个CTE选择的入口没有唯一性，结果union后有一些会有重复的两条记录

[streetclm]

C002和C003也不能出来，因为他们的上级B002不符合日期了
[Quote=引用 4 楼 chen8410 的回复:]

最后两条C002、C003也是要出来的吧，结果如下：
SQL code
;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<……
[/Quote]

[streetclm]

还有B003
[Quote=引用 6 楼 streetclm 的回复:]

C002和C003也不能出来，因为他们的上级B002，B003不符合日期了
引用 4 楼 chen8410 的回复:

最后两条C002、C003也是要出来的吧，结果如下：
SQL code
;with cte as
(select * from @test where sdate<'2010-10-15' and edate>'2010-10-15'
union all
select a……
[/Quote]

[streetclm]

Ok，搞定

;WITH cte(level,parent,child,sdate,edate) 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
)

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)

[finkle_zhang]

good job！！！！！！