Oracle SQL行转列查询如何避免MAX函数混合匹配联系人和邮箱

解决方案 #

核心通过窗口排序函数先对同应用、同类型的联系人去重，保证后续聚合时联系人和邮箱取自同一行记录，最终输出每个应用仅一行数据，完全兼容标准SQL无需PL/SQL。

通用标准SQL写法（兼容所有支持窗口函数的数据库） #

-- 步骤1：给同应用、同类型的联系人分组排序，每组仅保留1条记录
WITH ranked_contacts AS (
    SELECT 
        logical_name,
        type,
        contact,
        email,
        ROW_NUMBER() OVER(PARTITION BY logical_name, type ORDER BY contact) AS rn
        -- 若有指定取数规则（如取最新提交的联系人），可将ORDER BY后的字段替换为对应字段，例：ORDER BY create_time DESC
    FROM contact_table
    WHERE type IN (
        'APPLICATION SUPPORT OWNER',
        'APPLICATION SUPPORT - PRIMARY',
        'BUSINESS CONTACT - PRIMARY'
    )
),
deduplicated_contacts AS (
    SELECT logical_name, type, contact, email
    FROM ranked_contacts
    WHERE rn = 1
)
-- 步骤2：行转列输出所需结构
SELECT 
    logical_name,
    MAX(CASE WHEN type='APPLICATION SUPPORT OWNER' THEN contact END) Application_Support_Owner,
    MAX(CASE WHEN type='APPLICATION SUPPORT OWNER' THEN email END) App_Support_Owner_EMAIL,
    MAX(CASE WHEN type='APPLICATION SUPPORT - PRIMARY' THEN contact END) Application_Support_Primary,
    MAX(CASE WHEN type='APPLICATION SUPPORT - PRIMARY' THEN email END) App_Support_Primary_EMAIL,
    MAX(CASE WHEN type='BUSINESS CONTACT - PRIMARY' THEN contact END) Business_Contact_Primary,
    MAX(CASE WHEN type='BUSINESS CONTACT - PRIMARY' THEN email END) Business_Contact_Primary_EMAIL
FROM deduplicated_contacts
GROUP BY logical_name

Oracle专属简化写法 #

使用Oracle原生KEEP聚合函数，无需CTE即可保证字段取自同一行：

SELECT 
    logical_name,
    MAX(contact) KEEP(DENSE_RANK FIRST ORDER BY contact) FILTER(WHERE type='APPLICATION SUPPORT OWNER') AS Application_Support_Owner,
    MAX(email) KEEP(DENSE_RANK FIRST ORDER BY contact) FILTER(WHERE type='APPLICATION SUPPORT OWNER') AS App_Support_Owner_EMAIL,
    MAX(contact) KEEP(DENSE_RANK FIRST ORDER BY contact) FILTER(WHERE type='APPLICATION SUPPORT - PRIMARY') AS Application_Support_Primary,
    MAX(email) KEEP(DENSE_RANK FIRST ORDER BY contact) FILTER(WHERE type='APPLICATION SUPPORT - PRIMARY') AS App_Support_Primary_EMAIL,
    MAX(contact) KEEP(DENSE_RANK FIRST ORDER BY contact) FILTER(WHERE type='BUSINESS CONTACT - PRIMARY') AS Business_Contact_Primary,
    MAX(email) KEEP(DENSE_RANK FIRST ORDER BY contact) FILTER(WHERE type='BUSINESS CONTACT - PRIMARY') AS Business_Contact_Primary_EMAIL
FROM contact_table
GROUP BY logical_name

说明 #

• 两种写法输出的结构和你原有的子查询完全一致，可以直接替换原有子查询和应用表关联，不会出现一个应用对应多条记录的问题
• 同类型下仅保留第一条记录的规则可自定义，仅需修改ORDER BY后的字段即可

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