読者です 読者をやめる 読者になる 読者になる

【MySQL】複数列を取得する場合に特定の列を指定して distinct をかける。

MySQL

MySQL重複チェックをする時にdistinctを使うことが多い。
ただ、複数列を返して特定列に distinct をかけることはできないので、
結論としては無理。

なので、それの代わりになる方法をまとめる。
結構無理やりだけど・・・・。

ここでの重複チェックとは、
重複するレコードは1件だけ残して、
あとは捨てることとする。

まずはテーブル作成。

create table user(id int primary key auto_increment, name varchar(100), email varchar(100), tel varchar(100));
insert into user(name, email, tel) values('name1', 'email1', 'tel1');
insert into user(name, email, tel) values('name2', 'email2', 'tel2');
insert into user(name, email, tel) values('name3', 'email3', 'tel3');
insert into user(name, email, tel) values('name4', 'email4', 'tel4');
insert into user(name, email, tel) values('name5', 'email5', 'tel5');
insert into user(name, email, tel) values('name6', 'email6', 'tel6');
insert into user(name, email, tel) values('name7', 'email7', 'tel7');
insert into user(name, email, tel) values('name1', 'email2', 'tel2');
insert into user(name, email, tel) values('name1', 'email3', 'tel3');

上記のテーブルから、name, email, tel を取得し、
nameで重複チェックをかける。

重複チェックの一番簡単な方法は GROUP BY + MAX() / MIN() を使うパターン。

SELECT name, MAX(email), MAX(tel) FROM user GROUP BY name; 

GROUP BY は集計関数なので、重複チェックをかけてくれる。
そこで、 MAX() or MIN() を使って値を取り出す。

問題は GROUP BY でソートが発生するため、
レコードが大量になると遅い。
全レコードを取り出そうとすると、PHPのメモリエラーになる。

そこで、GROUP BY を使わない方法を考えた結果以下の方法があるかと・・・。
手法1.userテーブルをコピーして、そのテーブルから重複レコードをDELETEする。
手法2.userテーブルから重複レコードの主キーを抜き出し、NOT IN で対象外にする。


まずは手法1。

userテーブルのコピー。

CREATE TABLE tmp_user (SELECT * FROM user);

次にtmp_userテーブルから重複レコードを削除する。

DELETE U1 FROM user AS U1 WHERE U1.id < (SELECT MAX(U2.id) FROM (SELECT id, name FROM user) AS U2 WHERE U1.name = U2.name);

これで、tmp_userテーブルには name を重複チェックしたレコードが残る。
重複条件は「WHERE U1.name = U2.name」の部分で設定できる。
emailも含めたければ以下のようにする。
「WHERE U1.name = U2.name AND U1.email = U2.email」
これで name, email, tel を取得しつつ、
name と email で重複チェックをかけれる。


次に手法2。

SELECT name, email, tel FROM user WHERE id NOT IN (SELECT id FROM user AS U1 WHERE U1.id < (SELECT MAX(U2.id) FROM (SELECT id, name FROM user) AS U2 WHERE U1.name = U2.name));

普通に手法2の方がいいと思う。
手法1で実装した後に、
「重複IDをDELETEするなら、SELECT で NOT IN すればいーのでは・・・」
と思ったので、実際に採用したのは手法2です。