日期:2014-05-17 浏览次数:20530 次
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 指标序号,上级指标