sql - 这个递归 SQL CTE 究竟是如何工作的？

Question

有人可以向我解释一下这个 SQL 查询是如何工作的吗？

WITH recursive n(n) AS (
  SELECT 2 n
  UNION ALL
  SELECT n+1 FROM n WHERE n<1000
)
SELECT a.n
FROM n a
  LEFT JOIN n b
  ON b.n < sqrt(a.n)
GROUP BY a.n
HAVING a.n=2 OR MIN(a.n % b.n) > 0;

这将在 PostgresQL 中生成以下内容:

 n
====
251
887
601
647
577
...
9
(177 rows)

我对逐行分割的理解:

SELECT 2 n -- 选择数字2作为n[CTE的锚定成员]

UNION ALL -- 与 n<1000 的递归分量 n+1 结合，因此显示从 2 到 1000 的所有数字

SELECT a.n FROM n a -- 运行上面的查询[CTE的递归成员]

LEFT JOIN n b -- 左连接数字 2-1000 和第二组数字

ON b.n < sqrt(a.n) -- 第二组数字小于第一列数字的平方根？

GROUP BY a.n -- 只显示第一列数字

HAVING a.n=2 OR MIN(a.n. % b.n) > 0 -- ...其中 A=2 或 A 模 B 的最小值大于 0？

这是一个愚蠢的查询，但如果能帮助破译它，我们将不胜感激。

Answer 1

您的查询在正确修复后会生成一个小于 1000 的素数列表:

WITH recursive n(n) AS (
  SELECT 2 n
  UNION ALL
  SELECT n+1 FROM n WHERE n<1000
)
SELECT a.n
FROM n a
  LEFT JOIN n b
  ON b.n <= sqrt(a.n)                        -- Fix #1
GROUP BY a.n
HAVING a.n=2 OR a.n=3 OR MIN(a.n % b.n) > 0  -- Fix #2
ORDER BY a.n ASC

Demo.

解释相当简单:查询的递归部分只是一种为您提供从 2(含)到 1000(不含)的数字列表的方法。您可以将递归子句替换为填充有连续整数的实际表。

然后将这些数字输入到查询的非 C​​TE 部分，并在条件 b.n < sqrt(a.n) 上连接到它们自己. a边代表候选素数； b边代表候选除数。

这是您查询中的第一个错误:<必须改为 <= , 否则素数平方的平方根将包含在输出中。

GROUP BY将潜在素数及其候选约数分组为一个组。 HAVING子句用一个或多个候选除数将候选素数平均除以所有内容，即 MIN(a.n % b.n)为零。

这是您需要第二次修复的地方，因为 3 的平方根, 一个素数，小于 2 ，列表中最小的候选除数。因此，3最终完全没有候选除数，并被 HAVING 抛出条款;你需要添加 OR a.n=3以保护它。