如何关联一对多子表且不增加SQL查询返回行数？

我有一个包含2313行数据的父表A（对应SQL中的device.instance），它与子表B（controls.IO_table）为一对多关系。我需要创建视图展示表A的信息，核心要求是视图中表A的行不得重复，但需从子表B提取部分信息。

最初使用LEFT JOIN关联表B，返回行数增至约5000行，父表行出现重复；尝试通过含MAX与GROUP BY的子查询仅返回1条子表数据，但仍未成功，返回约3000行。我不关心选用哪条子表数据，仅需确保父表行无重复。

你当前的SQL里，除了通过子查询sub1关联子表B，还额外做了LEFT JOIN controls.IO_table AS t3 ON t3.parent_device_ID = t1.ID_auto——这就是父表行依旧重复的关键原因！t3的一对多关联会让每个父表行对应多条子表行，哪怕sub1已经按父表ID分组，t3的关联还是会把结果行数拉上去。

另外还发现一个小错误：LEFT JOIN enum.mounting_style AS e9 ON e7.ID_auto = t1.mounting_style_ID里的关联字段写错了，应该用e9.ID_auto而非e7.ID_auto，否则会导致MOUNTING STYLE数据关联错误。

去掉多余的t3关联，只保留子查询sub1来关联子表B，同时修正关联错误的字段，这样就能保证每个父表行仅对应一条子表数据，最终返回行数与父表一致。修改后的SQL如下：

SELECT 
    -- 表A及枚举表字段（正常关联）
    t1.device_name_AF                AS 'DEVICE NAME',
    e1.issuance_name                 AS 'ISSUANCE',
    t1.revision_number               AS 'REVISION',
    t1.description_combo_AF          AS 'INSTRUMENT DESCRIPTION',
    t2.PCDT_AF                       AS 'PCDT',
    t2.device_description            AS 'PCDT DESCRIPTION',
    c2.company_name                  AS 'VENDOR',
    t2.all_parts_used_AF             AS 'PARTS',
    e2.floor_key_AF                  AS 'FLOOR',
    -- t1.zone_location                 AS 'ZONE',
    e3.system_acronym                AS 'SYSTEM',
    t1.equipment_type                AS 'EQUIPMENT TYPE',
    t1.system_process_number         AS 'SYSTEM PROCESS NUMBER',
    e4.device_acronym                AS 'INSTRUMENT TYPE',
    t1.parallel_equipment_designator AS 'PARALLEL EQUIPMENT DESIGNATOR',
    t1.pnid_drawing                  AS 'P&ID',
    t1.location_drawing              AS 'LOCATION DRAWING',
    e5.model_status                  AS 'BIM STATUS',
    t1.submittal_number              AS 'SUBMITTAL NUMBER',
    t1.submittal_status              AS 'SUBMITTAL STATUS',
    e6.company_name                  AS 'PROCURED BY',
    e7.company_name                  AS 'INSTALLED BY',
    e8.company_name                  AS 'WIRED BY',
    t1.instance_comment              AS 'DEVICE COMMENT',
    e9.mounting_description          AS 'MOUNTING STYLE',
    t1.IO_point_count_AF             AS 'ASSOCIATED IO COUNT',

    -- 子表B字段（通过分组子查询获取）
    sub1.lookup_rack_num_AF           AS 'RACK',
    sub1.lookup_slot_num_AF           AS 'SLOT',
    sub1.channel                      AS 'POINT',

    t1.location_N_S                  AS 'LOCATION N/S',
    t1.location_E_W                  AS 'LOCATION E/W',
    t1.location_room                 AS 'LOCATION ROOM'
    
FROM device.instance                    AS t1
LEFT JOIN device.device_type_catalog    AS t2 ON t2.ID_auto = t1.PCDT_ID

LEFT JOIN enum.issuance                 AS e1 ON e1.ID_auto = t1.issuance_ID
LEFT JOIN enum.building_floor           AS e2 ON e2.ID_auto = t1.floor_ID
LEFT JOIN enum.process_system           AS e3 ON e3.ID_auto = t1.system_ID
LEFT JOIN enum.device_name              AS e4 ON e4.ID_auto = t1.instrument_type_ID
LEFT JOIN enum.BIM_status               AS e5 ON e5.ID_auto = t1.BIM_status_ID
LEFT JOIN enum.company                  AS e6 ON e6.ID_auto = t1.procured_by_ID
LEFT JOIN enum.company                  AS e7 ON e7.ID_auto = t1.installed_by_ID
LEFT JOIN enum.company                  AS e8 ON e8.ID_auto = t1.wired_by_ID
LEFT JOIN enum.mounting_style           AS e9 ON e9.ID_auto = t1.mounting_style_ID -- 修正此处关联字段

LEFT JOIN enum.company                  AS c2 ON c2.ID_auto = t2.vendor_ID

LEFT JOIN 
(
    SELECT 
        MAX(x3.lookup_rack_num_AF) AS lookup_rack_num_AF, 
        MAX(x3.lookup_slot_num_AF) AS lookup_slot_num_AF, 
        MAX(x3.channel) AS channel, 
        x3.parent_device_ID 
    FROM controls.IO_table AS x3
    GROUP BY x3.parent_device_ID
) AS sub1 ON sub1.parent_device_ID = t1.ID_auto

WHERE (t1.PCDT_ID IS NOT NULL) AND (t2.is_instrument = 1)

如果不一定要用MAX，也可以用ROW_NUMBER()窗口函数来任选一条子表数据，效果是一样的，示例如下：

-- 替换原有的sub1子查询部分
LEFT JOIN 
(
    SELECT 
        x3.lookup_rack_num_AF, 
        x3.lookup_slot_num_AF, 
        x3.channel, 
        x3.parent_device_ID,
        ROW_NUMBER() OVER(PARTITION BY x3.parent_device_ID ORDER BY (SELECT NULL)) AS rn
    FROM controls.IO_table AS x3
) AS sub1 ON sub1.parent_device_ID = t1.ID_auto AND sub1.rn = 1

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