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 Tree
AS(
		SELECT * FROM sys_org WHERE id='D02'
		UNION ALL
		SELECT tab.*  FROM sys_org tab INNER JOIN Tree ON tab.id=Tree.parentid
	)
SELECT *  FROM Tree 

2.查询所有下级

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

3.查询当前id的层级

-- 查询当前id的层级 
;WITH Tree
AS(
		SELECT * FROM sys_org WHERE id='B01'
		UNION ALL
		SELECT 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 Tree
AS(
		SELECT *,'' px FROM sys_org WHERE id='B01'
		UNION ALL
		SELECT 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