带排序的递归子查询 [英] Recursive Subquerying with sorting

问题描述

我在此处看过蒂姆·霍尔的精彩文章，允许您使用自引用实体，并使用Oracle中的CTE语法显示层次数据(从顶级节点开始并递归联接).

I looked at Tim Hall's excellent article here, that allows you to work with self-referenced entities and show hierarchical data (starting with top level nodes and joining back recursively), using CTE like syntax in Oracle.

所以我有如下代码:

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父级)，我想:

For the the anchor rows (the top level hierarchy J1 entries in my SQL, with NULL parents), I would like to:

 ORDER BY  J1.JOBMST_NAME 

对于递归联接:

ORDER BY J2.JOBMST_PRNTID, J2.JOBMST_NAME

• 如果尝试在UNION ALL语句上方添加ORDER BY语句，则会得到某种无效的SQL语法.

• 您如何解决此问题，以便最后按层次结构中每个深度级别的名称按字母顺序对数据进行排序?

  • If I try and add an ORDER BY statement above the UNION ALL statement I get some sort of invalid SQL syntax.

  • How do you work around that, so the data in the end is sorted alphabetically by name, at each level of depth in the hierarchy?

    (如果在连接点对数据进行了正确排序，则SEARCH DEPTH FIRST创建的DISP_SEQ应该正确整理数据).

    (If the data is sorted correctly at the point of the joins, the DISP_SEQ created by SEARCH DEPTH FIRST should collate the data correctly).

    您最终得到的是这样的名称(省略名称):

    You end up with something like this (names omitted):

    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的字母顺序排列，
    • 等，等等.

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

    Update: I have managed to tweak the code somewhat, so the anchor select is sorted:

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

    But I can't seem to apply same syntactic sugar to the recursive join.

    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
    

    推荐答案

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

    Initially, I could not see a more elegant solution than to create a temp table.

    我当时在想，什么是SQL Oracle的尴尬方言:

    I was thinking, what a awkward dialect of SQL Oracle is:

    1. 为什么没有IF表存在删除表?
    2. 为什么必须对字符串立即执行?为什么我不能仅自己做DROP TABLE TEMP?
    3. 为什么没有嵌套在ANCHOR上的括号就不能拥有ORDER BY?
    4. 为什么在UNION ALL之后不能对递归SELECT进行ORDER BY?
    5. SQL WITH需要标准化.其他数据库方言不需要在WITH语句上用括号括起来的列名.如果不这样做，则会在UNION ALL之后进行递归联接时出现一些毫无意义的ALIAS错误.
    6. 分页:请参见此处无限制/偏移
    1. Why no IF TABLE EXISTS DELETE TABLE?
    2. Why do I have to do EXECUTE IMMEDIATE with a string? Why can't I just do DROP TABLE TEMP on its own?
    3. Why can't I have ORDER BY without nesting in parentheses on ANCHOR?
    4. Why can't I have ORDER BY on recursive SELECT after UNION ALL?
    5. SQL WITH needs standardising. Other database dialects don't necessitate column names being parenthesised on WITH statement. If you don't do that you get some meaningless ALIAS error, at the point of the recursive join after UNION ALL.
    6. Pagination: See here No LIMIT / OFFSET

    ---

    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社区论坛上的数学家)向我指出了我的搜索深度优先应该刚刚由JOBMST_NAME来过.

    ---

    Then (mathguy on the Oracle Community Forum) pointed out to me my SEARCH DEPTH FIRST should have just been by 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
    

    这篇关于带排序的递归子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！