Pregunta SQL Server Equivalente a Oracle "CONECTAR POR ANTERIOR" y "ORDENAR HERMANOS POR"


Tengo este código de Oracle estructura Estoy tratando de convertir a SQL Server 2008 (Nota: He usado nombres genéricos, nombres de columnas adjuntas y nombres de tablas entre corchetes '[]', y he hecho algunos formatos para hacer que el código sea más legible):

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]

¿Cuál es el equivalente de SQL Server? modelo del código anterior?

Específicamente, estoy luchando con el NIVEL, y el orden HERMANOS POR 'Oracle construye.

Nota:  El "código" anterior es el resultado final de un conjunto de procedimientos de Oracle. Básicamente, el La cláusula 'WHERE' se construye dinámicamente y cambios dependiendo de varios parámetros aprobados. los El bloque de código que comienza con 'CONNECT BY PRIOR' está codificado.


Para referencia:

los Simulación de CONNECT BY PRIOR de ORACLE en SQL SERVER El artículo se cierra, pero no explica cómo manejar el 'NIVEL' y las construcciones 'ORDEN SIBLINGS'. ... Y mi mente se está poniendo en un giro!

SELECT name 
  FROM emp
  START WITH name = 'Joan'
  CONNECT BY PRIOR empid = mgrid

Igual a:

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

Si tengo una plantilla inicial para trabajar, esto me ayudará mucho a construir procedimientos almacenados de SQL Server para crear una declaración T-SQL correcta.

La asistencia será muy apreciada.


5
2017-08-05 08:55


origen


Respuestas:


Simulando la columna NIVEL

La columna de nivel se puede simular fácilmente incrementando un contador en la parte recursiva:

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;

Simulando order siblings by

Simulando el order siblings by es un poco mas complicado Suponiendo que tenemos una columna sort_order que define el orden de los elementos por padre (no el orden general, porque entonces order siblings no sería necesario), entonces podemos crear una columna que nos dé un orden general:

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;

La expresión para el sort_path parece tan complicado porque SQL Server (al menos la versión que está usando) no tiene una función simple para formatear un número con ceros iniciales. En Postgres utilizaría una matriz entera para que la conversión a varchar No es necesario, pero tampoco funciona en SQL Server.


11
2017-08-05 14:25



La opción dada por el usuario "a_horse_with_no_name" funcionó para mí. Cambié el código, lo apliqué a una consulta del generador de menús y funcionó la primera vez. Aquí está el código:

WITH tree(option_id,
       option_description,
      option_url,
      option_icon,
      option_level,
      sort_path)
     AS (
     SELECT ppo.option_id,
            ppo.option_description,
          ppo.option_url,
          ppo.option_icon,
          1 AS option_level,
          CAST('/' + RIGHT('00' + CONVERT(VARCHAR, ppo.option_index), 6) AS VARCHAR(MAX))
     FROM security.options_table_name ppo
     WHERE ppo.option_parent_id IS NULL
     UNION ALL
     SELECT co.option_id,
            co.option_description,
          co.option_url,
          co.option_icon,
          po.option_level + 1,
          po.sort_path + '/' + RIGHT('00' + CONVERT(VARCHAR, co.option_index), 6)
     FROM security.options_table_name co,
          tree AS po
     WHERE po.option_id = co.option_parent_id)
     SELECT *
     FROM tree
    ORDER BY sort_path;

0
2017-10-30 15:46



Preguntas populares