SQL Server等同于Oracle'CONNECT BY PRIOR'和'ORDER SIBLINGS BY' [英] SQL Server Equivalent of Oracle 'CONNECT BY PRIOR', and 'ORDER SIBLINGS BY'
问题描述
我已经获得了此Oracle代码结构，我正在尝试转换为SQL Server 2008 (注意:，我使用了通用名称，封闭的列名称和表名放在方括号"[]"中，并进行了一些格式化，以使代码更具可读性):
I've got this Oracle code structure I'm trying to convert to SQL Server 2008 (Note: I have used generic names, enclosed column names and table names within square brackets '[]', and done some formatting to make the code more readable):
SELECT [col#1], [col#2], [col#3], ..., [col#n], [LEVEL]
FROM (SELECT [col#1], [col#2], [col#3], ..., [col#n]
FROM [TABLE_1]
WHERE ... )
CONNECT BY PRIOR [col#1] = [col#2]
START WITH [col#2] IS NULL
ORDER SIBLINGS BY [col#3]
上述代码的SQL Server等效模板是什么?
What is the SQL Server equivalent template of the above code?
特别是，我正在为 LEVEL 和'ORDER SIBLINGS BY'Oracle构造而苦苦挣扎.
Specifically, I'm struggling with the LEVEL, and 'ORDER SIBLINGS BY' Oracle constructs.
注意: 上面的代码"是一组Oracle过程的最终输出.基本上， 'WHERE'子句是动态构建的 ，并根据传递的各种参数而变化.以'CONNECT BY PRIOR'开头的代码块是硬编码的..
Note: The above "code" is the final output from a set of Oracle procedures. Basically, the 'WHERE' clause is built up dynamically and changes depending on various parameters passed. The code block starting with 'CONNECT BY PRIOR' is hard-coded.
供参考:
SQL SERVER中ORACLE的PRIOR优先级对CONNECT的模拟接近，但没有解释如何处理"LEVEL"和"ORDER SIBLINGS"构造. ...而且我的想法正在转弯！
The Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER article comes close, but it does not explain how to handle the 'LEVEL' and the 'ORDER SIBLINGS' constructs. ... And my mind is getting in a twist!
SELECT name
FROM emp
START WITH name = 'Joan'
CONNECT BY PRIOR empid = mgrid
等于:
WITH n(empid, name) AS
(SELECT empid, name
FROM emp
WHERE name = 'Joan'
UNION ALL
SELECT nplus1.empid, nplus1.name
FROM emp as nplus1, n
WHERE n.empid = nplus1.mgrid)
SELECT name FROM n
如果我有一个初始模板可以使用，它将大大帮助我构造SQL Server存储的procs以建立正确的T-SQL语句.
If I have an initial template to work from, it will go a long way to helping me construct SQL Server stored procs to build up a correct T-SQL statement.
我们将非常感谢您的协助.
Assistance will be much appreciated.
推荐答案
模拟LEVEL列
可以通过在递归部分中增加一个计数器来轻松地模拟级别列:
Simulating the LEVEL column
The level column can easily be simulated by incrementing a counter in the recursive part:
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;
模拟order siblings by
模拟order siblings by有点复杂.假设我们有一个列sort_order来定义每个父元素的元素顺序(而不是整体排序顺序-因为这样就不必order siblings了)，那么我们可以创建一个列来给我们一个整体排序顺序:
Simulating order siblings by
Simulating the order siblings by is a bit more complicated. Assuming we have a column sort_order that defines the order of elements per parent (not the overall sort order - because then order siblings wouldn't be necessary) then we can create a column which gives us an overall 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中也不起作用.
The expression for the sort_path looks so complicated because SQL Server (at least the version you are using) does not have a simple function to format a number with leading zeros. In Postgres I would use an integer array so that the conversion to varchar isn't necessary - but that doesn't work in SQL Server either.
这篇关于SQL Server等同于Oracle'CONNECT BY PRIOR'和'ORDER SIBLINGS BY'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
