我有这个Oracle代码结构我正在尝试转换为sql Server 2008(注意：我在方括号'[]’中使用了通用名称,封闭的列名和表名,并做了一些格式化以使代码更多可读)：
SELECT [col#1],[col#2],[col#3],...,[col#n],[LEVEL]
FROM (SELECT [col#1],[col#n]
FROM [TABLE_1]
WHERE ... )
CONNECT BY PRIOR [col#1] = [col#2]
START WITH [col#2] IS NULL
ORDER SIBLINGS BY [col#3]
具体来说,我正在努力与LEVEL和’ORDER SIBLINGS BY’Oracle构造.
注意：
上面的“代码”是一组Oracle过程的最终输出.基本上,’WHERE’子句是动态构建的,并根据传递的各种参数而变化.以“CONNECT BY PRIOR”开头的代码块是硬编码的.
以供参考：
Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER文章接近尾声,但它没有解释如何处理’LEVEL’和’ORDER SIBLINGS’结构. ……我的思绪正在变化！
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存储过程以构建正确的T-sql语句.
非常感谢协助.
解决方法
模拟LEVEL列
通过递增递归部分中的计数器可以轻松地模拟级别列：
WITH tree (empid,name,level) AS (
SELECT empid,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,level,sort_path) AS (
SELECT empid,1 as level,cast('/' + right('000000' + CONVERT(varchar,sort_order),6) as varchar(max))
FROM emp
WHERE name = 'Joan'
UNION ALL
SELECT child.empid,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中也不起作用.
