Mysql 简明教程
MySQL - Self Join
MySQL Self Join
MySQL Self Join 用于将一个表连接到它自身，就像该表是两个表一样。为了执行此操作，至少在一个表在 MySQL 语句中临时重命名。
The MySQL Self Join is used to join a table to itself as if the table were two tables. To carry this out, at least one table is temporarily renamed in the MySQL statement.
自连接是一种内部连接，当需要比较同一张表的两列时执行此操作；可能需要在它们之间建立关系。换句话说，当一张表同时包含 Foreign Key 和 Primary Key 时，它会与自身连接。
Self Join is a type of inner join, which performed in cases where the comparison between two columns of a same table is required; probably to establish a relationship between them. In other words, a table is joined with itself when it contains both Foreign Key and Primary Key in it.
但是，与其他连接的查询不同，我们使用 WHERE 子句指定表与其自身合并的条件；而不是使用 ON 子句。
However, unlike queries of other joins, we use WHERE clause to specify the condition for the table to combine with itself; instead of the ON clause.
Syntax
以下是 MySQL 中自连接的基本语法−
Following is the basic syntax of Self Join in MySQL −
SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;这里，WHERE 子句可以是基于您要求的任何给定表达式。
Here, the WHERE clause could be any given expression based on your requirement.
Example
自连接只需要一张表连接到自身；因此，让我们创建一个 CUSTOMERS 表，其中包含客户详细信息，如姓名、年龄、地址和他们获得的薪水。
Self Join only requires one table to join itself; so, let us create a CUSTOMERS table containing the customer details like their names, age, address and the salary they earn.
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 语句向该表中插入值，如下所示：
Now insert values into this table using the INSERT statement as follows −
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 );该表将被创建为：
The table will be created as −
现在，让我们使用以下 Self Join query 连接该表。我们的目标是根据客户的收入建立一个关系。我们使用 WHERE 子句执行此操作。
Now, let us join this table using the following Self Join query. Our aim is to establish a relationship among the said customers on the basis of their earnings. We are doing this with the help of WHERE clause.
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;Self Join with ORDER BY Clause
此外，在使用自连接将表与其自身连接之后，还可以使用 ORDER BY 子句按升序对合并表中的记录进行排序。以下是语法−
Furthermore, after joining a table with itself using self join, the records in the combined table can also be sorted in an ascending order using the ORDER BY clause. Following is the syntax for it −
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 子句按指定列对记录进行升序排列。在此，我们根据薪水列排列记录
In this example, executing the query below will join the CUSTOMERS table with itself using self join on a WHERE clause. Then, arrange the records in an ascending order using the ORDER BY clause with respect to a specified column. Here, we are arranging the records based on the salary column
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;