計算重複紀錄

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