mysql 重複するデータの取得・1件をのぞいた重複データ削除方法

重複するデータの取得および1件をのぞいたデータ削除は以下の形式のクエリが有効です。

取得

select *
from {table}
inner join (
  select {column}
  from {table}
  group by {table}.{group_column}
  having count(*) > 1
) as tmp
on {table}.{group_column} = tmp.{group_column}

削除

delete 
from {table}
where {table}.id not in (
  select id 
  from (
    select {max|min|etc}(tmp.id) as id 
    from {table} as tmp
    group by tmp.{group_column}
  ) as tmp2
)

以下、実データ例を交えて

前提

ブログシステムを想定して
記事 および 記事に対する コメント、 コメントしたユーザが存在するとします。
データモデルは以下のような形です。

主題

「記事に対してユーザは何回でもコメントできること」 という仕様から、
「記事に対してユーザは1回しかコメントできないこと」 という仕様に変わったとします。

usecase1: 重複しているコメント一覧が欲しい

以下のようなクエリで取得できます。

select *
from comments
inner join (
  select article_id, user_id, count(*)
  from comments
  group by comments.article_id, comments.user_id
  having count(*) > 1
) as tmp
on comments.article_id = tmp.article_id and comments.user_id = tmp.user_id

usecase2: 最新一件だけ残して重複したデータを削除したい

以下のようなクエリで削除できます。

delete 
from comments
where comments.id not in (
  select max_id 
  from (
    select max(tmp.id) as max_id 
    from comments as tmp
    group by tmp.article_id, tmp.user_id
  ) as tmp2
)