SQL自連接

SQL自連接被用來聯接表本身以作爲兩個表,暫時重命名,在SQL語句中至少有一個表。

語法

自連接的基本語法如下:

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

在這裏,WHERE子句可以根據您的需要給定的任意表達式。

例子:

考慮下面的兩個表中,(a)CUSTOMERS表如下:

+----+----------+-----+-----------+----------+ | 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 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+

現在,讓我們用自連接這個表,如下所示:

SQL> SELECT a.ID, b.NAME, a.SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

這將產生以下結果:

+----+----------+---------+ | ID | NAME | SALARY | +----+----------+---------+ | 2 | Ramesh | 1500.00 | | 2 | kaushik | 1500.00 | | 1 | Chaitali | 2000.00 | | 2 | Chaitali | 1500.00 | | 3 | Chaitali | 2000.00 | | 6 | Chaitali | 4500.00 | | 1 | Hardik | 2000.00 | | 2 | Hardik | 1500.00 | | 3 | Hardik | 2000.00 | | 4 | Hardik | 6500.00 | | 6 | Hardik | 4500.00 | | 1 | Komal | 2000.00 | | 2 | Komal | 1500.00 | | 3 | Komal | 2000.00 | | 1 | Muffy | 2000.00 | | 2 | Muffy | 1500.00 | | 3 | Muffy | 2000.00 | | 4 | Muffy | 6500.00 | | 5 | Muffy | 8500.00 | | 6 | Muffy | 4500.00 | +----+----------+---------+

0 條評論,你可以發表評論,我們會進行改進
Comment author placeholder