Oracle非规范化表关联查询去重问题求助

问题分析 #

你遇到的重复数据是因为Test1和Test2中存在多个相同DEPT_ID+SECTOR_ID的记录，直接关联会产生笛卡尔积，导致结果集膨胀。以下是几种基于你仅拥有SELECT权限的解决方案：

方案1：直接去重（保留唯一记录组合） #

如果只需要结果集中的唯一字段组合，使用DISTINCT关键字过滤重复行：

SELECT DISTINCT
    t1.dir_id,
    t1.dept_id,
    t1.sector_id,
    t2.place_id,
    t2.amount
FROM test1 t1
JOIN test2 t2 
    ON t1.dept_id = t2.dept_id
    AND t1.sector_id = t2.sector_id;

方案2：先对单表去重再关联 #

先分别过滤Test1和Test2中的重复记录，再进行关联，减少笛卡尔积的计算量：

SELECT
    t1.dir_id,
    t1.dept_id,
    t1.sector_id,
    t2.place_id,
    t2.amount
FROM (
    SELECT DISTINCT dir_id, dept_id, sector_id
    FROM test1
) t1
JOIN (
    SELECT DISTINCT dept_id, sector_id, place_id, amount
    FROM test2
) t2 
    ON t1.dept_id = t2.dept_id
    AND t1.sector_id = t2.sector_id;

方案3：按行号一对一关联（保留明细避免无意义笛卡尔积） #

如果需要保留两个表的明细记录，但不想产生无意义的交叉匹配，可以用ROW_NUMBER()给每个DEPT_ID+SECTOR_ID分组内的行编号，再按行号关联：

SELECT
    t1.dir_id,
    t1.dept_id,
    t1.sector_id,
    t2.place_id,
    t2.amount
FROM (
    SELECT 
        dir_id, dept_id, sector_id,
        ROW_NUMBER() OVER (PARTITION BY dept_id, sector_id ORDER BY other) AS rn
    FROM test1
) t1
JOIN (
    SELECT 
        dept_id, sector_id, place_id, amount,
        ROW_NUMBER() OVER (PARTITION BY dept_id, sector_id ORDER BY amount) AS rn
    FROM test2
) t2 
    ON t1.dept_id = t2.dept_id
    AND t1.sector_id = t2.sector_id
    AND t1.rn = t2.rn;

注：ORDER BY后的字段可根据实际业务需求调整，确保行号排序逻辑符合预期。

方案4：聚合Test2数据（适合统计场景） #

如果需要对Test2的金额进行统计（比如求和、取平均值），可以先聚合再关联：

SELECT
    t1.dir_id,
    t1.dept_id,
    t1.sector_id,
    t2.place_id,
    t2.total_amount
FROM test1 t1
JOIN (
    SELECT 
        dept_id, sector_id, place_id,
        SUM(amount) AS total_amount
    FROM test2
    GROUP BY dept_id, sector_id, place_id
) t2 
    ON t1.dept_id = t2.dept_id
    AND t1.sector_id = t2.sector_id;

可根据需求替换SUM为MAX、MIN、AVG等聚合函数。

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