BCC-16 (in Chinese) 計算機概論十六講 MySQL -- grouping and sorting

MySQL 教材：分類與排序

where 子句可以幫我們挑出某些我們所想要的範圍， group by 子句可以讓我們對資料加以分類，稱之為「群組化」。這時統計函數就會分別對各組作用，而不再是將整張資料表的資料。

select zone, MAX(zipid) from ZIP group by zone;

將會列出各縣市最大的郵遞區號。

COUNT() 也是一個函數，功能就是計數，常常配合群組化使用。現在我們利用 group by 子句來統計 address 表中，id 十以內各區的資料數：

select zipid, COUNT(zipid) from address where id <= 10 group by zipid;

+-------+--------------+
| zipid | COUNT(zipid) |
+-------+--------------+
|    51 |            1 |
|    52 |            1 |
|   106 |            1 |
|   127 |            2 |
|   190 |            1 |
|   298 |            1 |
|   331 |            1 |
|   337 |            1 |
|   351 |            1 |
+-------+--------------+

表示 id 在十以內的資料中，zipid 為 127 有兩筆，其他都是各有一筆。

除了 COUNT(), group by 與其他統計函數的配合也是頗常見的。譬如書商想從一張存著交易記錄的資料表中，取得各客戶的平均消費金額，就需使用 group by 及 AVG().

having 子句與 where 效果相當，不過它是用來限制群組化後及聚合過的資料。

select zone, MAX(zip) from ZIP where id <= 100 group by zone having MAX(zip) < 200

這是告訴 MySQL ：僅將符合 where 條件限制 (id <= 100) 的資料取出，集合 group by 所指定欄位 (zone) 有相同值的資料，在每一組中取出 zip 最大的值，然後依照 having 的限制只列出 MAX(zip) 小於兩百的資料。

排序在 MySQL 也是件十分容易的事，只需加上「order by 欄位名稱」。舉個例子：取 address 前十筆的資料並依 zipid 排序─

select * from address where id <= 10 order by zipid

+----+-------+-----------------------+
| id | zipid | addressno             |
+----+-------+-----------------------+
|  7 |    51 | 三民路284號           |
|  3 |    52 | 南港街479號           |
|  5 |   106 | 金山路909巷755號      |
|  8 |   127 | 柳川東路285巷168號1樓 |
| 10 |   127 | 黎明路377號           |
|  4 |   190 | 永平街769號           |
|  2 |   298 | 中平街850號9樓        |
|  9 |   331 | 八德路476號           |
|  1 |   337 | 中港路730號1樓        |
|  6 |   351 | 永福路866巷174弄159號 |
+----+-------+-----------------------+

留意資料的順序。

而在「order by 欄位名稱」後加上 desc, 則以降冪的方式排列。

select * from address where id <= 10 order by zipid desc;

+----+-------+-----------------------+
| id | zipid | addressno             |
+----+-------+-----------------------+
|  6 |   351 | 永福路866巷174弄159號 |
|  1 |   337 | 中港路730號1樓        |
|  9 |   331 | 八德路476號           |
|  2 |   298 | 中平街850號9樓        |
|  4 |   190 | 永平街769號           |
|  8 |   127 | 柳川東路285巷168號1樓 |
| 10 |   127 | 黎明路377號           |
|  5 |   106 | 金山路909巷755號      |
|  3 |    52 | 南港街479號           |
|  7 |    51 | 三民路284號           |
+----+-------+-----------------------+

有一個叫需要注意的觀念是─這些指令都是類似 database() 的函式，不要將它當成一般的欄位名稱，例如 order by count() 是不被 MySQL 接受的。這個問題可以使用「別名」(alias)來解決。

先介紹別名。別名原先的用意是為了取代複雜的資料表名稱。假設有兩張資料表 "NCU_MATH_PINGPONG_member_data", "NCU_MATH_student_data" 想要查詢林姓成員的相關資料, 需要下列指令：

select NCU_MATH_PINGPONG_member_data.name, NCU_MATH_student_data.gender,
NCU_MATH_student_data.number, NCU_MATH_student_data.class,
NCU_MATH_PINGPONG_member_data.group, NCU_MATH_PINGPONG_member_data.type
from NCU_MATH_PINGPONG_member_data join NCU_MATH_student_data
where NCU_MATH_PINGPONG_member_data.acctid = NCU_MATH_student_data.id;

一看就眼花。如果不使用剪貼大法，查三次就開始甩手。

別名可以用在資料表及欄位，只要將在後面加上 "as 別名"。上面的例子可以改寫為

select P.name, S.gender, S.number, S.class, P.group, P.type
from NCU_MATH_PINGPONG_member_data as P join NCU_MATH_student_data as S
where P.acctid = S.id;

是不是好多了？

或許有讀者會想，既然表名長會添麻煩，為何不一開始就取簡單一點就好了呢？這是個好問題！如果我們把所有資料表叫做 "a", "b", "aa", "a0" 等等，資料少的時候或許自己還能記得住，但資料一多，時間一久，或者是非建立表格的人看到名稱，鐵定搞不懂裡頭放了什麼資料。而 NCU_MATH_PINGPONG_member_data 裝什麼，一目了然。

回到剛剛的問題：我們如果很莫名奇妙的想得到依地區分組計算郵地區號的平均，並排序列出，

select zone, AVG(zip) from ZIP group by zone order by AVG(zip);

是不被接受的，不過利用別名將 AVG(zip) 轉為一個欄位後就可以了。

select zone, AVG(zip) as i from ZIP group by zone order by i;

習題


	林勁伍 (02/10/07) --- 03/02/18 (伍)