SQL_遞歸查詢(複雜查詢示例)

需求: 一篇文章里有很多評論,每個評論又有很多回覆評論,要求: 頁面將文章展示出來,且文章的主評論按照評論時間分頁展示,回覆評論的評論完全展示在每個主評論下麵,且按照回覆時間排序

最終查詢結果SQL查詢結果如下:

Code: 評論編碼,ParentCode:回覆評論編碼,num:主評論序號,lvl:評論層級,CreateTime:評論創建時間

SQL詳細過程如下: 

1-先創建我們的數據臨時表作為基礎數據:

a.ContentInfoCode為文章編碼

SELECT * INTO #a FROM (
SELECT A.LikeNum, F.Title AS ContentTitle,A.CommentNum,A.ValidStatus,  A.Code,  E.CommentInfo as ReplayCommentInfo,A.CommentInfo,A.ParentCode, A.CreateTime ,B.LogoImageUrl ,( CASE WHEN D.Code IS NULL then '0' else '1' end) as IsLike ,C.LoginName,C.UserName,G.LoginName AS ReplyLoginName,G.UserName as ReplyUserName,f.ValidStatus as ContentValidStatus,a.ContentInfoCode
FROM [Content].Comment A LEFT JOIN [OAuth].[UserHeadPhoto] B ON  A.Creator=B.UserID and B.[VersionEndTime]='9999-12-31 23:59:59'
 LEFT JOIN [OAuth].[UserInfo] C ON (A.Creator=C.UserID and c.Status=1 )
  left join [Content].[UserAction] D ON (A.Code=D.CommentCode AND D.ActionType=1 AND D.Creator='b231f35a76a346449b2f4c5ddb4f88e7' ) 
  left Join [Content].Comment E ON A.ParentCode=E.Code left join [Content].[ContentInfo] F on a.ContentInfoCode =f.code  left join [OAuth].[UserInfo] G ON (E.Creator=G.UserID and G.Status=1 )
 WHERE 1=1  and a.ContentInfoCode='042f89bea6ee40f4968d2a219352f2f8'
) A 

2-按照遞歸查詢格式創建遞歸查詢:

with cte as
(
    SELECT * FROM (
    select *,0 as lvl,ROW_NUMBER() over(order by CreateTime  desc ) num from #a 
    WHERE ParentCode IS NULL 
    ) B WHERE B.num BETWEEN 1 AND 20  --主評論集合
    union all
    select #a.*,c.lvl+1,c.num     --子評論要展示的列(lvl層級列,按照c.Code=#a.ParentCode每層級+1,子評論num列直接使用主評論序號)
    from cte c 
    inner join #a on c.Code = #a.ParentCode --主評論和子評論關係
)
select * from cte ORDER BY num,CreateTime DESC  --查詢最終結果_按照主評論序號和創建時間排序

 3-查詢結果,並刪除臨時表:

select * from cte ORDER BY num,CreateTime DESC   --主評論和子評論關係

DROP TABLE #a   --刪除臨時表