mysql - 在 MySQL 查询中进行一系列联合选择后无法创建总计行

Question

在我们的后端，我们将居住在特定国家/地区的用户及其相关数据分为不同的数据源。例如，有一个数据源是新加坡用户及其交易数据，另一个数据源是日本用户及其交易数据，等等。

我在 Redash 中创建了一个查询，该查询构建了两列数据——国家/地区名称和用户——并且它按预期工作。但是，我无法按照本文中推荐的方法将总计行添加到底部:http://webdevzoom.com/ultimate-guide-count-subtotal-grand-total-mysql/ .

GROUP BY Country WITH ROLLUP 不会生成总计行；相反，它只是重复最后一行的注册计数。本文第 III.1 节中的建议似乎是最合适的，但我的实现在别名上返回“表 source1.all_signups 不存在”错误。作为引用，Redash 需要在构建查询时选择数据源，我选择的源是“source1”。

查询如下:

SELECT *
FROM
   (SELECT "Country1" AS Country,
          count(id) AS Users
    FROM users
    UNION SELECT "Country2" AS Country,
                count(id) AS Users
    FROM source2.users
    UNION SELECT "Country3" AS Country,
                count(id) AS Users
    FROM source3.st_users
    UNION SELECT "Country4" AS Country,
                count(id) AS Users
    FROM source4.st_users
    ...
    UNION SELECT "CountryN" AS Country,
                count(id) AS Signups
    FROM sourceN.st_users
    GROUP BY Country
    ORDER BY Signups DESC
) AS all_signups
UNION SELECT
   "GRAND TOTAL" AS Country,
    SUM(Signups) AS Signups
FROM all_signups

Answer 1

我能够解决这个问题，并想分享我的答案。关键是使用变量。我还更改了方向，将总计放在查询的顶部(个人偏好)。最终查询如下所示:

SELECT @total_signups := FORMAT(SUM(Signups), 0)
FROM
(
    SELECT
          "Country1" AS Country,
          count(id) AS Signups
    FROM source1.users
    UNION SELECT
          "Country2" AS Country,
          count(id) AS Signups
    FROM source2.users
    ...
    UNION SELECT
          "CountryN" AS Country,
          count(id) AS Signups
    FROM sourceN.st_users
) AS total_calc;

SELECT
   "GRAND TOTAL" AS Country,
    @total_signups AS Signups
UNION SELECT
    Country,
    FORMAT(Signups, 0) AS Signups
FROM
(
    SELECT
          "Country1" AS Country,
          count(id) AS Signups
    FROM source1.users
    UNION SELECT
          "Country2" AS Country,
          count(id) AS Signups
    FROM source2.users
    ...
    UNION SELECT
          "CountryN" AS Country,
          count(id) AS Signups
    FROM sourceN.st_users
    ORDER BY Signups DESC
) AS all_signups;

这是一个野兽般的查询，但它完成了工作!作为引用，我们使用的是 MySQL 5.7。