SQL三表关联查询改造：保留现有结果同时返回table2全量未匹配记录

初始查询与需求 #

最初的查询语句如下，核心逻辑是拉取table3全量记录，通过channel_name关联table2拿到聊天时长、发信标记等字段，通过pid关联table1拿到客户信息，再按渠道、pid、日期分组统计按钮点击次数。

SELECT t1.name, 
       t1.status, 
       t1.pid, 
       TIME(date_time) AS time, 
       t2.channel_name, 
       chattime, 
       email_sent, 
       sms_sent, 
       SUM(CASE WHEN notes = 'email' 
                THEN 1 
                ELSE 0 END) AS email 
FROM       table1 t1 
RIGHT JOIN table3 t3 
        ON t1.pid = t3.pid 
LEFT JOIN table2 t2 
       ON t2.channel_name = t3.channel_name 
GROUP BY t3.channel_name, 
         t1.pid, 
         date 
ORDER BY t3.id DESC;

需求是在保留原有全部返回结果的基础上，额外追加table2中所有channel_name未和table3匹配上的记录，最终结果集不能有重复条目。

补充背景信息 #

该查询用于给PHP报表页面提供数据源，页面效果如下：

三张表的样例数据如下（已隐去隐私姓名信息，从上到下依次为table1、table2、table3）：

报表字段和表字段的映射关系：

• Client（客户）：table1.name
• PID：table1.pid
• Date（日期）：table3.date_time
• Practice email、联系电话、业务领域、申请回呼、表单发送字段：来自t3.inquiry_notes（数据库记录每一次按钮点击行为，inquiry_notes标记点击类型，通过channel_name分组聚合单渠道下的所有点击数据）
• Chat（聊天时长）：table2.chattime
• Email sent（邮件已发送标记）：table2.email_sent
• SMS sent（短信已发送标记）：table2.sms_sent

此前UNION版本的问题 #

之前尝试编写的UNION查询无法正常运行，核心问题有4个：

• 子查询中重复查询channel_name字段，同时返回t2.channel_name和t3.channel_name，会触发字段重名错误，后续分组、关联也无法正确引用字段
• 外层查询引用了不存在的表别名t3，子查询别名设置为t23后，外层不能再直接用t3调用字段
• 右连分支中未匹配到table3的记录t3.id、t3.pid等字段为NULL，UNION去重时会因为这些NULL值和匹配记录的非NULL值不一致，导致重复行无法正常去重
• 没有对table2独有的记录做字段值兜底，分组统计时会因为NULL值出现计算错误

修正后的可用查询 #

直接用两段互斥的数据集做UNION ALL（两段数据无重叠，不需要UNION做额外去重，性能更高），先取所有table3的记录关联对应table2数据，再反查table2中没有匹配到任何table3记录的条目，合并后再关联table1做分组统计即可：

SELECT 
    t1.name,
    t1.chat_status,
    t1.pid,
    TIME(t23.date_time) AS time,
    DATE_FORMAT(DATE(t23.date_time), '%a, %e %M %Y') AS date,
    t23.channel_name,
    t23.chattime,
    t23.email_sent,
    t23.sms_sent,
    SUM(CASE WHEN t23.inquiry_notes = 'email click' THEN 1 ELSE 0 END) AS email,
    SUM(CASE WHEN t23.inquiry_notes = 'phone click' THEN 1 ELSE 0 END) AS phone,
    SUM(CASE WHEN t23.inquiry_notes = 'practice area click' THEN 1 ELSE 0 END) AS practice,
    SUM(CASE WHEN t23.inquiry_notes = 'inquiry form click' THEN 1 ELSE 0 END) AS form,
    SUM(CASE WHEN t23.inquiry_notes NOT LIKE '%click%' THEN 1 ELSE 0 END) AS formsent
FROM (
    -- 第一部分：原逻辑覆盖的所有table3记录，关联匹配到的table2字段
    SELECT 
        t3.id,
        t3.pid,
        t3.channel_name,
        t3.date_time,
        t3.inquiry_notes,
        t2.chattime,
        t2.email_sent,
        t2.sms_sent
    FROM table3 t3
    LEFT JOIN table2 t2 ON t2.channel_name = t3.channel_name
    UNION ALL
    -- 第二部分：table2中未关联到任何table3记录的独立条目
    SELECT 
        NULL AS id,
        NULL AS pid,
        t2.channel_name,
        NULL AS date_time,
        NULL AS inquiry_notes,
        t2.chattime,
        t2.email_sent,
        t2.sms_sent
    FROM table2 t2
    LEFT JOIN table3 t3 ON t2.channel_name = t3.channel_name
    WHERE t3.channel_name IS NULL
) t23
LEFT JOIN table1 t1 ON t1.pid = t23.pid
GROUP BY 
    t23.channel_name,
    t1.pid,
    DATE(t23.date_time)
ORDER BY t23.id DESC;

补充说明 #

• 第二部分数据中属于table3的字段统一填充NULL，外层SUM统计时会自动忽略NULL值，不会影响点击数的计算结果
• 因为两部分数据天然不存在重叠（第二部分是table2完全没匹配到table3的记录），用UNION ALL比UNION少了去重排序的开销，执行效率更高
• 所有外层引用的字段都来自子查询别名t23，不会出现表不存在、字段歧义的问题

内容的提问来源于stack exchange，提问作者Evan Carlstrom