MySQL學習足跡記錄14–表別名和自聯結
本查詢所用到的表：
下面的表num代表公共汽車路線，如1路車，2路車，stop帶表停車站點，如A站，B站
表：
route; +------+------+ | num | stop | +------+------+ | 1 | A | | 1 | B | | 1 | C | | 2 | B | | 2 | C | | 2 | D | +------+------+
準備知識
1.使用表別名
*表別名隻在查詢執行中使用
*表別名不返回到客戶機
Example:
mysql> SELECT * from route AS r1
-> WHERE r1.num = 1;
+------+------+
| num | stop |
+------+------+
| 1 | A |
| 1 | B |
| 1 | C |
+------+------+
3 rows in set (0.00 sec)
解析：
相信理解表別名應該不費力，類似與列別名，表別名隻是給表取另外一個名字而已，代表的還是相同的表
2.自聯結
*自聯結通常作為外部語句來代替從相同表中檢索數據時使用的子查詢語句
*使用表別名能在單條語句中多次使用相同的表
下面給出一個簡單的例子幫助理解自聯結的原理
Example:
下面的語句查詢的結果是共用同一車站的所有公交線
mysql> SELECT DISTINCT r2.num,r2.stop
-> FROM route AS r1,route AS r2
-> WHERE r1.stop = r2.stop
-> ORDER BY r2.stop;
+------+------+
| num | stop |
+------+------+
| 1 | A |
| 1 | B |
| 2 | B |
| 1 | C |
| 2 | C |
| 2 | D |
+------+------+
6 rows in set (0.00 sec)
解析：
第一句
mysql> SELECT DISTINCT r2.num,r2.stop
-> FROM route AS r1,route AS r2；
為瞭詳解，這裡先去除DISTINCT關鍵字，並隻截取下面的子句
mysql> select * From route AS r1,route r2; +------+------+------+------+ | num | stop | num | stop | +------+------+------+------+ | 1 | A | 1 | A | | 1 | B | 1 | A | | 1 | C | 1 | A | | 2 | B | 1 | A | | 2 | C | 1 | A | | 2 | D | 1 | A | | 1 | A | 1 | B | | 1 | B | 1 | B | | 1 | C | 1 | B | | 2 | B | 1 | B | | 2 | C | 1 | B | | 2 | D | 1 | B | | 1 | A | 1 | C | | 1 | B | 1 | C | | 1 | C | 1 | C | | 2 | B | 1 | C | | 2 | C | 1 | C | | 2 | D | 1 | C | | 1 | A | 2 | B | | 1 | B | 2 | B | | 1 | C | 2 | B | | 2 | B | 2 | B | | 2 | C | 2 | B | | 2 | D | 2 | B | | 1 | A | 2 | C | | 1 | B | 2 | C | | 1 | C | 2 | C | | 2 | B | 2 | C | | 2 | C | 2 | C | | 2 | D | 2 | C | | 1 | A | 2 | D | | 1 | B | 2 | D | | 1 | C | 2 | D | | 2 | B | 2 | D | | 2 | C | 2 | D | | 2 | D | 2 | D | +------+------+------+------+ 36 rows in set (0.00 sec)
從上面的查詢結果中可以看出，共有36條記錄（剛好是兩張表的笛卡爾積，關於笛卡爾積，請點擊MySQL學習足跡記錄13–聯結表），
可一推測，所謂的自聯結就是把同一張表，看成獨立的，不同的兩張表r1,r2
我們的目的是查詢共用同一車站的所有公交線，所以從上表的結果集中再添加篩選條件（令車站相等）：
r1.stop = r2.stop
mysql> SELECT * FROM route AS r1,route AS r2
-> WHERE r1.stop = r2.stop；
+------+------+------+------+
| num | stop | num | stop |
+------+------+------+------+
| 1 | A | 1 | A |
| 1 | B | 1 | B |
| 2 | B | 1 | B |
| 1 | C | 1 | C |
| 2 | C | 1 | C |
| 1 | B | 2 | B |
| 2 | B | 2 | B |
| 1 | C | 2 | C |
| 2 | C | 2 | C |
| 2 | D | 2 | D |
+------+------+------+------+
10 rows in set (0.00 sec)
這已經很接近所需的結果瞭，但我們隻需num,stop兩列，再添加
SELECT r2.num,r2.stop （SELECT r1.num,r1.stop也OK）
mysql> SELECT r2.num,r2.stop
-> FROM route AS r1,route r2
-> WHERE r1.stop = r2.stop;
+------+------+
| num | stop |
+------+------+
| 1 | A |
| 1 | B |
| 1 | B |
| 1 | C |
| 1 | C |
| 2 | B |
| 2 | B |
| 2 | C |
| 2 | C |
| 2 | D |
+------+------+
10 rows in set (0.00 sec)
最後去除相同的記錄，再按車站排序
mysql> SELECT DISTINCT r2.num,r2.stop
-> FROM route AS r1,route AS r2
-> WHERE r1.stop = r2.stop
-> ORDER BY r2.stop;
+------+------+
| num | stop |
+------+------+
| 1 | A |
| 1 | B |
| 2 | B |
| 1 | C |
| 2 | C |
| 2 | D |
+------+------+
6 rows in set (0.00 sec)
從結果集中可以看出1路車和2路 車共用B，C車站