SQL Server如何优先查询非空邮箱客户联系人 无则返回CONTACT_VC=1记录

表结构说明 #

涉及两张业务表，核心字段如下：

• CUSTOMER 表：ID（客户主键）、CUSTOMERNAME（客户名称）
• CUSTOMER_CONTACT 表：ID（联系人主键）、CUSTOMER_ID（关联客户表的外键）、CONTACT_VC（同客户下唯一的联系人条目编号）、EMAIL（邮箱字段，允许为NULL或空字符串）

需求逻辑 #

每个客户仅返回1条联系人记录，优先级规则：

1. 优先取CONTACT_VC最小的、邮箱不为空且不为NULL的联系人记录
2. 若该客户所有联系人都无有效邮箱，返回CONTACT_VC = 1的联系人记录

通用实现方案（支持MySQL 8+/PostgreSQL/Oracle/SQL Server等所有支持窗口函数的数据库） #

使用窗口函数自定义排序后取每组第一条，性能和可读性最优：

WITH ranked_contacts AS (
    SELECT
        cc.*,
        ROW_NUMBER() OVER (
            PARTITION BY cc.CUSTOMER_ID
            ORDER BY
                -- 有有效邮箱的记录排最前
                CASE WHEN cc.EMAIL IS NOT NULL AND TRIM(cc.EMAIL) != '' THEN 1 ELSE 2 END ASC,
                -- 同优先级按条目编号升序，保证取到最小编号的符合要求记录
                cc.CONTACT_VC ASC
        ) AS rank_num
    FROM CUSTOMER_CONTACT cc
)
SELECT
    c.ID AS customer_id,
    c.CUSTOMERNAME AS customer_name,
    rc.ID AS contact_id,
    rc.CONTACT_VC AS contact_vc,
    rc.EMAIL AS contact_email
FROM CUSTOMER c
LEFT JOIN ranked_contacts rc
    ON c.ID = rc.CUSTOMER_ID
    AND rc.rank_num = 1;

MySQL 5.x 兼容实现（不支持窗口函数场景） #

SELECT
    c.ID AS customer_id,
    c.CUSTOMERNAME AS customer_name,
    cc.ID AS contact_id,
    cc.CONTACT_VC AS contact_vc,
    cc.EMAIL AS contact_email
FROM CUSTOMER c
LEFT JOIN CUSTOMER_CONTACT cc ON c.ID = cc.CUSTOMER_ID
WHERE
    -- 匹配有有效邮箱的最小编号联系人
    (
        TRIM(cc.EMAIL) != ''
        AND cc.CONTACT_VC = (
            SELECT MIN(CONTACT_VC)
            FROM CUSTOMER_CONTACT
            WHERE CUSTOMER_ID = c.ID AND TRIM(EMAIL) != ''
        )
    )
    -- 无有效邮箱时匹配CONTACT_VC=1的联系人
    OR (
        NOT EXISTS (
            SELECT 1
            FROM CUSTOMER_CONTACT
            WHERE CUSTOMER_ID = c.ID AND TRIM(EMAIL) != ''
        )
        AND cc.CONTACT_VC = 1
    );

注意事项 #

• 代码中加入了TRIM()处理邮箱前后空格的场景，若业务里空格也属于有效邮箱可直接删除
• 若不需要返回客户名称，可直接在ranked_contacts CTE中过滤rank_num = 1，无需关联CUSTOMER表

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