SQL - 自连接

---

---

自连接顾名思义，是一种将表中的记录与其自身结合的连接类型。

假设一个组织在组织圣诞派对时，根据一些颜色在员工中选择秘密圣诞老人。它的设计方法是为每个员工分配一种颜色，并让他们从各种颜色的池中选择一种颜色。最后，他们将成为分配此颜色的员工的秘密圣诞老人。

如下图所示，分配的颜色和每个员工选择的颜色信息都输入到一个表中。该表使用自连接通过颜色列连接自身，以将员工与其秘密圣诞老人匹配。

SQL 自连接

SQL 自连接用于将一个表连接到自身，就好像该表是两个表一样。为了执行此操作，至少应使用一次表的别名。

自连接是一种内连接，当需要比较同一表的两个列时执行；可能是在它们之间建立关系。换句话说，当表中同时包含外键和主键时，该表会与自身连接。

与其他连接的查询不同，我们使用 WHERE 子句指定表与自身结合的条件；而不是 ON 子句。

语法

以下是 SQL 自连接的基本语法：

SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

这里，WHERE 子句可以是基于您的需求的任何给定表达式。

示例

自连接只需要一个表，所以，让我们创建一个包含客户详细信息（如姓名、年龄、地址和他们赚取的工资）的 CUSTOMERS 表。

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

现在，使用 INSERT 语句如下插入值到此表中：

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

该表将被创建为：

现在，让我们使用以下自连接查询连接此表。我们的目标是根据这些客户的收入在他们之间建立关系。我们正在使用 WHERE 子句来做到这一点。

SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME 
as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

输出

显示的结果表将列出所有收入低于其他客户的客户：

带有 ORDER BY 子句的自连接

使用自连接将表与自身连接后，还可以使用 ORDER BY 子句按顺序对组合表中的记录进行排序。

语法

以下是它的语法：

SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field
ORDER BY column_name;

示例

让我们使用 WHERE 子句上的自连接将 CUSTOMERS 表与其自身连接；然后，使用 ORDER BY 子句根据指定的列按升序排列记录，如下面的查询所示。

SELECT  a.ID, b.NAME as EARNS_HIGHER, a.NAME 
as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY
ORDER BY a.SALARY;

输出

结果表显示如下：

不仅是工资列，还可以根据姓名的字母顺序、客户 ID 的数字顺序等对记录进行排序。