SQL Academy · 课时

比较同一张表中的行

Q: 「比较同一张表中的行」课时是免费的吗？

是的 — 「比较同一张表中的行」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

Q: 「比较同一张表中的行」这节课中我会学到什么？

查找配对项和关系 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

Q: 学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。

Q: 「比较同一张表中的行」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

Q: 我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

查找配对项和关系

比较同一张表中的行 是 CoddyKit 上的免费 SQL Academy 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

什么是自连接

自连接是指将一个表与自身连接。这起初听起来很不寻常，但它是比较同一个表中各行的强大技术。

假设有一个employees表，其中每位员工都有一个指向同一个表中另一行的manager_id。自连接可以让您在一次查询中将每位员工与其经理匹配起来。

设置示例表

我们来创建一个简单的employees表，在整节课中使用它。每一行都有一个id、一个name、一个salary，以及一个引用同一个表中另一位员工的manager_id。

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(50),
  salary     INT,
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice',   90000, NULL),
  (2, 'Bob',     70000, 1),
  (3, 'Carol',   65000, 1),
  (4, 'Dave',    55000, 2),
  (5, 'Eve',     60000, 2),
  (6, 'Frank',   48000, 3);

基本自连接语法

要执行自连接，请使用两个不同的别名两次引用同一个表。这些别名的作用就像表的两个独立副本。然后，您需要指定连接两个副本的连接条件。

这里我们将employees别名为e（员工），将m用作经理别名，然后根据e.manager_id = m.id进行匹配。

SELECT
  e.name   AS employee,
  m.name   AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

使用 LEFT JOIN 包含顶层行

普通的INNER JOIN会删除manager_id为NULL的行，这意味着顶层员工（CEO Alice）会被排除在结果之外。

请使用LEFT JOIN，这样每位员工都会出现，即使他们没有经理。对于这些员工，经理列的值将只是NULL。

SELECT
  e.name          AS employee,
  m.name          AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name NULLS LAST, e.name;

查找共享某个属性的配对

自连接还可用于查找共享某个值的行对。例如，您可以查找所有向同一位经理汇报的员工对。

条件a.id < b.id可以防止（Bob，Carol）和（Carol，Bob）这样的重复配对同时出现在结果中。

SELECT
  a.name       AS employee_1,
  b.name       AS employee_2,
  a.manager_id AS shared_manager
FROM employees a
JOIN employees b
  ON a.manager_id = b.manager_id
 AND a.id < b.id;

比较各行之间的薪资

您可以使用自连接比较各行中的数值。下面的查询会找出所有薪资高于其直属经理薪资的员工——这是一个经典的分析检查。

SELECT
  e.name       AS employee,
  e.salary     AS emp_salary,
  m.name       AS manager,
  m.salary     AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

查找没有匹配项的行

将自连接与LEFT JOIN以及WHERE ... IS NULL检查结合使用，可以查找没有匹配对应行的行。这里我们查找没有任何下属向其汇报的员工——也就是层级结构中的叶节点。

SELECT e.name AS employee
FROM employees e
LEFT JOIN employees sub ON sub.manager_id = e.id
WHERE sub.id IS NULL
ORDER BY e.name;

统计直属下属数量

通过将表与自身连接，并按经理一侧分组，您可以统计每位经理拥有多少名直属下属。没有下属的员工也会通过LEFT JOIN包含在结果中。

SELECT
  m.name           AS manager,
  COUNT(e.id)      AS direct_reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

使用 CTE 理清自连接

当自连接查询变得复杂时，将其包装在公用表表达式（CTE）中可以让逻辑更易于阅读。您可以为层级结构定义一个 CTE，然后清晰地查询它。

WITH hierarchy AS (
  SELECT
    e.id         AS emp_id,
    e.name       AS employee,
    e.salary     AS emp_salary,
    m.name       AS manager,
    m.salary     AS mgr_salary
  FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.id
)
SELECT *
FROM hierarchy
WHERE mgr_salary IS NOT NULL
  AND emp_salary > mgr_salary;

在非层级表上使用自连接

自连接并不局限于经理-员工层级结构。下面的示例使用products表查找属于同一分类的所有产品对，这对于推荐引擎或相似性检查很有用。

CREATE TABLE products (
  id       INT PRIMARY KEY,
  name     VARCHAR(50),
  category VARCHAR(30)
);

INSERT INTO products VALUES
  (1, 'Laptop',  'Electronics'),
  (2, 'Phone',   'Electronics'),
  (3, 'Tablet',  'Electronics'),
  (4, 'Shirt',   'Clothing'),
  (5, 'Jeans',   'Clothing');

SELECT
  a.name AS product_1,
  b.name AS product_2,
  a.category
FROM products a
JOIN products b
  ON a.category = b.category
 AND a.id < b.id;

易错点：笛卡尔积和重复项

使用自连接时，两个常见错误是笛卡尔积（完全忘记连接条件）和重复配对（使用a.id <> b.id而不是a.id < b.id）。

请始终验证连接条件是否正确；当您只需要无序配对而不是有序配对时，请使用<而不是<>。

-- Wrong: returns every ordered pair (A,B) AND (B,A)
SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id
                AND a.id <> b.id;

-- Correct: returns each unordered pair once
SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id
                AND a.id < b.id;

快速检查

检验您对自连接以及同一张表中行比较的理解。

课程回顾

在本课中，您学习了如何使用自连接来比较同一张表中的行。

要点：

为表设置两个别名（例如 e 和 m），这样数据库会将它们视为独立的数据源。
只需要获取存在匹配项的行时使用 INNER JOIN；希望同时包含未匹配行时使用 LEFT JOIN。
使用 a.id < b.id 来避免出现重复的无序对。
自连接适用于层级结构（经理/员工）、相似性搜索（同一类别），以及同一张表中的两行必须建立关联的任何比较场景。
CTE 可以让复杂的自连接查询更易于阅读和维护。

常见问题解答