带排序的递归子查询

一尘不染

带排序的递归子查询

sql

我在此处查看了Tim
Hall的精彩文章，该文章允许您使用自引用实体并使用Oracle中的CTE语法显示分层数据（从顶级节点开始，然后递归返回）。

所以我有看起来像这样的代码：

WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (   
  SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1   
  FROM TIDAL.JOBMST   
  WHERE JOBMST_PRNTID IS NULL   
  UNION ALL   
  SELECT J2.JOBMST_ID,J2.JOBMST_NAME,J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL+1    
  FROM TIDAL.JOBMST J2    
  INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID    
  WHERE J2.JOBMST_PRNTID IS NOT NULL)    
SEARCH DEPTH FIRST BY JOBMST_ID SET DISP_SEQ
SELECT *
FROM J1
ORDER BY DISP_SEQ

对于锚行（SQL中的顶层层次结构J1条目，其父级为NULL），我想：

 ORDER BY  J1.JOBMST_NAME

对于递归联接：

ORDER BY J2.JOBMST_PRNTID, J2.JOBMST_NAME

如果尝试在UNION ALL语句上方添加ORDER BY语句，则会得到某种无效的SQL语法。
您如何解决此问题，以便最后按层次结构中每个深度级别的名称按字母顺序对数据进行排序？
（如果数据在连接点正确排序，则SEARCH DEPTH FIRST创建的DISP_SEQ应该正确整理数据）。

您最终得到的是这样的东西（名称被省略）：

JOBMST_ID JOBMST_NAME JOBMST_PRNTID JOBMST_TYPE LVL DISP_SEQ
 746                                1           1   1
1433                                1           1   2
1328                   1433         1           2   3
1329                   1328         1           3   4
1330                   1329         1           4   5
1331                   1329         1           4   6
1332                   1329         1           4   7

我的目标：

所有1级按JOBMST_NAME的字母顺序排序
1级中的所有2级按父级按JOBMST_NAME的字母顺序排序
每个父级中，第2级中的所有3级都按JOBMST_NAME的字母顺序排序，
等，等等。

更新：我设法对代码进行了一些调整，因此对锚选择进行了排序：

但是我似乎无法将相同的语法糖应用于递归联接。

WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (
  SELECT * FROM (
    SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
    FROM TIDAL.JOBMST
    WHERE JOBMST_PRNTID IS NULL
    ORDER BY JOBMST_NAME
  )
UNION ALL
SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1
FROM TIDAL.JOBMST J2
INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
WHERE J2.JOBMST_PRNTID IS NOT NULL
)
SEARCH DEPTH FIRST BY JOBMST_ID SET DISP_SEQ
SELECT *
FROM J1
ORDER BY DISP_SEQ

阅读 161

2021-05-05

共1个答案

一尘不染

最初，我看不到比创建临时表更好的解决方案。

我当时在想，SQL Oracle的尴尬方言是：

为什么没有IF表存在删除表？
为什么必须对字符串执行EXECUTE IMMEDIATE？为什么我不能仅自己做DROP TABLE TEMP？
为什么没有嵌套在ANCHOR上的括号内就不能拥有ORDER BY？
为什么在UNION ALL之后不能对递归SELECT进行ORDER BY？
SQL WITH需要标准化。其他数据库方言不需要在WITH语句上用括号括起来的列名。如果不这样做，则会在UNION ALL之后的递归联接时出现一些毫无意义的ALIAS错误。

DECLARE
 v_c NUMBER;
BEGIN
SELECT COUNT(*) INTO v_c FROM user_tables WHERE TABLE_NAME = 'TEMP';
IF v_c = 1 THEN
  EXECUTE IMMEDIATE 'DROP TABLE TEMP';
END IF;
END;
CREATE TABLE TEMP AS  (
    SELECT * FROM (
      SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE
      FROM TIDAL.JOBMST
      WHERE JOBMST_PRNTID IS NOT NULL
      ORDER BY JOBMST_PRNTID, JOBMST_NAME
    )
);
WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (
  SELECT * FROM (
    SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
    FROM TIDAL.JOBMST
    WHERE JOBMST_PRNTID IS NULL
    ORDER BY JOBMST_NAME
  )
UNION ALL
SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1
FROM TEMP J2
INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
WHERE J2.JOBMST_PRNTID IS NOT NULL
)
SEARCH DEPTH FIRST BY JOBMST_ID SET DISP_SEQ
SELECT *
FROM J1
ORDER BY DISP_SEQ;

然后（Oracle社区论坛上的mathguy）向我指出，我的SEARCH
DEPTH FIRST应该只是JOBMST_NAME。

然后一切都就位了：

WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (
    SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
    FROM TIDAL.JOBMST
    WHERE JOBMST_PRNTID IS NULL
UNION ALL
SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1
FROM TIDAL.JOBMST J2
INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
WHERE J2.JOBMST_PRNTID IS NOT NULL
)
SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
SELECT *
FROM J1
ORDER BY DISP_SEQ

2021-05-05