SQL Server 递归查询上下级

表结构如下：

CREATE TABLE [dbo].[sys_org]([id] [nvarchar](50) NOT NULL,[name] [nvarchar](100) NULL,[parentid] [nvarchar](50) NULL,CONSTRAINT [PK_sys_org] PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GO

表数据：

1.查询所有上级

-- 查询所有上级;WITH TreeAS(SELECT * FROM sys_org WHERE id='D02'UNION ALLSELECT tab.* FROM sys_org tab INNER JOIN Tree ON tab.id=Tree.parentid)SELECT * FROM Tree

2.查询所有下级

-- 查询所有下级;WITH TreeAS(SELECT * FROM sys_org WHERE parentid='0'UNION ALLSELECT tab.* FROM sys_org tab inner JOIN Tree ON tab.parentid =Tree.id)SELECT * FROM Tree

3.查询当前id的层级

-- 查询当前id的层级 ;WITH TreeAS(SELECT * FROM sys_org WHERE id='B01'UNION ALLSELECT tab.* FROM sys_org tab INNER JOIN Tree ON tab.id=Tree.parentid)SELECT COUNT(*) as level FROM Tree

4.拼接所有上级加当前的id

拼接id，所有的上级id+当前id例如 id=B01,拼接后的id为:01A01B01

由于查询出来的数据是倒序，id又没有排序规律，因此加了一个空字符串字段“px”

--拼接id，所有的上级id+当前id 例如 id=B01,拼接后的id为:01A01B01--由于查询出来的数据是倒序，id又没有排序规律，因此加了一个空字符串字段“px”;WITH TreeAS(SELECT *,'' px FROM sys_org WHERE id='B01'UNION ALLSELECT tab.* ,'' px FROM sys_org tab INNER JOIN Tree ON tab.id=Tree.parentid)select (select ''+ id from (SELECT *, ROW_NUMBER() OVER(order by px) as row FROM Tree )bbb order by row desc FOR XML PATH (''))as orgid