CREATE TABLE [dbo].[T_leader_group](
[id] [int] IDENTITY(1,1) NOT NULL,
[groupname] [varchar](50) NULL,
[parentid] [int] NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[groupname] [varchar](50) NULL,
[parentid] [int] NULL,
) ON [PRIMARY]
查询语句
WITH StepCTE
AS (
SELECT [id],parentid,[groupname],1 as Lev
FROM T_leader_group
WHERe id = 7
UNION ALL SELECT
t.[id],t.parentid,t.[groupname],
CTE.Lev + 1
FROM
T_leader_group T INNER JOIN StepCTE CTE
ON T.parentid = CTE.id
)
SELECT
[id],parentid,[groupname], Lev
FROM StepCTE;