備忘録 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
最近のコメント