SQL - Self Join

您是否认为需要比较同一表格的两栏? 例如,让我们相信,一个组织正在根据一些肤色选择一个秘密的圣塔。 这样做的办法是向每个雇员分配一张彩色,而其他雇员则从各种颜色中选取一个色。 最终,这名雇员将被分配到成为他们的圣殿秘密。

如下图所示,关于所分配的颜色和每名雇员所选取的颜色的资料已输入表格。 该表通过自食颜色栏自行取走圣秘密。

Self Join in SQL

SQL Self Join 如表是两个表格,则表格本身也使用。 为了做到这一点,至少有一个表格暂时改名为KQ。

如果需要对同一表格的两栏进行比较,则自行加入为一类;可能要在两栏之间建立联系。 换言之,如果表内载有Foreign Key和Primary Key,则该表将并入。

然而,与其他缔约方不同的是,我们使用WHERE条款来具体规定表格与自己合并的条件;而不是《国家空间公约》条款。

Syntax

The following is the basic syntax of Self Joinkou in

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

在这里,根据您的要求,WHERE条款可以表达。

Example

单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单单加入;这样,我们就应该制作一个客户表,列出他们的姓名、年龄、地址和工资等客户细节。

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 INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (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 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  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 | Chaitap     | Komal      |      4500.00 |
|  3 | Chaitap     | Kaushik    |      2000.00 |
|  2 | Chaitap     | Khilan     |      1500.00 |
|  1 | Chaitap     | Ramesh     |      2000.00 |
|  6 | Hardik       | Komal      |      4500.00 |
|  4 | Hardik       | Chaitap   |      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        | Chaitap   |      6500.00 |
|  3 | Muffy        | Kaushik    |      2000.00 |
|  2 | Muffy        | Khilan     |      1500.00 |
|  1 | Muffy        | Ramesh     |      2000.00 |
+----+--------------+------------+--------------+

Self Join with ORDER BY Clause

此外,在采用自成表格之后,合并表格中的记录也可以按照逐条排列的顺序排列。

Syntax

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

让我们使用下表所示客户:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

在执行以下询问时,我们将自行加入客户表格,加入《WHERE条款》;然后使用按条款对一栏的“ORDER”安排记录。

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 | Chaitap     | Khilan     |      1500.00 |
|  2 | Hardik       | Khilan     |      1500.00 |
|  2 | Komal        | Khilan     |      1500.00 |
|  2 | Muffy        | Khilan     |      1500.00 |
|  3 | Chaitap     | Kaushik    |      2000.00 |
|  1 | Chaitap     | 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 | Chaitap     | Komal      |      4500.00 |
|  6 | Hardik       | Komal      |      4500.00 |
|  6 | Muffy        | Komal      |      4500.00 |
|  4 | Hardik       | Chaitap   |      6500.00 |
|  4 | Muffy        | Chaitap   |      6500.00 |
|  5 | Muffy        | Hardik     |      8500.00 |
+----+--------------+------------+--------------+

不仅薪金栏,记录可以按姓名的字母顺序、客户身份号码等分类。