MySQL學習筆記（六）—— MySQL自串連

標籤：products   概念   cts   等等   sql   例子   語句   品質   查詢   

     有的時候我們需要對同一表中的資料進行多次檢索,這個時候我們可以使用之前學習過的子查詢,先查詢出需要的資料,再進行一次檢索。

例如:一張products表,有產品id,供應商id(vend_id),產品名稱等等.  

mysql> select * from products;

+---------+---------+-----------+------------+-----------+

| prod_id | vend_id | prod_name | prod_price | prod_desc |

+---------+---------+-----------+------------+-----------+

|       1 |  550001 | 蘋果      |         30 | NULL      |

|       2 |  550002 | 橘子      |         40 | NULL      |

|       3 |  550003 | 香蕉      |         50 | NULL      |

|       4 |  550004 | 甜梨      |         60 | NULL      |

|       6 |  550001 | 柿子      |         50 | NULL      |

|       7 |  550001 | 椰子      |         44 | NULL      |

+---------+---------+-----------+------------+-----------+

問題:我們瞭解到蘋果這個產品品質存在問題,因此想看下蘋果的供應商的其他產品是否存在問題.

分析:我們需要先擷取蘋果的vend_id,通過這個id擷取其他的產品.

方法一: 使用子查詢

mysql> select * from products where vend_id = (select vend_id from products where prod_name = ‘蘋果‘);

+---------+---------+-----------+------------+-----------+

| prod_id | vend_id | prod_name | prod_price | prod_desc |

+---------+---------+-----------+------------+-----------+

|       1 |  550001 | 蘋果      |         30 | NULL      |

|       6 |  550001 | 柿子      |         50 | NULL      |

|       7 |  550001 | 椰子      |         44 | NULL      |

+---------+---------+-----------+------------+-----------+

3 rows in set (0.00 sec)

方法二:我們使用自連接,將vend_id與蘋果這個產品vend_id相等的產品連接檢索出來.

首先我們先引入一個新概念,表別名.  我們在進行表操作的時候為了方便可以給表起個別名,例如:

select * from products as prod where prod.prod_id = 1;

瞭解了這個我們再看下自連接,還是那個問題,使用自連接:

mysql> select p1.prod_id,p1.prod_name from products as p1,products as p2

    -> where p1.vend_id = p2.vend_id and p2.prod_name = ‘蘋果‘;

+---------+-----------+

| prod_id | prod_name |

+---------+-----------+

|       1 | 蘋果      |

|       6 | 柿子      |

|       7 | 椰子      |

+---------+-----------+

3 rows in set (0.00 sec)

 

分析:我們將products表看成是兩個表,一個表中的資料存放區的是prod_name為蘋果的資料,另一個表中儲存的是其他的水果資料,我們將兩個表的資料進行連接(其實就是連接的products這一張表)

我們再倒著看,先看sql語句:

select p1.prod_id,p1.prod_name from products as p1,products as p2

    where p1.vend_id = p2.vend_id and p2.prod_name = ‘蘋果‘;

 這個語句說明的意思是尋找p1表中的id和name,條件是p1的vend_id和p2的vend_id相等,同時p2的prod_name是蘋果,那就是說先查看name為蘋果的vend_id,把與蘋果vend_id相等的資料找出來.

 

     使用自連接而不是使用子查詢的原因是,自連接的處理速度和效能更優於子查詢.

 

再舉個面試的例子,前幾天剛遇到的:

有一張學產生績表,有學生姓名,分數,科目,找出小明分數大於80分的科目中誰的分數最高.

方法一:

select * from student where student.class = (select student.class from student where student.name = ‘小明‘  and student.score >80 ) order by student.score limit 1;

方法二:

select s1.name,s1.score from student as s1,student as s2 where s1.class = s2.calss and s2.name = ‘小明‘ and s2.score >80;

 

MySQL學習筆記（六）—— MySQL自串連