SQLの重複についてまとめています。
SQLで重複データを除外・取得する方法
SQLで重複データを除外してデータを取得する
重複データを除外するにはdictinstを使います。dept_manager（部署マネージャ、部長）テーブルは、部長の社員番号（emp_no）、dept_no（部署番号）、有効期限の開始日（from_date）、終了日（to_date）を格納するテーブルです。
部長が変更になっても過去データは削除せずに履歴のように残しておくため、dept_noだけを見ると重複しています。
mysql> select * from dept_manager limit 10; +--------+---------+------------+------------+ | emp_no | dept_no | from_date | to_date | +--------+---------+------------+------------+ | 110022 | d001 | 1985-01-01 | 1991-10-01 | | 110039 | d001 | 1991-10-01 | 9999-01-01 | | 110085 | d002 | 1985-01-01 | 1989-12-17 | | 110114 | d002 | 1989-12-17 | 9999-01-01 | | 110183 | d003 | 1985-01-01 | 1992-03-21 | | 110228 | d003 | 1992-03-21 | 9999-01-01 | | 110303 | d004 | 1985-01-01 | 1988-09-09 | | 110344 | d004 | 1988-09-09 | 1992-08-02 | | 110386 | d004 | 1992-08-02 | 1996-08-30 | | 110420 | d004 | 1996-08-30 | 9999-01-01 | +--------+---------+------------+------------+ 10 rows in set (0.04 sec)
重複したdept_noを除外してデータを抽出するには、distinctを使います。
mysql> select distinct dept_no from dept_manager; +---------+ | dept_no | +---------+ | d001 | | d002 | | d003 | | d004 | | d005 | | d006 | | d007 | | d008 | | d009 | +---------+ 9 rows in set (0.01 sec)
【関連記事】
▶SQL distinctのサンプルコード集 group byよりも700倍速い？
SQLで重複しているデータをすべて抽出する
重複しているデータをすべて抽出するには、GROUP BYで重複データをグルーピングし、HAVINGで同じデータが一件以上存在するデータを抽出します。以下は、誕生日（birth_date）と姓名（名=first_name、姓＝last_name）がすべて同一の社員を抽出するSQLです。
SELECT birth_date,
first_name,
last_name
FROM employees
GROUP BY birth_date,
first_name,
last_name
HAVING Count(birth_date) > 1;
+------------+------------+-----------+
| birth_date | first_name | last_name |
+------------+------------+-----------+
| 1963-12-08 | Sarita | Ghalwash |
| 1963-07-19 | Holgard | Negoita |
| 1958-05-12 | Pragnesh | Acton |
| 1963-07-10 | Shim | Gide |
| 1963-05-07 | Tonny | Cromarty |
| 1956-11-25 | Pintsang | Granlund |
+------------+------------+-----------+
6 rows in set (0.68 sec)
重複データのbirth_date、first_name、last_nameはわかりました。このクエリをサブクエリとして利用し、具体的な重複レコードを取得します。以下は、サブクエリで求めた重複レコードの条件にマッチしたemployees（社員）テーブルのレコードをbirth_dateでソートして抽出するSQLです。
SELECT *
FROM employees
WHERE ( birth_date, first_name, last_name ) IN (SELECT birth_date,
first_name,
last_name
FROM employees
GROUP BY birth_date,
first_name,
last_name
HAVING Count(birth_date) > 1)
ORDER BY birth_date;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 289866 | 1956-11-25 | Pintsang | Granlund | M | 1989-12-16 |
| 446230 | 1956-11-25 | Pintsang | Granlund | M | 1993-03-27 |
| 100278 | 1958-05-12 | Pragnesh | Acton | F | 1987-05-02 |
| 285261 | 1958-05-12 | Pragnesh | Acton | M | 1986-11-04 |
| 280722 | 1963-05-07 | Tonny | Cromarty | M | 1990-11-05 |
| 290824 | 1963-05-07 | Tonny | Cromarty | M | 1995-03-14 |
| 106602 | 1963-07-10 | Shim | Gide | M | 1994-09-23 |
| 451837 | 1963-07-10 | Shim | Gide | M | 1989-06-12 |
| 99791 | 1963-07-19 | Holgard | Negoita | M | 1986-10-02 |
| 229167 | 1963-07-19 | Holgard | Negoita | M | 1993-07-11 |
| 94844 | 1963-12-08 | Sarita | Ghalwash | M | 1986-12-30 |
| 224501 | 1963-12-08 | Sarita | Ghalwash | M | 1992-12-25 |
+--------+------------+------------+-----------+--------+------------+
12 rows in set (1.15 sec)
12件のレコードで重複があることがわかりました。
参考）SQLで重複しているレコードを全て抽出する (GROUP BY + HAVING) | JOHOBASE
SQLで重複しているデータを、最大のもの以外を除外してデータを取得する
SQLで重複しているデータを、特定の条件で1件だけ残して残りを除外するには、集計関数とgroup byを使用します。
以下は、salaries（年収）テーブルのemp_no、to_dateの組み合わせからto_dateが最大（最も新しい）データのみを抽出する例です。
mysql> select emp_no, max(to_date) from salaries group by emp_no limit 10; +--------+--------------+ | emp_no | max(to_date) | +--------+--------------+ | 10001 | 9999-01-01 | | 10002 | 9999-01-01 | | 10003 | 9999-01-01 | | 10004 | 9999-01-01 | | 10005 | 9999-01-01 | | 10006 | 9999-01-01 | | 10007 | 9999-01-01 | | 10008 | 2000-07-31 | | 10009 | 9999-01-01 | | 10010 | 9999-01-01 | +--------+--------------+ 10 rows in set (0.00 sec)
言い換えると、重複データのうち、最大のもの以外を除外したデータを取得しています。
SQLで重複しているデータを削除（DELETE）する
SQLで重複しているデータを1件残して削除するには、サブクエリを使います。
上記の「重複しているデータを、最大のもの以外を除外してデータを取得するSQL」をサブクエリとして同一のテーブルでnot inを使って重複データ以外をdeleteのwhere句で削除条件として使用しています。サブクエリ内でtmp1と別名をつけているのは、同一テーブル内で自己結合をおこなうためです。
delete from salaries where (emp_no, to_date) not in ( select tmp1.emp_no as emp_no, tmp1.to_date as to_date from ( select emp_no, max(to_date) as to_date from salaries group by emp_no ) as tmp1 );
【関連記事】
▶SQLで重複を削除するサンプルコード 最新データを残してdeleteするには？
まとめ
- SQLの重複を単純除外するにはdistinct
- 重複データをすべて抽出するにはgroup byとhavingを使う
- 重複データの削除には、集計関数＋groupbyとサブクエリを使った自己結合を組み合わせる
