合并两张MySQL表查询结果并按用户ID求和的实现方法

当前查询语句1 #

SELECT
a.user,ui.contact_fname, ui.contact_lname,a.uid, COALESCE(pu.name,'N/A') AS UserPolicy,COALESCE(count(ea.user_ack),0) AS ack_count
FROM master_access.accounts a
RIGHT JOIN master_events.events_cleared ea ON a.uid=ea.user_ack
INNER JOIN master_biz.user_information as ui on ui.uid=a.uid
LEFT JOIN `master`.policies_users pu ON (a.template_id=pu.policy_id AND a.template_id!=0)
WHERE YEAR(ea.date_ack)=YEAR(NOW()) and MONTH(ea.date_ack)=10 and a.uid in (120,119,125,128,123,117,122,118,121,127)
GROUP BY a.user
ORDER BY COUNT(ea.user_ack) DESC

当前查询语句1 - 输出结果 #

输出结果展示指定用户在master_events.events_cleared表中10月份的事件确认计数，包含用户基础信息及关联策略。

当前查询语句2 #

SELECT
a.user,ui.contact_fname, ui.contact_lname,a.uid, COALESCE(pu.name,'N/A') AS UserPolicy,COALESCE(count(ea.user_ack),0) AS ack_count
FROM master_access.accounts a
RIGHT JOIN master_events.events_active ea ON a.uid=ea.user_ack
INNER JOIN master_biz.user_information as ui on ui.uid=a.uid
LEFT JOIN `master`.policies_users pu ON (a.template_id=pu.policy_id AND a.template_id!=0)
WHERE YEAR(ea.date_ack)=YEAR(NOW()) and MONTH(ea.date_ack)=10 and a.uid in (120,119,125,128,123,117,122,118,121,127)
GROUP BY a.user
ORDER BY COUNT(ea.user_ack) DESC

当前查询语句2 - 输出结果 #

输出结果展示指定用户在master_events.events_active表中10月份的事件确认计数，包含用户基础信息及关联策略。

期望输出 #

希望将上述两个查询的输出合并为一个视图，当User ID匹配时对ack_count的值进行求和。

实现方案 #

通过UNION ALL合并两个查询的结果集，再在外层按用户唯一标识分组求和，最终创建视图：

CREATE VIEW user_total_ack_counts AS
SELECT
    user,
    contact_fname,
    contact_lname,
    uid,
    UserPolicy,
    SUM(ack_count) AS total_ack_count
FROM (
    -- 第一个查询结果
    SELECT
        a.user,ui.contact_fname, ui.contact_lname,a.uid, COALESCE(pu.name,'N/A') AS UserPolicy,COALESCE(count(ea.user_ack),0) AS ack_count
    FROM master_access.accounts a
    RIGHT JOIN master_events.events_cleared ea ON a.uid=ea.user_ack
    INNER JOIN master_biz.user_information as ui on ui.uid=a.uid
    LEFT JOIN `master`.policies_users pu ON (a.template_id=pu.policy_id AND a.template_id!=0)
    WHERE YEAR(ea.date_ack)=YEAR(NOW()) and MONTH(ea.date_ack)=10 and a.uid in (120,119,125,128,123,117,122,118,121,127)
    GROUP BY a.user, ui.contact_fname, ui.contact_lname, a.uid, UserPolicy

    UNION ALL

    -- 第二个查询结果
    SELECT
        a.user,ui.contact_fname, ui.contact_lname,a.uid, COALESCE(pu.name,'N/A') AS UserPolicy,COALESCE(count(ea.user_ack),0) AS ack_count
    FROM master_access.accounts a
    RIGHT JOIN master_events.events_active ea ON a.uid=ea.user_ack
    INNER JOIN master_biz.user_information as ui on ui.uid=a.uid
    LEFT JOIN `master`.policies_users pu ON (a.template_id=pu.policy_id AND a.template_id!=0)
    WHERE YEAR(ea.date_ack)=YEAR(NOW()) and MONTH(ea.date_ack)=10 and a.uid in (120,119,125,128,123,117,122,118,121,127)
    GROUP BY a.user, ui.contact_fname, ui.contact_lname, a.uid, UserPolicy
) AS combined_data
GROUP BY user, contact_fname, contact_lname, uid, UserPolicy
ORDER BY total_ack_count DESC;

关键说明

1. 用UNION ALL替代UNION，避免自动去重导致计数丢失；
2. 子查询补充完整分组字段，符合SQL标准要求；
3. 外层通过SUM(ack_count)计算同一用户在两个表中的总确认数；
4. 创建的user_total_ack_counts视图可直接用于后续查询。

内容的提问来源于stack exchange，提问作者Wian Crous