Oracle中如何获取仅拥有相同balance的account_id与order_id？

需求说明 #

现有两张表：

• account：存储账号ID与订单ID的关联关系，一个账号可对应多个订单
• account_bal：存储订单ID对应的余额值

需要筛选出同一账号下，余额值重复出现的所有订单及关联信息，即每个账号中那些共享相同余额的订单记录。

原查询问题分析 #

你提供的原查询使用了旧版表连接语法，且GROUP BY仅做分组操作，未筛选出同一账号下余额重复的记录，因此无法得到期望结果。

正确查询语句 #

方法一：使用窗口函数（推荐，效率较高）

SELECT account_id, order_id, balance
FROM (
    SELECT 
        a.account_id, 
        a.order_id, 
        b.balance,
        -- 统计同一账号下同一余额对应的订单数量
        COUNT(*) OVER (PARTITION BY a.account_id, b.balance) AS balance_count
    FROM account a
    INNER JOIN account_bal b 
        ON a.order_id = b.order_id
) AS temp
-- 筛选出数量大于1的记录（即同一账号下余额重复的订单）
WHERE balance_count > 1
ORDER BY account_id, balance;

方法二：使用子查询筛选目标组合

SELECT a.account_id, a.order_id, b.balance
FROM account a
INNER JOIN account_bal b 
    ON a.order_id = b.order_id
-- 关联子查询得到"账号+余额"的重复组合
INNER JOIN (
    SELECT a.account_id, b.balance
    FROM account a
    INNER JOIN account_bal b 
        ON a.order_id = b.order_id
    GROUP BY a.account_id, b.balance
    -- 筛选出同一账号下对应多个订单的余额
    HAVING COUNT(DISTINCT a.order_id) > 1
) AS target_groups 
    ON a.account_id = target_groups.account_id 
    AND b.balance = target_groups.balance
ORDER BY a.account_id, b.balance;

结果验证 #

以上两种语句执行后，均可得到你期望的输出：

内容的提问来源于stack exchange，提问作者O_Athens