sql - 带有 JOIN 元素的递归链？

Question

我最近要了一个 recursive query在 PostgreSQL 中。答案很合适，但是一旦我添加 JOIN 元素，就会出现以下错误:

ERROR: missing FROM-clause entry for table "n": WITH RECURSIVE chain AS ( SELECT n.pordnr, pz.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion FROM pord n LEFT JOIN pordnrzu pz ON pz.pordnr = n.pordnr WHERE n.pordnr = 112 UNION ALL SELECT n.pordnr, pz2.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion FROM chain c LEFT JOIN pordnrzu pz2 ON pz2.pordnr = n.pordnr INNER JOIN pord n ON (c.pordnrzu = n.pordnr) WHERE c.abschl IS NULL ) SELECT * FROM chain c WHERE c.abschl IS NOT NULL

我尝试了一些修改，但我无法让它工作。我只想实现 pordnrzu 以寻找另一个 pordnr。

所以:pordnr -> pordnrzu -> pordnr

我刚刚错过了什么吗？

这是示例表:

CREATE TABLE pord (
  pordnr integer primary key,
  abschl text,
  stg text,
  kzfa text,
  pversion text
);

INSERT INTO pord (pordnr, abschl, stg, kzfa, pversion)
VALUES
(112,     NULL, NULL, NULL, NULL),
(140,     NULL, NULL, NULL, NULL),
(200,     NULL, NULL, NULL, NULL),
(210,     'f2', '140', 'H', '2011'),
(220,     'f2222', '140000', 'HHH', '201111');

CREATE TABLE pordnrzu (
  pordnr integer primary key,
  pordnrzu integer
);

INSERT INTO pordnrzu (pordnr, pordnrzu)
VALUES
(112,     140),
(140,     210),
(200,     220),
(210,     220),
(220,     NULL);

现在这是我的查询:

WITH RECURSIVE chain AS
(
  SELECT n.pordnr, pz.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion
  FROM pord n
  LEFT JOIN pordnrzu pz
  ON pz.pordnr = n.pordnr
  WHERE n.pordnr = 112
  UNION ALL
  SELECT n.pordnr, pz2.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion
  FROM chain c
  LEFT JOIN pordnrzu pz2
  ON pz2.pordnr = n.pordnr
  INNER JOIN pord n ON (c.pordnrzu = n.pordnr)
  WHERE c.abschl IS NULL
)
SELECT *
FROM chain c
WHERE c.abschl IS NOT NULL;

我的目标是从 pordnr=112 开始，然后将每个 pordnrzu 引导到其他 pordnr，直到找到第一个条目abschl NOT NULL

在这个例子中，解决方案是这一行:

pordnr   pordnrzu   abschl   stg   kzfa   pversion
200      210        f2       140   H      2011

---

SELECT version();

PostgreSQL 9.2.6 on x86_64-unknown-linux-gnu, compiled by gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit

Answer 1

JOIN 子句按从左到右 计算。向上移动第二个连接以使其工作:

WITH RECURSIVE chain AS (
  SELECT n.pordnr, pz.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion
  FROM   pord n
  LEFT   JOIN pordnrzu pz ON pz.pordnr = n.pordnr
  WHERE  n.pordnr = 112

  UNION ALL
  SELECT n.pordnr, pz.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion
  FROM   chain c
  JOIN   pord  n ON n.pordnr = c.pordnrzu
  LEFT   JOIN pordnrzu pz ON pz.pordnr = n.pordnr
  WHERE  c.abschl IS NULL
)
SELECT *
FROM   chain c
WHERE  c.abschl IS NOT NULL;

或者，您可以在第二个 SELECT 中重写第一个 JOIN:

LEFT   JOIN pordnrzu pz ON pz.pordnr = <b>c.pordnrzu</b>