(cache)重複しているレコードを検索するSQL(大量データも対応)

たまにしかやらないけど、
毎回やり方を調べている気がするので備忘録としてメモ。

DISTINCTを使って重複した行をまとめることはできるけど、
逆はできないので、強引に対応する。

列Aが重複しているデータを取得

SELECT
  列A
FROM
  テーブルA
GROUP BY
  列A
HAVING
  COUNT(列A) > 1

列Aが重複しているデータと重複している件数も一緒に取得

SELECT
  COUNT(列A) duplicate_count
  , 列A
FROM
  テーブルA 
GROUP BY
  列A
HAVING
  COUNT(列A) > 1

COUNT足しただけ。
このままだと重複している列のデータしかわからないので、他の列のデータも取得する

重複している列Aを含む全列取得

SELECT
  * 
FROM
  テーブルA
where
  列A in ( 
    SELECT
      列A
    FROM
      テーブルA
    GROUP BY
      列A
    HAVING
      COUNT(列A) > 1
  ) 
ORDER BY
  列A

重複している列Aを含む全列と重複している件数を一緒に取得

SELECT
 *
FROM
  テーブルA table1
  INNER JOIN ( 
    SELECT
      COUNT(列A) duplicate_count
      , 列A
    FROM
      テーブルA
    GROUP BY
      列A 
    HAVING
      COUNT(列A) > 1
  ) table2
    ON table1.列A = table2.列A
ORDER BY
  table1.列A

上記のタイプのSQLだと
データ件数が多いと返ってこない可能性があるので、
こんな感じに書くとイケるはず(多分)。

データの件数が多いとき

SELECT
  * 
FROM
  テーブルA table1
WHERE
  EXISTS ( 
    SELECT
      *
    FROM
      テーブルA table2 
    WHERE
      table1.列A = table2.列A
    GROUP BY
      table2.列A
    HAVING
      COUNT(table2.列A) > 1
  ) 
ORDER BY
  table1.列A

書いてみて、最後のやつだけでいいじゃんってなったけど気にしないw

ORDER BYでソートしてるのは見やすくするためです。

もっと良いやり方あるかもしれないけど、とりあえずこれで。