如何合并两条相同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
相关产品推荐
相关产品推荐

