next up previous contents
Next: 不重複輸出重複紀錄 Up: Duplicates 重複紀錄 Previous: 避免重複紀錄   Contents   DYWANG_HOME

計算重複紀錄

  1. 查詢資料庫 dyw 有那些資料表。
    mariadb root@localhost:dyw> show tables;                                        
    +----------------+
    | Tables_in_dyw  |
    +----------------+
    | animals        |
    | clone1_animals |
    | clone_animals  |
    | dcount         |
    | employees      |
    | indextab       |
    | person         |
    | tbl            |
    +----------------+
    
    8 rows in set
    Time: 0.030s
    
  2. 查詢資料表 animals 所有紀錄。
    mariadb root@localhost:dyw> select * from animals;                              
    +----+---------+------+
    | id | name    | foot |
    +----+---------+------+
    | 1  | dog     | 4    |
    | 2  | cat     | 4    |
    | 3  | chicken | 2    |
    | 15 | bird    | 2    |
    +----+---------+------+
    
    4 rows in set
    Time: 0.029s
    
  3. 新增一筆重複的紀錄。
    mariadb root@localhost:dyw> insert into animals(name,foot) values ('cat',4);    
    Query OK, 1 row affected
    Time: 0.048s
    
  4. 查詢資料表 animals 的紀錄,cat 有兩筆,但 id 不同。
    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.027s
    
  5. 使用 SELECT COUNT(*) ... GROUP BY ... HAVING ... 語法計算重複紀錄。
    mariadb root@localhost:dyw> select count(*) as repetitions,name,foot from animal
                                s group by name,foot having repetitions>1;          
    +-------------+------+------+
    | repetitions | name | foot |
    +-------------+------+------+
    | 2           | cat  | 4    |
    +-------------+------+------+
    
    1 row in set
    Time: 0.058s
    



De-Yu Wang 2020-04-07