求两条sql语句，
表a
指标序号 上级指标 指标说明
1 0 aaa
2 0 bbb
3 1 ccc
4 2 ddd
5 3 eee
6 4 fff
第一条，给出指标序号6，得出他的最顶级指标2，如下
2 0 bbb
第二条，给出指标序号2,得出他及他的下属指标
2 0 bbb
4 2 ddd
6 4 fff
[解决办法]
IF OBJECT_ID(N'表a',N'U') IS NOT NULL
DROP TABLE 表a
CREATE TABLE 表a(指标序号 INT ,上级指标 INT,指标说明 VARCHAR(100))
INSERT INTO 表a
SELECT 1,0,'aaa'
UNION ALL
SELECT
2 ,0 ,'bbb'
UNION ALL
SELECT
3 ,1 ,'ccc'
UNION ALL
SELECT
4 ,2 ,'ddd'
UNION ALL
SELECT
5 ,3 ,'eee'
UNION ALL
SELECT
6 ,4 ,'fff'
GO
Declare @Id Int
SET @ID = 6
;WITH ParentNodeCTE AS
(
SELECT 指标序号 , 上级指标 , 指标说明
FROM 表a
WHERE 指标序号 = @ID
UNION ALL
SELECT a.指标序号 , a.上级指标 , a.指标说明
FROM 表a AS a JOIN ParentNodeCTE AS b ON a.指标序号 = b.上级指标
)
SELECT TOP 1 * FROM ParentNodeCTE ORDER BY 指标序号
---------------------------------------------------------------------
Set @Id = 2;
With RootNodeCTE(指标序号,上级指标,指标说明)
As
(
Select 指标序号,上级指标,指标说明 From 表a Where 指标序号 IN (@Id)
Union All
Select 表a.指标序号,表a.上级指标,表a.指标说明 From RootNodeCTE
Inner Join 表a
On RootNodeCTE.指标序号 = 表a.上级指标
)
Select * From RootNodeCTE
/*
指标序号 上级指标 指标说明
----------- ----------- ----------------------------------------------------------------------------------------------------
2 0 bbb
(1 行受影响)
指标序号 上级指标 指标说明
----------- ----------- ----------------------------------------------------------------------------------------------------
2 0 bbb
4 2 ddd
6 4 fff
(3 行受影响)
*/
CREATE TABLE #t(id INT,pid INT ,c VARCHAR(10))
INSERT INTO #T SELECT 1,0,'aaa' UNION ALL
SELECT 2,0,'bbb' UNION ALL
SELECT 3,1,'ccc' UNION ALL
SELECT 4,2,'ddd' UNION ALL
SELECT 5,3,'eee' UNION ALL
SELECT 6,4,'fff' ;
-- 给6
;WITH cte AS (
SELECT * FROM #t WHERE id=6
UNION ALL
SELECT a.* FROM #t a JOIN cte b ON a.id=b.pid
)
SELECT * FROM cte WHERE pid=0;
--给2
;WITH cte AS (
SELECT * FROM #t WHERE id=2
UNION ALL
SELECT a.* FROM #t a JOIN cte b ON a.pid=b.id
)
SELECT * FROM cte;
DROP TABLE #t;
/*
id pid c
----------- ----------- ----------
2 0 bbb
(1 行受影响)
id pid c
----------- ----------- ----------
2 0 bbb
4 2 ddd
6 4 fff
*/
