如何连接3张表并将单个员工数据无冗余显示在一行

你的查询之所以返回4条冗余记录，核心原因是position和knowledge两张表都与staff是一对多关联，但两者之间没有任何关联条件，导致每个职位条目都会和每个证书条目进行笛卡尔积配对，最终产生职位数量×证书数量的冗余行。

要实现将单个员工的所有职位、证书数据横向展示在同一行的需求，需要先分别对position和knowledge的多值数据进行聚合，再关联到staff表。以下是不同数据库环境下的具体实现方案：

MySQL/MariaDB 实现 #

利用GROUP_CONCAT函数将同一员工的所有职位、证书信息拼接成字符串，同时用LEFT JOIN确保员工无职位或证书时仍能返回数据：

SELECT 
    st.uid,
    st.surname,
    -- 格式化为：职位名(起始-结束), 职位名(起始-结束)...
    GROUP_CONCAT(CONCAT(pos.role, '(', pos.`from`, ' - ', pos.`to`, ')') SEPARATOR ', ') AS roles,
    GROUP_CONCAT(CONCAT(knw.certificate, '(', knw.`from`, ' - ', knw.`to`, ')') SEPARATOR ', ') AS certificates
FROM staff st
LEFT JOIN position pos ON st.uid = pos.uid
LEFT JOIN knowledge knw ON st.uid = knw.uid
WHERE st.uid = '1234'
GROUP BY st.uid, st.surname;

PostgreSQL 实现 #

使用PostgreSQL原生的STRING_AGG函数完成聚合：

SELECT 
    st.uid,
    st.surname,
    STRING_AGG(CONCAT(pos.role, '(', pos."from", ' - ', pos."to", ')'), ', ') AS roles,
    STRING_AGG(CONCAT(knw.certificate, '(', knw."from", ' - ', knw."to", ')'), ', ') AS certificates
FROM staff st
LEFT JOIN position pos ON st.uid = pos.uid
LEFT JOIN knowledge knw ON st.uid = knw.uid
WHERE st.uid = '1234'
GROUP BY st.uid, st.surname;

SQL Server 实现 #

2017及以上版本（支持STRING_AGG） #

SELECT 
    st.uid,
    st.surname,
    STRING_AGG(CONCAT(pos.role, '(', pos.[from], ' - ', pos.[to], ')'), ', ') AS roles,
    STRING_AGG(CONCAT(knw.certificate, '(', knw.[from], ' - ', knw.[to], ')'), ', ') AS certificates
FROM staff st
LEFT JOIN position pos ON st.uid = pos.uid
LEFT JOIN knowledge knw ON st.uid = knw.uid
WHERE st.uid = '1234'
GROUP BY st.uid, st.surname;

2016及以下版本（用STUFF+FOR XML PATH） #

SELECT 
    st.uid,
    st.surname,
    -- 聚合职位信息
    STUFF((
        SELECT ', ' + CONCAT(pos.role, '(', pos.[from], ' - ', pos.[to], ')')
        FROM position pos
        WHERE pos.uid = st.uid
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS roles,
    -- 聚合证书信息
    STUFF((
        SELECT ', ' + CONCAT(knw.certificate, '(', knw.[from], ' - ', knw.[to], ')')
        FROM knowledge knw
        WHERE knw.uid = st.uid
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS certificates
FROM staff st
WHERE st.uid = '1234';

进阶：结构化JSON输出 #

如果需要更便于后续程序处理的结构化输出，可以用JSON聚合函数，以MySQL为例：

SELECT 
    st.uid,
    st.surname,
    JSON_ARRAYAGG(JSON_OBJECT('role', pos.role, 'from', pos.`from`, 'to', pos.`to`)) AS roles,
    JSON_ARRAYAGG(JSON_OBJECT('certificate', knw.certificate, 'from', knw.`from`, 'to', knw.`to`)) AS certificates
FROM staff st
LEFT JOIN position pos ON st.uid = pos.uid
LEFT JOIN knowledge knw ON st.uid = knw.uid
WHERE st.uid = '1234'
GROUP BY st.uid, st.surname;

内容的提问来源于stack exchange，提问作者Sailor Moon