SQL数据库调优

1、使用With As做数据库递归，调优树形表结构

例如：设计表结构简化如：ID、ParentID、Name；这里的ParentID就是这个表本身的某个ID

 

WITH cte AS
(
    SELECT a.* FROM table1 AS a WHERE ID=5
    UNION ALL
    SELECT b.* FROM table1 AS b INNER JOIN cte ON 
    b.ParentID=cte.ID
)

select * from cte  

 

;with cte as
(
select OrgId, OrgName, IsOne, ParentId,OrgName as rid from Org where IsOne=1
union all
select c.OrgId, c.OrgName, c.IsOne, c.ParentId,p.rid from cte p 
inner join Org c on p.OrgId=c.ParentId
)