可用版本：Dev (3.21) | 最新 (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11

WITH RECURSIVE子句

适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版

各种 SQL 方言对于编写递归公共表表达式时是否使用 RECURSIVE 存在分歧。使用 jOOQ 时，始终使用 DSLContext.withRecursive() 或 DSL.withRecursive() 方法，如果需要，jOOQ 将呈现 RECURSIVE 关键字。

假设有这样一个表

CREATE TABLE directory (
  id           INT NOT NULL,
  parent_id    INT,

  -- In PostgreSQL, use TEXT instead, to work around https://github.com/jOOQ/jOOQ/issues/12067
  label        VARCHAR(50),

  CONSTRAINT pk_directory PRIMARY KEY (id),
  CONSTRAINT fk_directory FOREIGN KEY (parent_id) REFERENCES directory (id)
);

INSERT INTO directory VALUES ( 1, null, 'C:');
INSERT INTO directory VALUES ( 2,    1, 'eclipse');
INSERT INTO directory VALUES ( 3,    2, 'configuration');
INSERT INTO directory VALUES ( 4,    2, 'dropins');
INSERT INTO directory VALUES ( 5,    2, 'features');
INSERT INTO directory VALUES ( 7,    2, 'plugins');
INSERT INTO directory VALUES ( 8,    2, 'readme');
INSERT INTO directory VALUES ( 9,    8, 'readme_eclipse.html');
INSERT INTO directory VALUES (10,    2, 'src');
INSERT INTO directory VALUES (11,    2, 'eclipse.exe');

使用 WITH RECURSIVE，您现在可以按如下方式查询此目录的结构

WITH RECURSIVE t (
  id,
  name,
  path
) AS (
  SELECT
    DIRECTORY.ID,
    DIRECTORY.LABEL,
    DIRECTORY.LABEL
  FROM
    DIRECTORY
  WHERE
    DIRECTORY.PARENT_ID IS NULL
  UNION ALL
  SELECT
    DIRECTORY.ID,
    DIRECTORY.LABEL,
    t.path
      || '\'
      || DIRECTORY.LABEL
  FROM
    t
  JOIN
    DIRECTORY
  ON t.id = DIRECTORY.PARENT_ID
)
SELECT *
FROM
  t;

CommonTableExpression<?> cte = name("t").fields(
  "id",
  "name",
  "path"
).as(
  select(
    DIRECTORY.ID,
    DIRECTORY.LABEL,
    DIRECTORY.LABEL)
  .from(DIRECTORY)
  .where(DIRECTORY.PARENT_ID.isNull())
  .unionAll(
  select(
    DIRECTORY.ID,
    DIRECTORY.LABEL,
    field(name("t", "path"), VARCHAR)
      .concat("\\")
      .concat(DIRECTORY.LABEL))
  .from(table(name("t")))
  .join(DIRECTORY)
  .on(field(name("t", "id"), INTEGER)
    .eq(DIRECTORY.PARENT_ID)))
);

System.out.println(
    create.withRecursive(cte)
          .selectFrom(cte)
          .fetch()
);

输出结果如下

+----+---------------------+---------------------------------------+
| id | name                | path                                  |
+----+---------------------+---------------------------------------+
| 1  | C:                  | C:                                    |
| 2  | eclipse             | C:\eclipse                            |
| 3  | configuration       | C:\eclipse\configuration              |
| 4  | dropins             | C:\eclipse\dropins                    |
| 11 | eclipse.exe         | C:\eclipse\eclipse.exe                |
| 5  | features            | C:\eclipse\features                   |
| 7  | plugins             | C:\eclipse\plugins                    |
| 8  | readme              | C:\eclipse\readme                     |
| 9  | readme_eclipse.html | C:\eclipse\readme\readme_eclipse.html |
| 10 | src                 | C:\eclipse\src                        |
+----+---------------------+---------------------------------------+