SQL基础分享篇章3

自连接

基础分享篇章2以及自连接的相关内容可以划分为不相关子查询。这一篇补充一下自连接~希望有所帮助

1.定义​

​自连接是指在同一张表中，通过别名创建表的多个虚拟镜像，基于特定字段的关联条件进行连接查询，这里就是可以把这个表想成两个一模一样的表，只不过我们命名的时候需要分开来

例如，员工表中既有员工ID，又有其上级的ID（manager_id），通过自连接可查询员工的上下级关系。

2.语法核心

• 别名区分​​：为同一表赋予不同别名（如e1、e2），避免字段歧义

• ​​连接条件​​：基于表中关联字段（如e1.manager_id = e2.employee_id）建立逻辑关系

3.示例

（1）准备数据

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);
-- 插入顶级管理者（CEO）
INSERT INTO employees (employee_id, name, manager_id) 
VALUES (1, '王五', NULL), 
-- 插入中层管理者（引用王五的ID）
       (2, '李四', 1), 
       (7, '财务总监', 1),  
-- 插入基层员工（引用李四的ID）
       (3, '张三', 2), 
       (4, '赵六', 2), 
       (8, '会计', 7);

(2)员工及其领导信息

SELECT 
    e1.name AS 员工姓名,
    COALESCE(e2.name, '无上级') AS 上级姓名 #如果无领导，是null值，利用函数返回无上级
FROM employees e1  #e1员工表
LEFT JOIN employees e2  #e2领导表
    ON e1.manager_id = e2.employee_id;

（3）难度升级：查询这些人的层级关系

WITH RECURSIVE emp_tree AS (
    SELECT employee_id, NAME, manager_id, 0 AS LEVEL
    FROM employees
    WHERE manager_id IS NULL  -- 起点：顶级管理者
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, et.level + 1
    FROM employees e JOIN emp_tree et 
    ON e.manager_id = et.employee_id
)
SELECT * 
FROM emp_tree 
ORDER BY LEVEL;

基础篇章2与本章示例都是不相关子查询，下一篇带来更进一步的相关子查询~