首页 > 代码库 > 理解 with递归调用 Sqlserver 树查询
理解 with递归调用 Sqlserver 树查询
--with用法 --可以这么理解 with SQL语句变量或者叫临时表名 as( SQL语句 ) select * from SQL语句变量或者叫临时表名
--递归调用 with CTE as( select ZTBM_ID,ztbm_name,ParentId from TB_ZYM_ZTBM where ParentId is null or ParentId=‘‘ union all select a.ZTBM_ID,a.ztbm_name,a.ParentId from TB_ZYM_ZTBM a inner join CTE on a.ParentId=CTE.ZTBM_ID ) select * from CTE --此语句可以理解如下
--同上一个语句 --最顶层 1 select ZTBM_ID,ztbm_name,ParentId from TB_ZYM_ZTBM where ParentId is null or ParentId=‘‘ union all select a.ZTBM_ID,a.ztbm_name,a.ParentId from TB_ZYM_ZTBM a inner join --此处括弧可以理解为最顶层的CTE 此处红色CTE即上一个SQL语句的CTE ( --第二层 select ZTBM_ID,ztbm_name,ParentId from TB_ZYM_ZTBM where ParentId is null or ParentId=‘‘ union all select a.ZTBM_ID,a.ztbm_name,a.ParentId from TB_ZYM_ZTBM a inner join --此处括弧可以理解为第二层的CTE ( --第三层 select ZTBM_ID,ztbm_name,ParentId from TB_ZYM_ZTBM where ParentId is null or ParentId=‘‘ union all select a.ZTBM_ID,a.ztbm_name,a.ParentId from TB_ZYM_ZTBM a inner join ( --第四层 select ZTBM_ID,ztbm_name,ParentId from TB_ZYM_ZTBM where ParentId is null or ParentId=‘‘ union all select a.ZTBM_ID,a.ztbm_name,a.ParentId from TB_ZYM_ZTBM a inner join ( select ZTBM_ID,ztbm_name,ParentId from TB_ZYM_ZTBM where ParentId is null or ParentId=‘‘ --inner join.... 层层嵌套 --最底层 ) CTE on a.ParentId=CTE.ZTBM_ID ) CTE on a.ParentId=CTE.ZTBM_ID ) CTE on a.ParentId=CTE.ZTBM_ID ) CTE on a.ParentId=CTE.ZTBM_ID
变异如下查询(排序,及分割)
with CTE as ( -->Begin 一个定位点成员 select ZTBM_ID, ztbm_name,ParentId,cast(ztbm_name as nvarchar(max)) as TE, ROW_NUMBER()over(order by getdate()) as OrderID,0 as Levle from TB_ZYM_ZTBM where deleteMark=1 and (ParentId is null or ParentId=‘‘) -->End union all -->Begin一个递归成员 select TB_ZYM_ZTBM.ZTBM_ID, TB_ZYM_ZTBM.ztbm_name,TB_ZYM_ZTBM.ParentId,cast(replicate(‘ ‘,Levle+1)+‘|_‘+TB_ZYM_ZTBM.ztbm_name as nvarchar(MAX)) as TE, CTE.OrderID*100+ROW_NUMBER()over(Order by GETDATE()) as OrderID ,Levle+1 as Levle from TB_ZYM_ZTBM inner join CTE on TB_ZYM_ZTBM.ParentId=CTE.ZTBM_ID -->End ) select * from CTE order by LTRIM(OrderID)
参考:http://blog.csdn.net/bin_520_yan/article/details/5998349
http://msdn.microsoft.com/zh-cn/library/ms175972(SQL.105).aspx
理解 with递归调用 Sqlserver 树查询
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。