next up previous contents
Next: 刪除重複紀錄 Up: Duplicates 重複紀錄 Previous: 計算重複紀錄   Contents   DYWANG_HOME

不重複輸出重複紀錄

  1. 使用 DISTINCT 語法讓重複紀錄只輸出一筆。
    mariadb root@localhost: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.030s
    
  2. 使用 SELECT ... GROUP BY ... 語法可以自行組合重複紀錄判斷欄位。
    mariadb root@localhost: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.021s
    
  3. 比較 DISTINCTSELECT ... GROUP BY ... 語法之差異,先輸出目前資料表的內容有三筆紀錄。
    mariadb root@localhost: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
    
  4. DISTINCT 語法指定「組合的欄位」只輸出一筆,也輸出「組合的欄位」,無法輸出其他欄位。
    mariadb root@localhost:dyw> select distinct foot from animals;                  
    +------+
    | foot |
    +------+
    | 4    |
    | 2    |
    +------+
    2 rows in set
    Time: 0.029s
    
  5. DISTINCT name,foot 表示欄位 name+foot 組合不重複,只輸出欄位 name 及 foot。
    mariadb root@localhost:dyw> select distinct name,foot from animals;             
    +---------+------+
    | name    | foot |
    +---------+------+
    | dog     | 4    |
    | cat     | 4    |
    | chicken | 2    |
    | bird    | 2    |
    +---------+------+
    4 rows in set
    Time: 0.022s
    
  6. 若要指定某一欄位不重複,但輸出所有欄位的內容,就必須使用 SELECT ... GROUP BY ... 語法,select * 輸出所有欄位內容,group by foot 指定欄位 foot 成群組,也就是只輸出一筆。
    mariadb root@localhost:dyw> select * from animals group by foot;                
    +----+---------+------+
    | id | name    | foot |
    +----+---------+------+
    | 3  | chicken | 2    |
    | 1  | dog     | 4    |
    +----+---------+------+
    
    2 rows in set
    Time: 0.022s
    



De-Yu Wang 2020-04-07