MySQL:希望将这些 UNION 合并在一起

Question

好吧，我头疼...!

这个漂亮的 MySQL 查询:

(SELECT mtwitterfollowers AS twitfollow FROM `media` WHERE media.id=1)
UNION
(SELECT SUM(twitterfollowers) AS twitfollow FROM people LEFT JOIN peoplejoin ON peoplejoin.people_id = people.id LEFT JOIN positions ON position_id = positions.id WHERE peoplejoin.media_id = 1)
UNION 
(SELECT SUM(twitterfollowers) AS twitfollow FROM people LEFT JOIN peoplejoin ON peoplejoin.people_id = people.id LEFT JOIN networkjoin ON networkjoin.network_id = peoplejoin.network_id LEFT JOIN positions ON position_id = positions.id WHERE networkjoin.media_id = 1)

...返回三行漂亮的数字。

理想情况下，我希望此查询返回所有三个“twitfollow”结果，并加在一起。

但是，在它们周围加上一个 SUM 会给我一个关于“每个派生表必须有自己的别名”的错误，我对如何解决这个问题有点困惑。

(当然，我可以在 PHP 中对结果求和；但我假设使用 MySQL 服务器执行此操作会更快。我是对的吗？)

Answer 1

将整个查询用作另一个查询的 FROM 子句:

SELECT SUM(twitfollow) FROM (
    (SELECT mtwitterfollowers AS twitfollow FROM `media` WHERE media.id=1)
    UNION ALL
    (SELECT SUM(twitterfollowers) AS twitfollow FROM people LEFT JOIN peoplejoin ON peoplejoin.people_id = people.id LEFT JOIN positions ON position_id = positions.id WHERE peoplejoin.media_id = 1)
    UNION ALL
    (SELECT SUM(twitterfollowers) AS twitfollow FROM people LEFT JOIN peoplejoin ON peoplejoin.people_id = people.id LEFT JOIN networkjoin ON networkjoin.network_id = peoplejoin.network_id LEFT JOIN positions ON position_id = positions.id WHERE networkjoin.media_id = 1)
) t1

我还将您的 UNION 更改为 UNION ALL 因为您可能不想仅仅因为一个表中的总和等于另一个表中的总和而删除行.