CTE查询有2个条件不起作用？
我出生于 1978 年 12 月 22 日，
我想要一个查询（在 CTE 中！）来显示我所有的生日（datepart day = 22）
declare @t0 datetime = '1978/12/22';
declare @t1 datetime = getdate();
with CTEE (val,day1)
AS
(
SELECT @t0,DATEPART(day,@t0)
UNION all
SELECT DATEADD(day,1,val) , DATEPART(day,day1) from ctee where( DATEADD(day,1,val) <=@t1) and DATEPART(day,day1)=22
)
select val,day1 from CTEE OPTION (MAXRECURSION 20000)
这似乎返回了我 1 行（为什么？？ ？）
如果我删除 和 DATEPART(day,day1)=22 这样它就会给我我活着的所有日子。
我找到的解决方案是这样的：
declare @t0 datetime = '1978/12/22';
declare @t1 datetime = getdate();
with CTEE (val,day1)
AS
(
SELECT @t0,DATEPART(day,@t0)
UNION all
SELECT DATEADD(day,1,val) , DATEPART(day,day1) from ctee where( DATEADD(day,1,val) <=@t1)
)
select val,day1 from CTEE where day1=22 OPTION (MAXRECURSION 20000)
但我的问题是为什么在第一个查询中它不起作用？
I was born in 1978/12/22
I wanted a query (in CTE!) to display me all my birthdays (datepart day = 22)
declare @t0 datetime = '1978/12/22';
declare @t1 datetime = getdate();
with CTEE (val,day1)
AS
(
SELECT @t0,DATEPART(day,@t0)
UNION all
SELECT DATEADD(day,1,val) , DATEPART(day,day1) from ctee where( DATEADD(day,1,val) <=@t1) and DATEPART(day,day1)=22
)
select val,day1 from CTEE OPTION (MAXRECURSION 20000)
How ever this seems to return me 1 row ( why???)
If I remove and DATEPART(day,day1)=22 so it gives me all the days im alive.
The solution I found is this:
declare @t0 datetime = '1978/12/22';
declare @t1 datetime = getdate();
with CTEE (val,day1)
AS
(
SELECT @t0,DATEPART(day,@t0)
UNION all
SELECT DATEADD(day,1,val) , DATEPART(day,day1) from ctee where( DATEADD(day,1,val) <=@t1)
)
select val,day1 from CTEE where day1=22 OPTION (MAXRECURSION 20000)
But my question is why in the first query it didn't work?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
因为第二条记录是
1978-12-23。这不满足
DATEPART(day,day1)=22条件，因此递归步骤不会返回结果，这会终止递归，如 SQL Server 联机丛书Because the second record is
1978-12-23.That doesn't meet the
DATEPART(day,day1)=22condition, so that recursive step returns no results, this terminates the recursion as documented in SQL Server Books Online