SQL

递归CTEs

递归CET可用于查询组织结构图、文件系统、网页之间的链接图等分层数据

递归CTE三部分：

1. 锚构件（定点成员）：返回 CTE 的基本结果的初始查询
2. 递归成员：引用 CTE 的递归查询。这是所有与锚构件的联盟
3. 停止递归构件的终止条件（隐式的；当上一个调用中未返回行时，递归将停止。）

点击查看

示例

-- 测试表建表语句
CREATE TABLE TABLENAME(
  ItemId INT PRIMARY KEY,
  ParentItemId INT,
  ItemName VARCHAR(200)
)

-- 插入数据
insert into tablename values(2,0,'管理费用');
insert into tablename values(3,0,'销售费用');
insert into tablename values(4,0,'财务费用');
insert into tablename values(5,0,'生产成本');
insert into tablename values(35,5,'材料');
insert into tablename values(36,5,'人工');
insert into tablename values(37,5,'制造费用');
insert into tablename values(38,35,'原材料');
insert into tablename values(39,35,'主要材料');
insert into tablename values(40,35,'间辅材料');
insert into tablename values(41,36,'工资');
insert into tablename values(42,36,'福利');
insert into tablename values(43,2,'管理费用子项');
insert into tablename values(113,43,'管理费用子项的子项');

-- 递归 CTE 语句（在 MySQL8.0 尝试不好使，在 Oracle 是好使的）
WITH tablenameTemp(ItemId, ItemName, levelno) AS (
SELECT ItemId, ItemName, 0 as levelno
  FROM tablename
 WHERE ParentItemId = 0
UNION ALL
SELECT T1.ItemId, T1.ItemName, T2.levelno + 1 as levelno
  FROM tablename T1
 INNER JOIN tablenameTemp T2
    ON T1.parentitemid = T2.itemid
)
SELECT * FROM tablenameTemp

参考链接 (opens new window)

很牛的用法 (opens new window)

WITH    RECURSIVE
        q (r, i, rx, ix, g) AS
        (
        SELECT  r::DOUBLE PRECISION * 0.02, i::DOUBLE PRECISION * 0.02, .0::DOUBLE PRECISION, .0::DOUBLE PRECISION, 0
        FROM    generate_series(-60, 20) r, generate_series(-50, 50) i
        UNION ALL
        SELECT  r, i, CASE WHEN ABS(rx * rx + ix * ix) <= 2 THEN rx * rx - ix * ix END + r, CASE WHEN ABS(rx * rx + ix * ix) <= 2 THEN 2 * rx * ix END + i, g + 1
        FROM    q
        WHERE   rx IS NOT NULL
                AND g < 99
        )
