Oracle SQL消除多表连接重复行，按规则保留关联字段值

在Oracle SQL环境中，关联STRUCTURE、STRUCTURE_STATUS_TY、span、STRUCTURE_FEAT_PROXMTY四表后，由于STRUCTURE_FEAT_PROXMTY表存在多组Name_1与Proximity值，导致桥梁核心信息字段（Structure_no、Name等前5列）出现大量重复行。需求是：每个Structure_no下的不同Span NO.行中，仅为部分行分配Name_1和Proximity值，其余行对应字段留空。

原SQL语句 #

SELECT 
    STRUCTURE_NO, 
    NAME,
    STRUCTURE_TYPE,
    number_of_spans,
    main_span_flag,
    CASE rn WHEN 1 THEN name1 END AS name1,
    CASE rn WHEN 1 THEN PROXIMITY_CODE END AS proximity_code
FROM   ( 
    SELECT a.STRUCTURE_NO, 
        a.NAME,
        a.STRUCTURE_TYPE,
        a.number_of_spans,
        d.main_span_flag,
        d.span_no,
        e.NAME AS name1,
        e.PROXIMITY_CODE,
      
        ROW_NUMBER() OVER (
            PARTITION BY a.structure_no,e.name,e.proximity_code                      
            ORDER BY d.span_no ASC
        ) AS rn,
        ROW_NUMBER() OVER (
            PARTITION BY d.span_no,e.name                     
            ORDER BY a.structure_no ASC
        ) AS rn1
FROM   STRUCTURE a
        INNER JOIN STRUCTURE_STATUS_TY b
        ON a.structure_status_type_code=b.structure_status_type_code
        LEFT OUTER JOIN span d
        ON a.structure_id=d.structure_id
        LEFT OUTER JOIN STRUCTURE_FEAT_PROXMTY e
        ON a.STRUCTURE_ID = e.STRUCTURE_ID
        

ORDER BY a.STRUCTURE_NO ASC 
)

修正后的SQL语句 #

SELECT 
    STRUCTURE_NO, 
    NAME,
    STRUCTURE_TYPE,
    number_of_spans,
    main_span_flag,
    CASE WHEN rn = 1 THEN name1 ELSE NULL END AS name1,
    CASE WHEN rn = 1 THEN PROXIMITY_CODE ELSE NULL END AS proximity_code
FROM   ( 
    SELECT 
        a.STRUCTURE_NO, 
        a.NAME,
        a.STRUCTURE_TYPE,
        a.number_of_spans,
        d.main_span_flag,
        d.span_no,
        e.NAME AS name1,
        e.PROXIMITY_CODE,
        -- 标记每个(Structure_no, name1, proximity_code)组合对应的首个Span行
        ROW_NUMBER() OVER (
            PARTITION BY a.structure_no, e.name, e.proximity_code                      
            ORDER BY d.span_no ASC
        ) AS rn,
        -- 确保每个Span行仅关联一组(name1, proximity_code)
        ROW_NUMBER() OVER (
            PARTITION BY a.structure_no, d.span_no
            ORDER BY e.name, e.proximity_code
        ) AS rn_span
    FROM   STRUCTURE a
    INNER JOIN STRUCTURE_STATUS_TY b
        ON a.structure_status_type_code = b.structure_status_type_code
    LEFT OUTER JOIN span d
        ON a.structure_id = d.structure_id
    LEFT OUTER JOIN STRUCTURE_FEAT_PROXMTY e
        ON a.STRUCTURE_ID = e.STRUCTURE_ID
    WHERE rn_span = 1 -- 过滤同一Span行的重复关联结果
    ORDER BY a.STRUCTURE_NO ASC, d.span_no ASC
)

修正说明 #

• 新增rn_span分区逻辑：按Structure_no和span_no分组，为每个Span行仅保留第一组匹配的(name1, proximity_code)，避免同一Span行被多组关联值重复输出
• 增加WHERE rn_span = 1过滤条件，去除同一Span行的重复数据，减少核心信息字段的重复
• 保留原rn标记逻辑，确保每个(Structure_no, name1, proximity_code)组合仅在对应的首个Span行显示值，其余Span行的name1和proximity_code字段留空

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