sqlserver树状结构表中,获取指定节点的所有父节点路径_MySQL
- WBOYOriginal
- 2016-05-30 17:09:491867browse
CREATE PROCEDURE [dbo].[A_P_GetParentIds]
(
@IdValue NVARCHAR(36) ,-- 子节点值
@tableName NVARCHAR(MAX) ,-- 表名
@ParentIdColumnName NVARCHAR(MAX) ,-- 父节点列名
@ChildIdColumnName NVARCHAR(MAX)-- 子节点列名
)
AS
DECLARE @sqlTemp NVARCHAR(MAX); --查询sql
SET @sqlTemp = ' with cte_root as
(
select ' + @ParentIdColumnName + ' from ' + @tableName + ' where '
+ @ChildIdColumnName + ' = ''' + @IdValue + ''' union all
select a.' + @ParentIdColumnName + ' from ' + @tableName
+ ' a inner join cte_root b on a.' + @ChildIdColumnName + '=b.'
+ @ParentIdColumnName + ' ) ' + CHAR(10) + 'select * from cte_root ';
------返回查询结果-----
EXEC sp_executesql @sqlTemp;
GO
CREATE PROCEDURE [dbo].[A_P_GetParentInfo]
(
@IdValue NVARCHAR(36) ,-- 子节点值
@tableName NVARCHAR(MAX) ,-- 表名
@ParentIdColumnName NVARCHAR(MAX) ,-- 父节点列名
@ChildIdColumnName NVARCHAR(MAX)-- 子节点列名
)
AS
DECLARE @sqlTemp NVARCHAR(MAX); --查询sql
SET @sqlTemp = ' with cte_root as
(
select * from ' + @tableName + ' where '
+ @ChildIdColumnName + ' = ''' + @IdValue + ''' union all
select a.* from ' + @tableName
+ ' a inner join cte_root b on a.' + @ChildIdColumnName + '=b.'
+ @ParentIdColumnName + ' ) ' + CHAR(10) + 'select * from cte_root ';
------返回查询结果-----
EXEC sp_executesql @sqlTemp;
GO
调用测试：
DECLARE @return_value int EXEC @return_value = [dbo].[A_P_GetParentIds] @IdValue = N'a29672f9-3d03-4cfc-9cb0-403ce729db5b',--子节点值 @tableName = N'SystemMenu',--表名 @ParentIdColumnName = N'ParentId',--父节点列名 @ChildIdColumnName = N'Id'--子节点列名 SELECT 'Return Value' = @return_value GO
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：MySQL编程中的6个重要的实用技巧_MySQLNext article：使用CTE解决复杂查询的问题_MySQL