oracle order siblings by,sql-server – SQL Server相当于Oracle的“CONNECT BY PRIOR”和“ORDER SIBLINGS BY”...

模拟LEVEL列

通过递增递归部分中的计数器可以轻松地模拟级别列：

WITH tree (empid, name, level) AS (

SELECT empid, name, 1 as level

FROM emp

WHERE name = 'Joan'

UNION ALL

SELECT child.empid, child.name, parent.level + 1

FROM emp as child

JOIN tree parent on parent.empid = child.mgrid

)

SELECT name

FROM tree;

模拟兄弟姐妹的顺序

模拟兄弟姐妹的顺序有点复杂.假设我们有一个列sort_order来定义每个父元素的顺序(不是整体排序顺序 – 因为那时不需要兄弟顺序),那么我们可以创建一个列,它给出了一个整体排序顺序：

WITH tree (empid, name, level, sort_path) AS (

SELECT empid, name, 1 as level,

cast('/' + right('000000' + CONVERT(varchar, sort_order), 6) as varchar(max))

FROM emp

WHERE name = 'Joan'

UNION ALL

SELECT child.empid, child.name, parent.level + 1,

parent.sort_path + '/' + right('000000' + CONVERT(varchar, child.sort_order), 6)

FROM emp as child

JOIN tree parent on parent.empid = child.mgrid

)

SELECT *

FROM tree

order by sort_path;

sort_path的表达式看起来很复杂,因为SQL Server(至少是您使用的版本)没有简单的函数来格式化带前导零的数字.在Postgres中,我将使用整数数组,因此不需要转换为varchar – 但这在SQL Server中也不起作用.