You need to enable JavaScript to run this app. 如何合并两条相同merchantID的记录,在查询结果中新增SecondaryEmailAddress字段
优惠活动
大模型
产品
解决方案
定价
更多

如何合并两条相同merchantID的记录,在查询结果中新增SecondaryEmailAddress字段

T-SQL 同商户号多邮箱合并实现方案

问题根因

你遇到的同merchantID返回多条记录的问题,本质是同一个商户在POSH5_Prod_MerchantContactDetails表中存在多条联系人记录,关联查询后就会生成对应行数的结果,以下是两种可直接落地的实现方案:


方案1:拆分主次邮箱(适配最多N个邮箱的场景)

如果需要明确区分主、副邮箱,可通过窗口函数排序+条件聚合实现:

WITH MerchantContactRanked AS (
    SELECT
        m.id,
        m.merchantID,
        m.name,
        m.LegalName,
        c.EmailAddress,
        m.MCCRecord,
        m.addressLine1,
        m.addressLine2,
        m.addressLine3,
        m.addressLine4,
        m.city,
        m.phoneNumber,
        -- 对同商户下的联系人排序,可调整ORDER BY规则决定主邮箱优先级
        ROW_NUMBER() OVER (PARTITION BY m.merchantID ORDER BY c.id ASC) AS contact_rank
    FROM POSH5_Prod_MerchantDetails m
    INNER JOIN POSH5_Prod_MerchantContactDetails c 
        ON m.id = c.MerchantRecord
    WHERE m.coreBankingRecord = 27 AND m.active = 1
)
SELECT
    id,
    merchantID,
    name,
    LegalName,
    MAX(CASE WHEN contact_rank = 1 THEN EmailAddress END) AS EmailAddress,
    MAX(CASE WHEN contact_rank = 2 THEN EmailAddress END) AS SecondaryEmailAddress,
    -- 若存在更多邮箱可继续拓展对应字段
    MCCRecord,
    addressLine1,
    addressLine2,
    addressLine3,
    addressLine4,
    city,
    phoneNumber
FROM MerchantContactRanked
GROUP BY 
    id, merchantID, name, LegalName, MCCRecord,
    addressLine1, addressLine2, addressLine3, addressLine4, city, phoneNumber

方案2:多邮箱拼接(适配不限邮箱数量的场景)

如果不需要拆分邮箱主次,可直接用STRING_AGG函数拼接所有邮箱:

SELECT
    m.id,
    m.merchantID,
    m.name,
    m.LegalName,
    STRING_AGG(c.EmailAddress, ';') AS AllEmailAddresses, -- 分号分隔所有邮箱,可自定义分隔符
    m.MCCRecord,
    m.addressLine1,
    m.addressLine2,
    m.addressLine3,
    m.addressLine4,
    m.city,
    m.phoneNumber
FROM POSH5_Prod_MerchantDetails m
INNER JOIN POSH5_Prod_MerchantContactDetails c 
    ON m.id = c.MerchantRecord
WHERE m.coreBankingRecord = 27 AND m.active = 1
GROUP BY 
    m.id, m.merchantID, m.name, m.LegalName, m.MCCRecord,
    m.addressLine1, m.addressLine2, m.addressLine3, m.addressLine4, m.city, m.phoneNumber

内容的提问来源于stack exchange,提问作者Adil15

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.22 05:54:07