自己結合の使用例２（同一項目での結合）

自己結合の使用例を2部構成で紹介しています。

本記事では、第2部として同テーブルの同一項目での自己結合の使用例を紹介します。

同一項目での自己結合の使用例
受注テーブルを例に自己結合を考える
1. 重複データの抽出
2. 重複データの削除

同一項目での自己結合の使用例

同テーブルの同一項目での自己結合での使用例は、重複データの抽出や削除です。

具体的には、あるテーブルにデータが重複しているデータがあることが分かった場合に、対象のデータを抽出して確認したり、不要な方のデータを削除したりする場合です。

受注テーブルを例に自己結合を考える

以下のような受注テーブルを例に考えてみます。

-- receive_order（受注テーブル）
--  1.customer_id（得意先ID）
--  2.order_no（伝票番号）
--  3.insert_date（登録日）

CUSTOMER_ID ORDER_NO   INSERT_DATE
----------- ---------- -----------
00100       1111111111 20161026
00100       2222222222 20161026
00100       2222222222 20161027
00100       3333333333 20161026
00100       3333333333 20161027
00100       3333333333 20161028
00200       1111111111 20161028
00200       1111111111 20161028
00200       2222222222 20161028

重複データの抽出

重複しているデータを抽出する場合を考えてみます。

得意先ID＋伝票番号で重複しているデータを出力します。

SELECT *
FROM   receive_order r1
WHERE  EXISTS(SELECT *
              FROM   receive_order r2
              WHERE  r1.customer_id =  r2.customer_id
              AND    r1.order_no    =  r2.order_no
              AND    r1.ROWID       <> r2.ROWID
             )
;

CUSTOMER_ID ORDER_NO   INSERT_DATE
----------- ---------- -----------
00100       2222222222 20161026
00100       2222222222 20161027
00100       3333333333 20161026
00100       3333333333 20161027
00100       3333333333 20161028
00200       1111111111 20161028
00200       1111111111 20161028

得意先ID＋伝票番号で集約し、件数が2件以上のデータを抽出する方法も考えられます。
しかし、その方法では対象の得意先ID＋伝票番号の一覧になってしまい、対象のレコード全体を出力するためにはもうひと手間が必要になります。

SELECT customer_id
      ,order_no
FROM   receive_order
GROUP BY customer_id
        ,order_no
;

CUSTOMER_ID ORDER_NO
----------- ----------
00100       2222222222
00100       3333333333
00200       1111111111

-- 抽出されたキー情報を元に再度検索
SELECT *
FROM   receive_order
WHERE  customer_id = '00100'
AND    order_no    = '2222222222'

CUSTOMER_ID ORDER_NO   INSERT_DATE
----------- ---------- -----------
00100       2222222222 20161026
00100       2222222222 20161027

重複データの削除

重複しているデータを削除する場合を考えてみます。

得意先ID＋伝票番号で重複しているデータを削除します。ただし、登録日が最新のデータを優先して残すようにします（登録日も同一の場合はいずれかを削除）。

DELETE FROM receive_order r1
WHERE EXISTS(SELECT *
             FROM   receive_order r2
             WHERE  r1.customer_id = r2.customer_id
             AND    r1.order_no    = r2.order_no
             AND    (   r1.insert_date < r2.insert_date
                     OR (    r1.insert_date = r2.insert_date
                         AND r1.ROWID       < r2.ROWID
                        )
                    )
            )
;

--削除後
CUSTOMER_ID ORDER_NO   INSERT_DATE
----------- ---------- -----------
00100       1111111111 20161026
00100       2222222222 20161027
00100       3333333333 20161028
00200       1111111111 20161028
00200       2222222222 20161028