MySQL联盟，表优先级和假列
我试图查询3个主要不相关的表。所有三个的关系是一列（电子邮件地址字段）。我创建了一个'union all'并从所有3个表中提取电子邮件，然后在最后分组以确保没有重复项（我也尝试过union）。
我的问题是它一切正常，但我需要知道第一个表上的每个电子邮件地址是1,0还是NULL。我已尝试拉入列，然后将“”AS Col1“添加到其他表中（避免错误1222，匹配列）。我可以这样做，但表1中的数据被table2中的'nothing'覆盖，然后表3中的任何内容都被覆盖。
这是查询：
SELECT * FROM (
SELECT email AS emails, COUNT(email) AS qty, '' AS custCountry, '' AS custID, '' AS cName, active AS newsletter
FROM newsletter
WHERE email != ''
GROUP BY email
UNION ALL
SELECT email AS emails, COUNT(email) AS qty, '' AS custCountry, '' AS custID, '' AS cName, '' AS newsletter
FROM orders
WHERE email != ''
GROUP BY email
UNION ALL
SELECT email AS emails, COUNT(email) AS qty, country AS custCountry, customerID AS custID, countries.name AS cName, '' AS newsletter
FROM customers
LEFT JOIN countries ON customers.country = countries.zoneID
WHERE email != ''
GROUP BY email
) AS newtable
GROUP BY emails
ORDER BY qty DESC
没有找到相关结果
已邀请:
2 个回复
锑寝粒
select email from A union select email from B union select email from C查询，您只能在select子句中包含那些列在group by子句中的列，或者是聚合函数，如MySQL不会为此抛出错误，但会在无效的colu中显示一些随机值。其他数据库，即Oracle会抛出错误。公藕