34,576
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
GO
CREATE TABLE t1(
id INT IDENTITY(1,1) PRIMARY KEY,
pid INT,
[name] NVARCHAR(20)
)
GO
SET NOCOUNT ON
INSERT INTO t1(pid,[name]) VALUES (0,'组织结构');
INSERT INTO t1(pid,[name]) VALUES (1,'人力资源');
INSERT INTO t1(pid,[name]) VALUES (1,'财务');
INSERT INTO t1(pid,[name]) VALUES (1,'采购');
INSERT INTO t1(pid,[name]) VALUES (2,'招聘专员');
INSERT INTO t1(pid,[name]) VALUES (2,'招聘主管');
INSERT INTO t1(pid,[name]) VALUES (2,'人力经理');
INSERT INTO t1(pid,[name]) VALUES (3,'财务经理');
INSERT INTO t1(pid,[name]) VALUES (3,'出纳');
INSERT INTO t1(pid,[name]) VALUES (4,'采购经理');
INSERT INTO t1(pid,[name]) VALUES (4,'采购专员');
GO
---- 以上为测试表及测试数据 -----
--加一个标量函数,后面获取路径就非常方便了:
IF OBJECT_ID('Fun_GetPath') IS NOT NULL
DROP FUNCTION Fun_GetPath
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION dbo.Fun_GetPath
(
@id INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @r NVARCHAR(MAX)
;WITH cte AS (
SELECT *,0 AS dep FROM t1 WHERE id=@id
UNION ALL
SELECT t1.*,dep+1 AS dep FROM t1 INNER JOIN cte ON t1.id=cte.pid
)
SELECT @r = REPLACE(STUFF( (SELECT '》'+[name] FROM cte ORDER BY dep DESC FOR XML PATH('')),1,1,''),'》','>')
RETURN @r;
END
GO
SELECT *,dbo.Fun_GetPath(t1.id) AS [path] FROM t1