SQL 通用递归查询

在数据库的存储中我们经常会碰到这样的树形数据结构：

最常用就是菜单：

 

问题的产生：在一个系统的设计中，经常会遇到如上图的表，它由一个id与pid进行层级关系的控制。菜单、角色、部门....都会用到。

现在假设我要查询物资系统里的菜单：物资系统Node_Id为2000  那么，Node_Pid为2000的数据也应该出来、接着 Node_Pid为（Node_Pid为2000的数据的Node_Id）的数据同样该出来，这个时候怎么办？？？这个查询应该要怎么写？看官可以先试着写写看。

问题就因为这样产生了，写这样的SQL实现是很繁琐的工作,SQL CTE可以帮我们解决这个问题。但此类设计又会有很多地方都会用到，那么有没有一种通用的办法，能解决所有用到这种数据设计的查询呢？

前2年写过这样一个存储过程，今天我就把它分享出来，直接上代码了。

CREATE PROC [dbo].[System_GetTree]          
(        
@tbName nvarchar(200),        
@tIDName nvarchar(50),        
@pIDName nvarchar(50),        
@pid int=0,      
@whe nvarchar(2000)=''    
)          
as          
begin          
DECLARE @strSQL NVARCHAR(MAX);        
if(@pid!=0)          
begin          
SET @strSQL='        
WITH    SimpleRecursive ('+@tIDName+','+@pIDName+')          
          AS ( SELECT   '+@tIDName+','+@pIDName+'         
               FROM     '+@tbName+'          
               WHERE    '+@tIDName+' = '+CAST(@pid AS NVARCHAR(10))+' '+@whe+' UNION ALL          
               SELECT   p.'+@tIDName+' ,          
                        p.'+@pIDName+'          
               FROM     '+@tbName+' p          
                        INNER JOIN SimpleRecursive A ON A.'+@tIDName+' = p.'+@pIDName+' WHERE 1=1 '+@whe+'     
             )          
                       
    SELECT  et.*,1 as isDown into #SimpTB        
    FROM    SimpleRecursive sr          
            INNER JOIN '+@tbName+' et ON sr.'+@tIDName+' = et.'+@tIDName+' WHERE 1=1 '+@whe+';        
        
WITH    SimpleRecursive ('+@tIDName+','+@pIDName+')          
          AS ( SELECT   '+@tIDName+','+@pIDName+'         
               FROM     '+@tbName+'          
               WHERE    '+@tIDName+' = '+CAST(@pid AS NVARCHAR(10))+' '+@whe+'  UNION ALL          
               SELECT   p.'+@tIDName+' ,          
                        p.'+@pIDName+'          
               FROM     '+@tbName+' p          
                        INNER JOIN SimpleRecursive A ON A.'+@pIDName+' = p.'+@tIDName+'  WHERE 1=1 '+@whe+'          
             )          
                       
    SELECT  et.*,0 as isDown into #SimpTB1        
    FROM    SimpleRecursive sr          
            INNER JOIN '+@tbName+' et ON sr.'+@tIDName+' = et.'+@tIDName+' WHERE 1=1 AND et.'+@tIDName+'!='+CAST(@pid as NVARCHAR(10))+' '+@whe+' ;        
    SELECT * FROM #SimpTB Union ALL SELECT * FROM #SimpTB1 ORDER BY '+@pIDName+','+@tIDName+' ;        
    DROP TABLE #SimpTB;DROP TABLE #SimpTB1;        
                '        
end          
else          
begin          
 SET @strSQL='select * from '+@tbName+' WHERE 1=1 '+@whe+' '         
end          
PRINT @strSQL        
EXEC (@strSQL)        
end 

 

存储过程的调用方式：exec System_GetTree 'Tb_System_PowerNode','Node_Id','Node_Pid',0,'';

参数1：表名

参数2：id字段名称

参数3：pid字段名称

参数4：id字段名称的父级值,(这个地方可以把类型该为nvarchar可能更为通用些)

参数5：查询条件带and部分，如：and Node_Name like '%系统%'