SQL查询需求：将主字段关联子字段聚合为对象数组返回

针对你描述的md表主字段重复、子字段差异化的场景，以下是通过临时表插入+检索实现聚合的具体方案：

1. 生成去重主字段临时表 #

先将重复的主字段组合去重，存入临时表，确保每个主字段组合仅保留一条记录：

CREATE TEMPORARY TABLE md_main AS
SELECT DISTINCT laborOfficeId, sequenceNumber, unifiedNationalNumber, nationalId
FROM md;

2. 关联原表聚合子字段为对象数组 #

通过临时表关联原表，利用JSON聚合函数将每个主字段对应的子字段打包为paidstatuslist对象数组：

MySQL版本 #

SELECT 
    m.laborOfficeId,
    m.sequenceNumber,
    m.unifiedNationalNumber,
    m.nationalId,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'paidStatus', d.paidStatus,
            'bankName', d.bankName
            -- 按需添加其他需要聚合的子字段
        )
    ) AS paidstatuslist
FROM md_main m
JOIN md d ON 
    m.laborOfficeId = d.laborOfficeId
    AND m.sequenceNumber = d.sequenceNumber
    AND m.unifiedNationalNumber = d.unifiedNationalNumber
    AND m.nationalId = d.nationalId
GROUP BY 
    m.laborOfficeId,
    m.sequenceNumber,
    m.unifiedNationalNumber,
    m.nationalId;

PostgreSQL版本 #

如果使用PostgreSQL，替换对应的JSON函数即可：

SELECT 
    m.laborOfficeId,
    m.sequenceNumber,
    m.unifiedNationalNumber,
    m.nationalId,
    json_agg(
        json_build_object(
            'paidStatus', d.paidStatus,
            'bankName', d.bankName
        )
    ) AS paidstatuslist
FROM md_main m
JOIN md d ON 
    m.laborOfficeId = d.laborOfficeId
    AND m.sequenceNumber = d.sequenceNumber
    AND m.unifiedNationalNumber = d.unifiedNationalNumber
    AND m.nationalId = d.nationalId
GROUP BY 
    m.laborOfficeId,
    m.sequenceNumber,
    m.unifiedNationalNumber,
    m.nationalId;

简化方案：直接分组聚合 #

如果你的主字段组合本身没有逻辑重复（只是查询时重复展示），可以跳过临时表步骤，直接在原表上分组聚合：

-- MySQL直接聚合
SELECT 
    laborOfficeId,
    sequenceNumber,
    unifiedNationalNumber,
    nationalId,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'paidStatus', paidStatus,
            'bankName', bankName
        )
    ) AS paidstatuslist
FROM md
GROUP BY 
    laborOfficeId,
    sequenceNumber,
    unifiedNationalNumber,
    nationalId;

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