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车站