備忘録 SQLで重複した行をすべて表示する

このようなテーブルがある。

以下のような結果を得たい。

WITH
  sample AS (
  SELECT
    1 AS code,
    100 AS name,
  UNION ALL
  SELECT
    1 AS code,
    101 AS name,
  UNION ALL
  SELECT
    2 AS code,
    200 AS name,
  UNION ALL
  SELECT
    3 AS code,
    300 AS name,
    )

-- SELECT * FROM sample

SELECT
  a.code,
  b.name
FROM (
  SELECT
    code,
    COUNT(*) AS c
  FROM
    sample
  GROUP BY
    code ) a
INNER JOIN
  sample b
ON
  b.code = a.code
  AND a.c > 1

BigQuery

Posted by たみぼうず