SQL SELF JOIN：自连接
自连接通常用于将表的某个字段与该表的同一字段的其它值进行比较。
语法
SELF JOIN 的基本语法如下：
SELECT a.column1, b.column1...
FROM table1 AS a, table1 AS b
WHERE a.common_column < 网站站点" rel="nofollow" />
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
现在，让我们使用 SELF JOIN 来比较 SALARY 字段，找到比某个客户工资低的其它所有客户，如下所示：
SQL> SELECT a.ID, a.NAME, a.SALARY, b.NAME AS THIS_NAME, b.SALARY AS THIS_SALARY
FROM CUSTOMER AS a, CUSTOMER AS b
WHERE a.SALARY < b.SALARY;
执行结果如下：
+----+----------+--------+-----------+-------------+
| ID | NAME | SALARY | THIS_NAME | THIS_SALARY |
+----+----------+--------+-----------+-------------+
| 2 | Khilan | 1500 | Ramesh | 2000 |
| 2 | Khilan | 1500 | Kaushik | 2000 |
| 1 | Ramesh | 2000 | Chaitali | 6500 |
| 2 | Khilan | 1500 | Chaitali | 6500 |
| 3 | Kaushik | 2000 | Chaitali | 6500 |
| 6 | Komal | 4500 | Chaitali | 6500 |
| 1 | Ramesh | 2000 | Hardik | 8500 |
| 2 | Khilan | 1500 | Hardik | 8500 |
| 3 | Kaushik | 2000 | Hardik | 8500 |
| 4 | Chaitali | 6500 | Hardik | 8500 |
| 6 | Komal | 4500 | Hardik | 8500 |
| 1 | Ramesh | 2000 | Komal | 4500 |
| 2 | Khilan | 1500 | Komal | 4500 |
| 3 | Kaushik | 2000 | Komal | 4500 |
| 1 | Ramesh | 2000 | Muffy | 10000 |
| 2 | Khilan | 1500 | Muffy | 10000 |
| 3 | Kaushik | 2000 | Muffy | 10000 |
| 4 | Chaitali | 6500 | Muffy | 10000 |
| 5 | Hardik | 8500 | Muffy | 10000 |
| 6 | Komal | 4500 | Muffy | 10000 |
+----+----------+--------+-----------+-------------+
从执行结果可以发现，SELF JOIN 以右表为主，它先将左表中的每一行与右表中的行进行比较，然后再将左表中的行与右表中的第二行进行比较，以此类推，直到右表的一行。
示例2
现在有如下所示的员工表，名字为 employee：
+----+----------+-----+--------+------------+
| id | name | sex | salary | manager_id |
+----+----------+-----+--------+------------+
| 1 | joe | m | 7000 | 3 |
| 2 | henry | m | 8000 | 4 |
| 3 | sam | f | 6000 | NULL |
| 4 | max | m | 9000 | NULL |
| 5 | ramesh | m | 7500 | 3 |
| 6 | khilan | f | 11500 | 4 |
| 7 | Chaitali | m | 8600 | 4 |
| 8 | Komal | f | 7800 | 4 |
+----+----------+-----+--------+------------+
现在，让我们使用 SELF JOIN 找出工资超过各自经理的员工姓名：
SELECT e1.name AS employee_name, e1.salary, e2.name AS manager_name, e2.salary
FROM employee AS e1, employee AS e2
WHERE e1.manager_id = e2.id
AND e1.salary > e2.salary;
运行结果：
+---------------+--------+--------------+--------+
| employee_name | salary | manager_name | salary |
+---------------+--------+--------------+--------+
| joe | 7000 | sam | 6000 |
| ramesh | 7500 | sam | 6000 |
| khilan | 11500 | max | 9000 |
+---------------+--------+--------------+--------+
示例3
现在有如下所示的用户表，名字为 user：
+----+------------+-----------------+--------------------------+-------------+-----------------+
| id | username | password | email | cellphone | ip |
+----+------------+-----------------+--------------------------+-------------+-----------------+
| 1 | mozhiyan | 123456 | java@biancheng网站站点" rel="nofollow" />
DELETE u1 FROM user AS u1, user AS u2 WHERE u1.email = u2.email AND u1.id > u2.id;
执行完该语句，user 表的数据如下：
+----+------------+-----------+--------------------------+-------------+-----------------+
| id | username | password | email | cellphone | ip |
+----+------------+-----------+--------------------------+-------------+-----------------+
| 1 | mozhiyan | 123456 | java@biancheng网站站点" rel="nofollow" />
- 随机文章
- 风儿(风儿轻轻的吹)
- 儿童教育文章(儿童教育)
- 光遇花手先祖位置(安卓光遇手花先祖)
- 抖音卡(抖音卡顿怎么解决)
- xboxones(xboxone手柄怎么配对主机)
- 兵马俑(兵马俑介绍和历史背景)
- 陈武简历
- 帆船比赛(帆船比赛视频)
- 海猫鸣泣之时游戏(海猫鸣泣之时游戏在哪玩)
- 韩国媳妇和小雪(韩国媳妇和小雪的父亲工资是多少)
- 儋州市第二中学(儋州市第二中学录取分数线)
- 鬼泣5攻略(鬼泣5攻略第三关怎么跳)
- 地球日主题(2020年世界地球日主题)
- 和柳亚子(和柳亚子先生于田)
- 冰客(冰客果汁)
- yy魔兽(yy魔兽世界)
- 充值卡代理(充值卡代理加盟)
- 拆奶罩
- 郭妮小说(恶魔的法则郭妮小说)
- 东天目山(东天目山景区)
- 杭同(杭同培训中心怎么样)
- 蝙蝠给人类的一封信(蝙蝠给人类的一封信)
- 大松电饭煲(美的大松电饭煲)
- 服饰加盟(服饰加盟店招商)
- 点对点短信息(点对点短信息费是什么意思)
- 观音普门品(观音普门品念诵全文)
- 河北省大运会(河北省大运会时间)
- 哈利波特官网(哈利波特官网在哪里)
- 骇客神条(骇客神条怎么辨别真假)
- 杜星霖(杜星霖图片)