在数据库的存储中我们经常会碰到这样的树形数据结构:
最常用就是菜单:
问题的产生:在一个系统的设计中,经常会遇到如上图的表,它由一个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 '%系统%'