提问人：Wian Crous 提问时间：11/28/2022 更新时间：11/28/2022 访问量：13
比较两个MySQL表中的值
Compare Values from two MySQL tables
问：
当前方法：
当前查询 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 - 输出
当前查询 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 - 输出
期望输出
基本上将两个输出添加到一个视图中，如果“用户 ID”匹配，则添加值。有人可以帮忙吗？
答：
0赞
Akina
11/28/2022
#1
以桌子为基础。LEFT JOIN 两个查询都加入到它。筛选至少有一个子查询行匹配的行。生成输出列列表。master_access.accounts
SELECT * -- build needed columns list
FROM master_access.accounts
LEFT JOIN ( {query 1 text} ) AS subquery1 USING (uid)
LEFT JOIN ( {query 2 text} ) AS subquery2 USING (uid)
WHERE COALESCE (subquery1.uid, subquery2.uid) IS NOT NULL
在输出列表中 - 采用不带表别名的列，而不是所有其他列。uid
评论
0赞
Wian Crous
11/29/2022
所以我的查询最终看起来像这样：
0赞
Wian Crous
11/29/2022
它在以下情况下有效： 但在以下情况下它不起作用：SELECT acc.user,acc.uid -- build needed columns list FROM master_access.accounts AS acc LEFT JOIN ( {query 1 text} ) AS subquery1 USING (uid) LEFT JOIN ( {query 2 text} ) AS subquery2 USING (uid) WHERE COALESCE (subquery1.uid, subquery2.uid) IS NOT NULLSELECT acc.user,subquery1.contact_fname -- build needed columns list FROM master_access.accounts acc LEFT JOIN ( {query 1 text} ) AS subquery1 USING (uid) LEFT JOIN ( {query 2 text} ) AS subquery2 USING (uid) WHERE COALESCE (subquery1.uid, subquery2.uid) IS NOT NULL
0赞
Wian Crous
11/29/2022
没关系，我想通了谢谢：谢谢你的帮助SELECT acc.user,COALESCE(sq1.contact_fname,sq2.contact_fname),COALESCE(sq1.contact_lname,sq2.contact_lname).....
评论