mysql - SUM Union All作为select语句上的子查询

我有三个表：quiz，comment，question。
它们都有一个名为activity的activityID外键。

我想在单个查询中sum每个表上的条目数量，为所有活动返回各自的sum。

这是我的尝试：

SELECT
    `activity`.`id` AS `activityID`,
    `activity`.`name`,
    (SELECT SUM(`count`) FROM (
        SELECT COUNT(*) AS `count`
        FROM `quiz` WHERE `quiz`.`activityID` = `activity`.`id`

        UNION ALL

        SELECT COUNT(*) AS `count`
        FROM `comment` WHERE `comment`.`activityID` = `activity`.`id`

        UNION ALL

        SELECT COUNT(*) AS `count`
        FROM `question` WHERE `question`.`activityID` = `activity`.`id`
    ) `union`) AS `interactions`
FROM
    `activity`
WHERE 1
    AND `activity`.`eventID` = 1234
GROUP BY `activity`.`id`
ORDER BY `interactions` DESC

它不起作用，因为在嵌套子查询中找不到activity。id。是否可以将此值添加到嵌套子查询范围？甚至更好，是否有一种更简单的方法而无需运行三个单独的子查询并添加它们呢？

这是到目前为止我拥有的最好的解决方案（不使用UNION）：

SELECT
    `activity`.`id` AS `activityID`,
    `activity`.`name`,
    ( SELECT COUNT(*) AS `count`
      FROM `quiz` WHERE `quiz`.`activityID` = `activity`.`id` ) +
    ( SELECT COUNT(*) AS `count`
      FROM `comment` WHERE `comment`.`activityID` = `activity`.`id` ) +
    ( SELECT COUNT(*) AS `count`
      FROM `question` WHERE `question`.`activityID` = `activity`.`id` ) AS `interactions`
FROM
    `activity`
WHERE 1
    AND `activity`.`eventID` = 1234
GROUP BY `activity`.`id`
ORDER BY `interactions` DESC

谢谢。

最佳答案

您可以加入所有表格并获得计数。

SELECT
    `activity`.`id` AS `activityID`,
    `activity`.`name`,
    SUM(I.count) as interactions
FROM
    `activity` A INNER JOIN
    (SELECT `quiz`.`activityID`, COUNT(*) AS `count`
        FROM `quiz`
        GROUP BY `quiz`.`activityID`
        UNION ALL
        SELECT `comment`.`activityID`, COUNT(*) AS `count`
        FROM `comment`
        GROUP BY `comment`.`activityID`
        UNION ALL
        SELECT `question`.`activityID`, COUNT(*) AS `count`
        FROM `question`
        GROUP BY `question`.`activityID`
    ) interactioncounts I on A.id = I.activityID
WHERE 1
    AND `activity`.`eventID` = 1234
GROUP BY `activity`.`id`
ORDER BY `interactions` DESC

关于mysql - SUM Union All作为select语句上的子查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49390900/