SELECT  ARRAY_TO_STRING(ARRAY_AGG(s ORDER BY r), '')
FROM    (
        SELECT  i, r, SUBSTRING(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
        FROM    q
        GROUP BY
                i, r
        ) q
GROUP BY
        i
ORDER BY
        i

WITH    RECURSIVE
        q (r, i, rx, ix, g) AS
        (
        SELECT  r::DOUBLE PRECISION * 0.04, i::DOUBLE PRECISION * 0.04, .0::DOUBLE PRECISION, .0::DOUBLE PRECISION, 0
        FROM    generate_series(-40, 20) r, generate_series(-40, 20) i
        UNION ALL
        SELECT  r, i, CASE WHEN ABS(rx * rx + ix * ix) <= 1E8 THEN rx * rx - ix * ix END + r, CASE WHEN ABS(rx * rx + ix * ix) <= 2 THEN ABS(2 * rx * ix) END + i, g + 1
        FROM    q
        WHERE   rx IS NOT NULL
                AND g < 99
        )
SELECT  ARRAY_TO_STRING(ARRAY_AGG(s ORDER BY r), '')
FROM    (
        SELECT  i, r, SUBSTRING(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
        FROM    q
        GROUP BY
                i, r
        ) q
GROUP BY
        i
ORDER BY
        i

WITH    RECURSIVE
        q (r, i, rx, ix, g) AS
        (
        SELECT  r::DOUBLE PRECISION * 0.000001, i::DOUBLE PRECISION * 0.000001,
                r::DOUBLE PRECISION * 0.000001, i::DOUBLE PRECISION * 0.000001,
                0
        FROM    generate_series(-40, 40) r, generate_series(-50, 50) i
        UNION ALL
        SELECT  r, i,
                CASE WHEN ABS(rx * rx + ix * ix) < 1E8 THEN rx * rx - ix * ix END + 0,
                CASE WHEN ABS(rx * rx + ix * ix) < 1E8 THEN 2 * rx * ix END + 1,
                g + 1
        FROM    q
        WHERE   rx IS NOT NULL
                AND g < 99
        )
SELECT  ARRAY_TO_STRING(ARRAY_AGG(s ORDER BY r), '')
FROM    (
        SELECT  i, r, SUBSTRING(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
        FROM    q
        GROUP BY
                i, r
        ) q
GROUP BY
        i
ORDER BY
        i

WITH    RECURSIVE
        q (r, i, rx, ix, g) AS
        (
        SELECT  r::DOUBLE PRECISION * 0.0002, i::DOUBLE PRECISION * 0.0002,
                r::DOUBLE PRECISION * 0.0002, i::DOUBLE PRECISION * 0.0002,
                0
        FROM    generate_series(-200, -120) r, generate_series(0, 100) i
        UNION ALL
        SELECT  r, i,
                CASE WHEN ABS(rx * rx + ix * ix) < 1E8 THEN rx * rx - ix * ix END - 0.70176,
                CASE WHEN ABS(rx * rx + ix * ix) < 1E8 THEN 2 * rx * ix END + 0.3842,
                g + 1
        FROM    q
        WHERE   rx IS NOT NULL
                AND g < 99
        )
SELECT  ARRAY_TO_STRING(ARRAY_AGG(s ORDER BY r), '')
FROM    (
        SELECT  i, r, SUBSTRING(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
        FROM    q
        GROUP BY
                i, r
        ) q
GROUP BY
        i
ORDER BY
        i

WITH    RECURSIVE
        q (r, i, rx, ix, g) AS
        (
        SELECT  x + r::DOUBLE PRECISION * step, y + i::DOUBLE PRECISION * step,
                x + r::DOUBLE PRECISION * step, y + i::DOUBLE PRECISION * step,
                0
        FROM    (
                SELECT  0.25 x, -0.55 y, 0.002 step, r, i
                FROM    generate_series(-40, 40) r
                CROSS JOIN
                        generate_series(-40, 40) i
                ) q
        UNION ALL
        SELECT  r, i,
                CASE WHEN (rx * rx + ix * ix) < 1E8 THEN (rx * rx + ix * ix) ^ 0.75 * COS(1.5 * ATAN2(ix, rx)) END - 0.2,
                CASE WHEN (rx * rx + ix * ix) < 1E8 THEN (rx * rx + ix * ix) ^ 0.75 * SIN(1.5 * ATAN2(ix, rx)) END,
                g + 1
        FROM    q
        WHERE   rx IS NOT NULL
                AND g < 99
        )
SELECT  ARRAY_TO_STRING(ARRAY_AGG(s ORDER BY r), '')
FROM    (
        SELECT  i, r, SUBSTRING(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
        FROM    q
        GROUP BY
                i, r
        ) q
GROUP BY
        i
ORDER BY
        i

// Make sure to add code blocks to your code group

窗口函数 rowsbetween、rangebetween 的使用

-- 待补充

SELECT CODE
,TO_CHAR(WM_CONCAT(CODE) OVER(PARTITION BY LEVELNO ORDER BY CODE )) AS test1
--,TO_CHAR(WM_CONCAT(CODE) OVER(PARTITION BY LEVELNO ORDER BY CODE rows between 1 preceding and 3 following)) AS test1   -- 当前之前一行到后面三行
--,TO_CHAR(WM_CONCAT(CODE) OVER(PARTITION BY LEVELNO ORDER BY CODE rows between UNBOUNDED PRECEDING and CURRENT ROW)) AS test2  -- 区间第一行到当前行
,TO_CHAR(WM_CONCAT(CODE) OVER(PARTITION BY LEVELNO ORDER BY CODE rows between CURRENT ROW and UNBOUNDED FOLLOWING)) AS test3  -- 当前行区间最后一行
FROM AD_TABLE

// Make sure to add code blocks to your code group

博客：10个SQL杀手级特性

原文地址 (opens new window)