MySQL UNION ALL 只从每个表返回一条记录

Question

以下内容仅返回问题表中的一条记录和讨论表中的一条记录。这两个表中都不止一个。

如何让它返回所有结果？

SELECT d.discussion_id AS id, 
d.discussion_google_id AS user_id, 
d.discussion_title AS title, 
d.discussion_text AS text, 
d.discussion_views AS views, 
d.discussion_last_view_ip AS last_ip, 
d.discussion_created AS created, 
d.discussion_updated AS updated,
u.google_picture_link AS picture_link,
sum(v.vote_amount) AS votes_total
FROM discussions AS d
INNER JOIN google_users AS u
ON u.google_id = d.discussion_google_id
LEFT JOIN votes AS v
ON v.vote_discussion_id = d.discussion_id
WHERE d.discussion_deleted = 0
UNION ALL
SELECT q.question_id AS id, 
q.question_google_id AS user_id, 
q.question_title AS title, 
q.question_text AS text, 
q.question_views AS views, 
q.question_last_view_ip AS last_ip, 
q.question_created AS created, 
q.question_updated AS updated,
u.google_picture_link AS picture_link,
sum(v.vote_amount) AS votes_total
FROM questions AS q
INNER JOIN google_users AS u
ON u.google_id = q.question_google_id
LEFT JOIN votes AS v
ON v.vote_question_id = q.question_id
WHERE q.question_deleted = 0
ORDER BY votes_total

Answer 1

当你在 select 中使用普通列和聚合函数时，你应该使用 GROUP BY 子句，否则 MySQL 只返回第一行。