計算重複紀錄

1. 查詢資料庫 dyw 有那些資料表。

   MariaDB root@(none):dyw> show tables;
   +----------------+
   | Tables_in_dyw  |
   +----------------+
   | animals        |
   | clone1_animals |
   | clone_animals  |
   | dcount         |
   | employees      |
   | indextab       |
   | person         |
   | tbl            |
   +----------------+
   
   8 rows in set
   Time: 0.035s
   

2. 查詢資料表 animals 所有紀錄。

   MariaDB root@(none):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.028s
   

3. 新增一筆重複的紀錄。

   MariaDB root@(none):dyw> insert into animals(name,foot) values ('cat',4);
   Query OK, 1 row affected
   Time: 0.010s
   

4. 查詢資料表 animals 的紀錄，cat 有兩筆，但 id 不同。

   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.021s
   

5. 使用 SELECT COUNT(*) ... GROUP BY ... HAVING ... 語法計算重複紀錄。

   MariaDB root@(none):dyw> select count(*) as repetitions,name,foot from animals gr
                         -> oup by name,foot having repetitions>1;
   +-------------+------+------+
   | repetitions | name | foot |
   +-------------+------+------+
   | 2           | cat  | 4    |
   +-------------+------+------+
   
   1 row in set
   Time: 0.040s