sql - 如何在 Oracle SQL 数据库中查找相关行

Question

使用与此类似的表:

+-----------------+--------------------------+----------+
| CONTRACT_NUMBER | ORIGINAL_CONTRACT_NUMBER | CUST_ID| |
+-----------------+--------------------------+----------+
| 1               |                     0090 |      789 |
| 1-01            |                     0090 |      654 |
| 1-02            |                     0090 |      123 |
| 2               |                     0093 |      001 |
| 2 -01           |                     0093 |      001 |
| 3               |                     0094 |      666 |
| 4               |                     0095 |      999 |
| 4-01            |                     0095 |      888 |
| 5               |                     0096 |      002 |
| 5-01            |                     0096 |      002 |
| 6               |                     0097 |      555 |
+-----------------+--------------------------+----------+

我正在尝试整理所有没有重复的记录(即 contract_number 3 和 6)和有重复但具有相同 cust_id(即 contract_numbers 2、2-01 和 5、5-01)的记录，其中唯一剩下的契约(Contract)类型有多个条目，契约(Contract)编号相同但 cust_id 不同(即契约(Contract) 1,1-01,1-02 和 4、4-01)。我需要这全部是 sql，因为我需要评估超过 140k 条记录

甲骨文 SQL 11g

我的代码没有取得任何进展。

预期的结果是这样的:

+-----------------+--------------------------+---------+
| CONTRACT_NUMBER | ORIGINAL_CONTRACT_NUMBER | CUST_ID |
+-----------------+--------------------------+---------+
| 1               |                     0090 |     789 |
| 1-01            |                     0090 |     654 |
| 1-02            |                     0090 |     123 |
| 4               |                     0095 |     999 |
| 4-01            |                     0095 |     888 |
+-----------------+--------------------------+---------+

Answer 1

使用 db<>fiddle 在线测试:

WITH
  t AS (
    SELECT '1' AS contract_number, '0090' AS original_contract_number,
           '789' AS cust_id FROM dual UNION ALL
    SELECT '1-01',  '0090', '654' FROM dual UNION ALL
    SELECT '1-02',  '0090', '123' FROM dual UNION ALL
    SELECT '2',     '0093', '001' FROM dual UNION ALL
    SELECT '2 -01', '0093', '001' FROM dual UNION ALL
    SELECT '3',     '0094', '666' FROM dual UNION ALL
    SELECT '4',     '0095', '999' FROM dual UNION ALL
    SELECT '4-01',  '0095', '888' FROM dual UNION ALL
    SELECT '5',     '0096', '002' FROM dual UNION ALL
    SELECT '5-01',  '0096', '002' FROM dual UNION ALL
    SELECT '6',     '0097', '555' FROM dual
  ),
  a AS (
    SELECT t.*,
      COUNT(DISTINCT cust_id) OVER (PARTITION BY original_contract_number) q
    FROM t
  )
SELECT
  contract_number, original_contract_number, cust_id
FROM a
WHERE q > 1;

输出:

+-----------------+--------------------------+---------+
| CONTRACT_NUMBER | ORIGINAL_CONTRACT_NUMBER | CUST_ID |
+-----------------+--------------------------+---------+
| 1-02            |                     0090 |     123 |
| 1-01            |                     0090 |     654 |
| 1               |                     0090 |     789 |
| 4-01            |                     0095 |     888 |
| 4               |                     0095 |     999 |
+-----------------+--------------------------+---------+