SQLの小ネタ。

レコードが1:Nの関係のときに、Nが一定数以上のレコードが存在しうるか、存在する場合はどのようなレコードかを知りたいことがありました。

今回の記事ではユースケースとして分かりやすいように、usersテーブルからnameのうち同姓同名が何人・何種類あるかを調べることができるのをゴールとします。

環境

MySQL
- 8

下準備

次のテーブルを用意する。

CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(1024),
)

INSERT INTO users (id, name) VALUES (1, 'あいうえお');
INSERT INTO users (id, name) VALUES (2, 'あいうえお');
INSERT INTO users (id, name) VALUES (3, 'かきくけこ');
INSERT INTO users (id, name) VALUES (4, 'さしすせそ');
INSERT INTO users (id, name) VALUES (5, 'さしすせそ');

ゴール

同姓同名となるあいうえお、さしすせそを取得できるSQLを作成する。

対応

基本

集約したいカラムにgroup byをする
集約したカラムの数をCOUNTで数える
重複しているレコードを探したいので、HAVINGで2以上のレコードを探す

SELECT name, COUNT(*)
FROM users
GROUP BY name
HAVING COUNT(*) > 1;

name	COUNT(*)
あいうえお	2
さしすせそ	2

発展

IDも取得したい

MySQLの場合、group_concatを使用することで集約したIDを取得可能です。

SELECT name, COUNT(*), group_concat(id)
FROM users
GROUP BY name
HAVING COUNT(*) > 1;

name	COUNT(*)	group_concat(id)
あいうえお	2	1,2
さしすせそ	2	4,5

同姓同名の人が何人いるかを知りたい

同姓同名の人が何人いるかを知るには、基本の形をベースに副問い合わせを使用してSUMで計算するだけです。

SELECT SUM(same_name.a)
FROM (SELECT COUNT(*) as a
      FROM users
      GROUP BY name
      HAVING COUNT(*) > 1) as same_name;

同姓同名となった種類がいくつかを知りたい

同姓同名の種類がいくつかを知りたい場合は、基本の形をベースに副問い合わせを使用してCOUNTで計算するだけです。

SELECT COUNT(DISTINCT name) AS distinct_duplicate_names
FROM (SELECT name
      FROM users
      GROUP BY name
      HAVING COUNT(*) > 1) as duplicates;