SQLで重複を検索し件数を取得したい!

2020-06-09

重複数を出すのが、難しかったので備忘録。

動作環境

この記事はSQL FIDDLEでMySQL5.6、PostgreSQL9.6、SQLiteで動作確認しました。
下記文章は主にPostgreSQLで仕様で調べています。

SQLで重複を検索し件数を取得する方法

サンプルコードで実際に重複件数を確認してみます。

準備

以下の内容のテーブルを使用します。

ItemIDItemNameQuantityAREA
1プリン25大阪
2ゼリー20大阪
3ゼリー30東京
4プリン15大阪
5プリン60東京
6プリン25大阪
7ゼリー20東京
item テーブル

上記テーブルの作成用コードです。

-- CREATE文 item テーブル
CREATE TABLE item (
ItemID numeric(8, 0) unique not null,
ItemName character varying (20),
Quantity numeric(8, 0),
AREA character varying (20),
primary key(ItemID)
);
-- itemテーブル用INSERT文
insert into item
values(0001, 'プリン', 25, '大阪'),
(0002, 'ゼリー', 20, '大阪'),
(0003, 'ゼリー', 30, '東京'),
(0004, 'プリン', 15,'大阪'),
(0005, 'プリン', 60, '東京'),
(0006, 'プリン', 25, '大阪'),
(0007, 'ゼリー', 20, '東京');

1カラムで重複している箇所を取得

ItemNameのカラムで重複している件数を取得したいと思います。

SELECT
count(ItemName),
ItemName
FROM
item
GROUP BY
ItemName
HAVING count(ItemName)>1
;

■ 取得結果

プリンが4つゼリーが3つ重複していることがわかります。

countitemname
4プリン
3ゼリー

count()については以下でまとめています。

複数カラムで重複している箇所を取得

ItemNameとAREAがどちらも重複している箇所を取得します。

SELECT
count(*),
ItemName,
AREA
FROM
item
GROUP BY
ItemName,
AREA
HAVING count(*)>1
;

■ 取得結果

count(*)ItemNameAREA
2ゼリー東京
3プリン大阪

おわり

HAVINGが便利そうですね。
データが一意でなければならない時に、一意でないデータを抽出するのに使ったりしました。