如何从通过Client code关联的一对多表中查询符合要求的记录

实现方案 #

你需要用窗口函数对相同Client code的行分组排序，再通过条件判断实现仅第一条有有效登录名的行展示联系人姓名，其余行置空，参考SQL如下（兼容MySQL 8.0+/PostgreSQL/SQL Server等支持窗口函数的数据库）：

WITH contact_company AS (
    -- 先关联公司表和联系人表，匹配对应联系人
    SELECT 
        c.`Client code`,
        c.`Company name`,
        c.`Contact Person`,
        ct.`LoginName`,
        -- 对相同Client code分组，按Contact code升序排序
        ROW_NUMBER() OVER (
            PARTITION BY c.`Client code` 
            ORDER BY ct.`Contact code` ASC
        ) AS rn
    FROM company c
    LEFT JOIN contact ct 
        ON c.`Client code` = ct.`Client code` 
        AND c.`Contact Person` = ct.`Client name`
)
SELECT 
    `Client code`,
    `Company name`,
    `Contact Person`,
    -- 仅分组内排序第一、且有登录名的行展示联系人名，其余为NULL
    CASE WHEN rn = 1 AND LoginName IS NOT NULL THEN `Contact Person` ELSE NULL END AS `Have Login`
FROM contact_company
ORDER BY `Client code`, rn;

逻辑说明 #

1. 先通过左连接关联公司表和联系人表，关联条件同时匹配Client code和联系人姓名，保证拿到每个公司对应联系人的登录名信息
2. 用ROW_NUMBER()窗口函数按Client code分组，按联系人ID（Contact code）升序排序，给同客户下的每个公司行分配组内序号
3. 最后通过CASE判断，仅组内序号为1、且登录名不为空的行在Have Login列展示联系人姓名，其余行返回NULL，和预期结果完全匹配
   如果需要关联客户表补充其他字段，只需在CTE里再加一层左连接关联client表的Client code和Company code即可。

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