公共表表达式“cte”的递归成员具有多个递归引用
公共表表达式“cte”的递归成员具有多个递归引用
提问于 2022-03-10 02:21:57
我有这些数据
CODE DATEAPPLY PARENT_CODE REMARK
A 2022/3/10 A１
A 2020/3/10 X A2
B 2022/3/10 A B1
B 2020/3/10 B2
C 2022/3/10 A C1
C 2020/3/10 A C2
D 2022/3/10 C D1
D 2020/3/10 B D2
E 2022/3/10 D E1
E 2020/3/10 C E2我想要做的是得到所有的代码，并且它是子的。例如，如果我使用条件= 'C‘进行搜索，则查询必须递归返回C和C的子查询。
但问题是，有一个DATEAPPLY将决定哪些数据在搜索时是有效的。
例如，对于CODE=C和current date = 20211231，结果应该是
CODE DATEAPPLY PARENT_CODE REMARK
C 2020/3/10 A C2 -- this record is get because it's DateApply is closest to the current date
E 2020/3/10 C E2 -- this record is get because it's DateApply is closet to above record's dateapply and it's parent_code=`C`但是对于CODE=C和current date = 20221231，结果应该是
CODE DATEAPPLY PARENT_CODE REMARK
C 2022/3/10 A C1
D 2022/3/10 C D1使用CTE，我能够递归地获得代码，但是由于涉及到ApplyDate，我不能排除不必要的代码。
declare @code varchar(8), @currentdate varchar(8);
set @code = 'C';
set @currentdate = '20221231';
Create table #CODEMASTER(CODE varchar(8), APPLYDATE varchar(8), PARENT_CODE varchar(8), DEL_FLG bit);
insert into #CODEMASTER values('A', '20220310', '', 0);
insert into #CODEMASTER values('A', '20200310', 'X', 0);
insert into #CODEMASTER values('B', '20220310', 'A', 0);
insert into #CODEMASTER values('B', '20200310', '', 0);
insert into #CODEMASTER values('C', '20220310', 'A', 0);
insert into #CODEMASTER values('C', '20200310', 'A', 0);
insert into #CODEMASTER values('D', '20220310', 'C', 0);
insert into #CODEMASTER values('D', '20200310', 'B', 0);
insert into #CODEMASTER values('E', '20220310', 'D', 0);
insert into #CODEMASTER values('E', '20200310', 'C', 0);
;with cte as
(
select *, cast(0 as bigint) as seqnum from #CODEMASTER where CODE=@CODE and APPLYDATE = (select max(APPLYDATE) from #CODEMASTER where APPLYDATE < @currentdate and CODE=@CODE)
union all
Select * from (
select t.*, row_number() over (partition by t.CODE, t.APPLYDATE order by t.APPLYDATE desc) as seqnum
from #CODEMASTER t
inner join cte on cte.CODE = t.PARENT_CODE AND t.APPLYDATE <= cte.APPLYDATE
and cte.CODE <> t.CODE
-- where t.CODE not in (select CODE from cte)
) as q
where q.seqnum = 1
)
select * from cte;注释部分是我需要包括的地方，以排除cte结果中已经存在的代码。
-- where t.CODE not in (select CODE from cte)我得到了一个错误：
公共表表达式“cte”的
递归成员具有多个递归引用。
在不使用循环的情况下，是否有任何工作可以使用纯查询来实现？谢谢。
回答 1
Stack Overflow用户
发布于 2022-03-10 10:31:42
嗨，EagerToLearn，试试这个
declare @code varchar(8), @currentdate varchar(8);
set @code = 'C';
set @currentdate = '20221231';
Create table #CODEMASTER(CODE varchar(8), APPLYDATE varchar(8), PARENT_CODE varchar(8), DEL_FLG bit);
insert into #CODEMASTER values('A', '20220310', '', 0);
insert into #CODEMASTER values('A', '20200310', 'X', 0);
insert into #CODEMASTER values('B', '20220310', 'A', 0);
insert into #CODEMASTER values('B', '20200310', '', 0);
insert into #CODEMASTER values('C', '20220310', 'A', 0);
insert into #CODEMASTER values('C', '20200310', 'A', 0);
insert into #CODEMASTER values('D', '20220310', 'C', 0);
insert into #CODEMASTER values('D', '20200310', 'B', 0);
insert into #CODEMASTER values('E', '20220310', 'D', 0);
insert into #CODEMASTER values('E', '20200310', 'C', 0);
;with
RecentCodemaster as (
select #codemaster.*
from
#codemaster
inner join
(select
CODE
,min(abs(datediff(dd,datefromparts(left( applydate,4),substring(applydate,5,2),right(applydate,2))
,datefromparts(left( @currentdate,4),substring(@currentdate,5,2),right(@currentdate,2))))) MinDayApart
from
#codemaster
group by CODE) as CloseApply
on #codemaster.code = closeapply.code
and abs(datediff(dd,datefromparts(left( applydate,4),substring(applydate,5,2),right(applydate,2))
,datefromparts(left( @currentdate,4),substring(@currentdate,5,2),right(@currentdate,2)))) = CloseApply.MinDayApart)
,cte as
(
select
Code
,Applydate
,parent_code
from RecentCodemaster where CODE=@CODE
union all
Select
#codemaster.Code
,#codemaster.Applydate
,#codemaster.parent_code
from
#codemaster
inner join
cte
on cte.Code = #codemaster.parent_code
and cte.applydate = #codemaster.applydate
)
select * from cte; 这里的诀窍是设置RecentCodemaster CTE，它查找接近当前日期的所有recs，然后使用它进行递归，因为由@代码过滤的父数据集。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71418128
复制相关文章
相似问题
腾讯云开发者
