データベース内の重複レコードの削除
5562 ワード
データの準備
MySQLのテーブル名とフィールド名は小文字として認識されません.
大文字が必要な場合は、
`テーブル名またはフィールド名を囲む:
DB2
データベースに重複レコードが存在する場合は、次の2つに分けられます.
1つ目のケースでは、フィールドの値が同じ場合があります.
2つ目のケースは、すべてのフィールドの値が同じです.
次は汎用SQL文です.すべてのリレーショナル・データベースの1と2のシナリオに適用されます.
名前などの重複レコードの問合せ-コースの重複:
結果:
+------+---------+-------+
| name | course | count |
+------+---------+-------+
| John | Biology | 3 |
+------+---------+-------+
これに基づいて、すべての重複レコードをクエリーします.
結果:
+------+------+---------+-------+
| id | name | course | score |
+------+------+---------+-------+
| 1 | John | Biology | 90 |
| 2 | John | Biology | 90 |
| 4 | John | Biology | 90 |
+------+------+---------+-------+
1つ目のケース:
a.通常のSQL接続方式を採用して、条件はこれらのフィールドの値が同じで、その他のフィールドの値が対応するフィールドの値より大きいか小さい(一つだけ取って、同じレコードを表すことに等しい)
重複したレコードのクエリ:
結果:
+------+------+---------+-------+
| id | name | course | score |
+------+------+---------+-------+
| 2 | John | Biology | 90 |
| 4 | John | Biology | 90 |
+------+------+---------+-------+
重複除外:
b.データベース固有のSQL文を使用し、第2のケースと同様の書き方をします.
第2のケース
異なるデータベースには異なるソリューションがあり、SQL Serverは
DISTINCTキーワード、Oracle Yes
ROWID、DB 2は
ROW_NUMBER OVER(PARTITION BY [FIELDS] ORDER BY [FIELDS])
データの準備:
SQL Server
重複除外:
Oracle
重複したレコードのクエリ:
重複除外:
DB2
重複したレコードのクエリ:
重複除外:
MySQLのテーブル名とフィールド名は小文字として認識されません.
大文字が必要な場合は、
`テーブル名またはフィールド名を囲む:
-- Clear first
DROP TABLE IF EXISTS `tb_score`;
-- Create the table
CREATE TABLE `tb_score`(
`id` mediumint,
`name` varchar(20),
`course` varchar(30),
`score` smallint
);
-- Batch insert, supported by MySQL and DB2
INSERT INTO tb_score
(id, name, course, score)
VALUES
(1, 'John', 'Biology', 90),
(2, 'John', 'Biology', 90),
(3, 'Lisa', 'Chemistry', 80),
(4, 'John', 'Biology', 90);
DB2
データベースに重複レコードが存在する場合は、次の2つに分けられます.
1つ目のケースでは、フィールドの値が同じ場合があります.
2つ目のケースは、すべてのフィールドの値が同じです.
次は汎用SQL文です.すべてのリレーショナル・データベースの1と2のシナリオに適用されます.
名前などの重複レコードの問合せ-コースの重複:
SELECT
name,
course,
COUNT(*) AS "count"
FROM
tb_score
GROUP BY
name,
course
HAVING
COUNT(*) > 1
結果:
+------+---------+-------+
| name | course | count |
+------+---------+-------+
| John | Biology | 3 |
+------+---------+-------+
これに基づいて、すべての重複レコードをクエリーします.
SELECT
*
FROM
tb_score a
WHERE
(
a.name, a.course) IN
(
SELECT
b.name,
b.course
FROM
tb_score b
GROUP BY
b.name,
b.course
HAVING
COUNT(1) > 1)
結果:
+------+------+---------+-------+
| id | name | course | score |
+------+------+---------+-------+
| 1 | John | Biology | 90 |
| 2 | John | Biology | 90 |
| 4 | John | Biology | 90 |
+------+------+---------+-------+
1つ目のケース:
a.通常のSQL接続方式を採用して、条件はこれらのフィールドの値が同じで、その他のフィールドの値が対応するフィールドの値より大きいか小さい(一つだけ取って、同じレコードを表すことに等しい)
重複したレコードのクエリ:
SELECT
*
FROM
tb_score a
WHERE
EXISTS
(
SELECT
1
FROM
tb_score b
WHERE
a.name=b.name
AND a.course=b.course
AND a.id > b.id)
結果:
+------+------+---------+-------+
| id | name | course | score |
+------+------+---------+-------+
| 2 | John | Biology | 90 |
| 4 | John | Biology | 90 |
+------+------+---------+-------+
重複除外:
-- MySQL doesn't support
DELETE
FROM
tb_score a
WHERE
EXISTS
(
SELECT
1
FROM
tb_score b
WHERE
a.name = b.name
AND a.course = b.course
AND a.id > b.id)
b.データベース固有のSQL文を使用し、第2のケースと同様の書き方をします.
第2のケース
異なるデータベースには異なるソリューションがあり、SQL Serverは
DISTINCTキーワード、Oracle Yes
ROWID、DB 2は
ROW_NUMBER OVER(PARTITION BY [FIELDS] ORDER BY [FIELDS])
データの準備:
-- Drop and create table are omitted
-- Batch insert
INSERT INTO tb_score
(id, name, course, score)
VALUES
(1, 'John', 'Biology', 90),
(1, 'John', 'Biology', 90),
(2, 'Lisa', 'Chemistry', 80),
(1, 'John', 'Biology', 90);
SQL Server
重複除外:
CREATE TABLE
#tmp AS
SELECT DISTINCT
*
FROM
tb_score;
TRUNCATE TABLE
tb_score;
INSERT
INTO
tb_score
SELECT
*
FROM
#tmp;
Oracle
重複したレコードのクエリ:
SELECT
*
FROM
tb_score a
WHERE
a.ROWID >
(
SELECT
MIN(ROWID)
FROM
tb_score b
WHERE
a.id = b.id
AND a.name = b.name
AND a.course = b.course)
重複除外:
DELETE
FROM
tb_score a
WHERE
a.ROWID >
(
SELECT
MIN(ROWID)
FROM
tb_score b
WHERE
a.id = b.id
AND a.name = b.name
AND a.course = b.course)
DB2
重複したレコードのクエリ:
SELECT
id,
name,
course
FROM
(
SELECT
id,
name,
course,
ROW_NUMBER() OVER(PARTITION BY id, name, course) AS row_seq
FROM
tb_score) t
WHERE
t.row_seq > 1
重複除外:
DELETE
FROM
(
SELECT
id,
name,
course,
ROW_NUMBER() OVER(PARTITION BY id, name, course) AS row_seq
FROM
tb_score) t
WHERE
t.row_seq > 1