Sql 简明教程

SQL - Self Join

Self Join ，顾名思义，是一种将表的记录与其自身组合的联接类型。

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

正如我们从下图中可以看到的，有关所分配颜色的信息和每位员工选择的颜色的信息被输入到一个表中。该表使用颜色列上的自联接将自己与其他表联接，以将员工与其秘密圣诞老人相匹配。

The SQL Self Join

SQL Self Join 用于将一张表联接到自己，就好像该表是两张表一样。要执行此操作，应至少使用一次表别名。

自联接是一种内部联接类型，在需要比较同一张表的两列的情况下执行；可能要建立它们之间的关系。换句话说，当一张表同时包含 Foreign Key 和 Primary Key 时，它会与自己联接。

Syntax

以下是 SQL 自联接的基本语法−

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

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

Example

自联接只需要一张表，所以，让我们创建一个 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 );

该表将被创建为：

ID	NAME	AGE	ADDRESS	SALARY
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;

Output

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

ID	EARNS_HIGHER	EARNS_LESS	LOWER_SALARY
2	Ramesh	Khilan	1500.00
2	Kaushik	Khilan	1500.00
6	Chaitali	Komal	4500.00
3	Chaitali	Kaushik	2000.00
2	Chaitali	Khilan	1500.00
1	Chaitali	Ramesh	2000.00
6	Hardik	Komal	4500.00
4	Hardik	Chaitali	6500.00
3	Hardik	Kaushik	2000.00
2	Hardik	Khilan	1500.00
1	Hardik	Ramesh	2000.00
3	Komal	Kaushik	2000.00
2	Komal	Khilan	1500.00
1	Komal	Ramesh	2000.00
6	Muffy	Komal	4500.00
5	Muffy	Hardik	8500.00
4	Muffy	Chaitali	6500.00
3	Muffy	Kaushik	2000.00
2	Muffy	Khilan	1500.00
1	Muffy	Ramesh	2000.00

Self Join with ORDER BY Clause

在使用自联接将一张表与其自身联接后，也可以使用 ORDER BY 子句按顺序对合并表中的记录进行排序。

Syntax

以下为此提供的语法−

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

Example

让我们使用 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;

Output

最终表如下所示：

ID	EARNS_HIGHER	EARNS_LESS	LOWER_SALARY
2	Ramesh	Khilan	1500.00
2	Kaushik	Khilan	1500.00
2	Chaitali	Khilan	1500.00
2	Hardik	Khilan	1500.00
2	Komal	Khilan	1500.00
2	Muffy	Khilan	1500.00
3	Chaitali	Kaushik	2000.00
1	Chaitali	Ramesh	2000.00
3	Hardik	Kaushik	2000.00
1	Hardik	Ramesh	2000.00
3	Komal	Kaushik	2000.00
1	Komal	Ramesh	2000.00
3	Muffy	Kaushik	2000.00
1	Muffy	Ramesh	2000.00
6	Chaitali	Komal	4500.00
6	Hardik	Komal	4500.00
6	Muffy	Komal	4500.00
4	Hardik	Chaitali	6500.00
4	Muffy	Chaitali	6500.00
5	Muffy	Hardik	8500.00

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