主页 / dba / 问题 / 215426
Accepted
jdids
Asked: 2018-08-21 13:12:55 +0800 CST

简单的递归 CTE 陷入无限循环

  • 772

我需要递归查询来查找菊花链相关数据。我有以下查询

CREATE TABLE #source
(Parent varchar(10), Child varchar(10), Date datetime)


INSERT INTO #source
(Parent, Child, Date)
VALUES
('05529744', '05395782', '9999-12-31'),
('05395782', '05529744', '2017-12-31'),
('05147762', '05395782', '2016-12-31'),
('04564728', '05147762', '2016-09-30'),
('04299824', '04564728', '2013-07-31'),
('05224920', '04999841', '2017-03-31'),
('04999841', '05224920', '2016-10-31'),
('04824720', '04999841', '2015-12-31'),
('02917870', '04824720', '2015-06-30')

;with 
descendants as
  ( select parent, child as descendant, Date, 1 as level
    from #source
  union all
   select d.parent, s.child, d.Date, d.level + 1
     from descendants as d
  join #source s
    on d.descendant = s.parent
) 
select *
from descendants 

--order by parent, level, descendant
option (maxrecursion 0) ;

DROP TABLE #source

正如您从测试数据中看到的那样，父字段和子字段以菊花链形式来回连接。不幸的是，当我运行上述查询时，它陷入了无限循环，而我正在为为什么而摸不着头脑。

最终目标是让数据看起来像这样：

Parent      Child       Date          Rank
05529744    05529744    9999-12-31    1
05529744    05395782    2017-12-31    2
05529744    05147762    2016-12-31    3
05529744    04564728    2016-09-30    4
05529744    04299824    2013-07-31    5
05224920    05224920    2017-03-31    1
05224920    04999841    2016-10-31    2
05224920    04824720    2015-12-31    3
05224920    02917870    2015-06-30    4
sql-server

1 个回答

  1. Best Answer

    我想你错过了锚在哪里......

    select parent, child as descendant, Date, 1 as level
    from #source where <<SOMETHING TO SELECT JUST FEW RECORDS>>

    否则，每次您都会选择所有记录。

    此外，检查前 2 条记录...

    ('05529744', '05395782', '9999-12-31'),
('05395782', '05529744', '2017-12-31'),

    它们是周期性的......这永远不会结束......
    修复数据，再试一次，让我知道

    其他会引发错误的是日期转换...您的日期字段应如下所示：

    convert(date,'9999-12-31',120)

    修复一些数据。以下 CTE 将起作用：

    CREATE TABLE #source
(Parent varchar(10), Child varchar(10), Date datetime)

INSERT INTO #source
(Parent, Child, Date)
VALUES
('05529744', '05529744', convert(date,'9999-12-31',120)),
('05395782', '05395782', convert(date,'2017-12-31',120)),
('05395782', '05147762', convert(date,'2016-12-31',120)),
('05147762', '04564728', convert(date,'2016-09-30',120)),
('04564728', '04299824', convert(date,'2013-07-31',120)),
('05224920', '05224920', convert(date,'2017-03-31',120)),
('04999841', '04999841', convert(date,'2016-10-31',120)),
('04999841', '04824720', convert(date,'2015-12-31',120)),
('04824720', '02917870', convert(date,'2015-06-30',120))

select * from #source order by 1

;with 
descendants as
  ( select parent, child as descendant, Date, 1 as level
    from #source where Parent = Child
  union all
   select d.parent, s.child, d.Date, d.level + 1
     from descendants as d
  join #source s
    on d.descendant = s.Parent
    where s.Child <> s.Parent
) 
select *
from descendants 

order by parent, level, descendant
option (maxrecursion 0) ;

DROP TABLE #source;
    • 3

相关问题