不重複輸出重複紀錄

1. 使用 DISTINCT 語法讓重複紀錄只輸出一筆。

   MariaDB root@(none):dyw> select distinct name,foot from animals order by name;
   +---------+------+
   | name    | foot |
   +---------+------+
   | bird    | 2    |
   | cat     | 4    |
   | chicken | 2    |
   | dog     | 4    |
   +---------+------+
   
   4 rows in set
   Time: 0.025s
   

2. 使用 SELECT ... GROUP BY ... 語法可以自行組合重複紀錄判斷欄位。

   MariaDB root@(none):dyw> select name,foot from animals group by name,foot;
   +---------+------+
   | name    | foot |
   +---------+------+
   | bird    | 2    |
   | cat     | 4    |
   | chicken | 2    |
   | dog     | 4    |
   +---------+------+
   
   4 rows in set
   Time: 0.020s
   

3. 以下比較 DISTINCT 與 SELECT ... GROUP BY ... 語法之差異。
4. 先輸出目前資料表的內容有五筆紀錄。

   MariaDB root@(none):dyw> select * from animals;
   +----+---------+------+
   | id | name    | foot |
   +----+---------+------+
   | 1  | dog     | 4    |
   | 2  | cat     | 4    |
   | 3  | chicken | 2    |
   | 15 | bird    | 2    |
   | 16 | cat     | 4    |
   +----+---------+------+
   
   5 rows in set
   Time: 0.023s
   

5. DISTINCT 語法指定「組合的欄位」只輸出一筆，也輸出「組合的欄位」，無法輸出其他欄位。

   MariaDB root@(none):dyw> select distinct foot from animals;
   +------+
   | foot |
   +------+
   | 4    |
   | 2    |
   +------+
   
   2 rows in set
   Time: 0.023s
   

6. DISTINCT name,foot 表示欄位 name+foot 組合不重複，只輸出欄位 name 及 foot。

   MariaDB root@(none):dyw> select distinct name,foot from animals;
   +---------+------+
   | name    | foot |
   +---------+------+
   | dog     | 4    |
   | cat     | 4    |
   | chicken | 2    |
   | bird    | 2    |
   +---------+------+
   
   4 rows in set
   Time: 0.022s
   

7. 若要指定某一欄位不重複，但輸出所有欄位的內容，就必須使用 SELECT ... GROUP BY ... 語法，select * 輸出所有欄位內容，group by foot 指定欄位 foot 成群組，也就是只輸出一筆。

   MariaDB root@(none):dyw> select * from animals group by foot;
   +----+---------+------+
   | id | name    | foot |
   +----+---------+------+
   | 3  | chicken | 2    |
   | 1  | dog     | 4    |
   +----+---------+------+
   
   2 rows in set
   Time: 0.020s