SQL SELF JOIN 用于将一个表连接到自身,就好像该表是两个表一样,在SQL语句中临时重命名至少一个表。
SELF JOIN的基本语法如下-
SELECT a.column_name, b.column_name... FROM table1 a, table1 b WHERE a.common_field=b.common_field;
请考虑下表。
客户表(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 | Learnfk | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
现在,让无涯教程使用SELF JOIN联接此表,如下所示:
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 | Learnfk | 2000.00 | | 2 | Learnfk | 1500.00 | | 3 | Learnfk | 2000.00 | | 4 | Learnfk | 6500.00 | | 5 | Learnfk | 8500.00 | | 6 | Learnfk | 4500.00 | +----+----------+---------+
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)