成语接龙-查找指定层数以内的成语，

成语接龙-查找指定层数以内的成语，

--从一个成语开始接龙找到另一个成语
DECLARE @cb NVARCHAR(4)= '为所欲为'; --,
    --@ce NVARCHAR(15)= '鸡飞狗跳';
WITH    cte_get_path
          AS ( SELECT   word ,
                        --0 AS is_recycle ,
                        CAST(word AS NVARCHAR(MAX)) AS cpath ,
                        1 AS level ,
                        first_word ,
                        last_word
               FROM     dbo.cy
               WHERE    word = @cb
               UNION ALL
               SELECT   s.word ,
                        --CASE WHEN s.word = @ce THEN 1
                        --     ELSE 0
                        --END AS is_recycle ,
                        CAST(p.cpath + '>' + s.word AS NVARCHAR(MAX)) AS cpath ,
                        p.level + 1 AS level ,
                        s.first_word ,
                        s.last_word
               FROM     dbo.cy AS s
                        INNER JOIN cte_get_path AS p ON p.last_word = s.first_word
                                                        AND CHARINDEX(s.word,
                                                              p.cpath) = 0
                                                        AND p.level + 1 <= 5--限制成语的个数为5个
                                                        --AND p.is_recycle = 0
             )
    SELECT  *
    FROM    cte_get_path
    --WHERE   cte_get_path.word = @ce;