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

streetclm 2010-10-15 04:36:10

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


...全文
76 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
finkle_zhang 2010-10-18
good job!!!!!!
回复
streetclm 2010-10-16
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)

回复
streetclm 2010-10-16
还有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 2010-10-16
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]
回复
chen8410 2010-10-15
select distinct *
from cte
之所以要加 distinct 是因为这个CTE选择的入口没有唯一性,结果union后有一些会有重复的两条记录
回复
chen8410 2010-10-15
最后两条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
回复
streetclm 2010-10-15
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]
回复
SQL2088 2010-10-15
第2条记录日期怎么不符合了?
回复
streetclm 2010-10-15

;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
)
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-15 04:36
社区公告
暂无公告