自连接（Self-JOIN）：一个带有自身的表

有时候你想要遍历的关系并不是两个人之间的关系不同的表，但同一个表的行之间。典型例子：a “类别 → 子类别”层次结构，其中父级和子级都是相同的 categories 表，由 parent_id 链接。

要一起读取一行（子行）和“它的”另一行（父行），您可以使用 自连接：同一个表在FROM中出现两次，有两个不同的别名。

语法

SQL

SELECT colonne
FROM   tabella AS alias_a
JOIN   tabella AS alias_b ON alias_a.foreign_key = alias_b.id;

别名是强制的：如果我们省略 AS a / AS b，PostgreSQL 就没有当我们编写 name 或 id 时，我们可以知道我们指的是两个副本中的哪一个。

SQL

-- Ogni sotto-categoria con il nome della categoria padre:
SELECT child.name  AS subcategory,
       parent.name AS category
FROM   categories AS child
JOIN   categories AS parent ON child.parent_id = parent.id;

用简单的英语阅读：“获取 categories 名为 child 的行，并且对于每一个，找到名为 parent 的 categories 行，其 id 是孩子的 parent_id"。

结果排除了根类别（具有 parent_id = NULL 的类别）因为 INNER JOIN 会跳过不匹配的行。如果你想包括他们，使用 LEFT JOIN。

与LEFT自联“亦留根”

SQL

-- Tutte le categorie, con nome del padre o NULL se sono radice:
SELECT child.name AS category,
       parent.name AS parent
FROM   categories AS child
LEFT JOIN categories AS parent ON child.parent_id = parent.id;

现在您将获得 9 行（数据集中的每个类别），其中包含 parent = NULL 为 3 个根。

通过自连接进行聚合

它与 GROUP BY 结合得很好，可以询问“每个孩子有多少个” 家长有”：

SQL

SELECT p.name AS parent_category,
       COUNT(c.id) AS children
FROM   categories AS p
LEFT JOIN categories AS c ON c.parent_id = p.id
WHERE  p.parent_id IS NULL          -- solo le radici
GROUP BY p.name
ORDER BY p.name;

请注意两个重要细节：

WHERE p.parent_id IS NULL 过滤器在 JOIN 之前：我们只采用根作为“左手表”。
COUNT(c.id) （不是 COUNT(*)）对于没有子项的根返回 0，而 COUNT(*) 将返回 1，因为 LEFT JOIN 行带有空。

自己尝试一下

锻炼#sql.m5.l2.e1

尝试：0加载中...

根据以下类别（与parent_id NOT NULL 相关的类别），请参阅以下类别名称和您的类别名称。 Due colonne：子类别，类别。 Ordina 每个类别 poi 每个子类别。

正在加载编辑器...

显示提示

自加入的条件 è child.parent_id = Parent.id。

3 次尝试后可用的解决方案

复习练习

锻炼#sql.m5.l2.e2

尝试：0加载中...

对于每个类别基数 (parent_id IS NULL)，最重要的是以下类别的名称和编号。 Due colonne：名字，孩子们。奥迪娜的名字。包括 una eventuale radice senza filie con 0。

正在加载编辑器...

显示提示

对类别进行两次 LEFT JOIN（别名 p 表示父级，c 表示子级）。使用 COUNT(c.id)，而不是 COUNT(*)，在没有子项的根上得到 0。

3 次尝试后可用的解决方案