mysql中用一个查询获取多个数据库（模式）和表的计数之和

问题

下面的查询返回了4排数据，这很好。但我同时需要对同一个查询中的所有计数求和。这如何实现？我已经尝试了多种不同的方法，但只得到sintax报错。

SELECT COUNT(*) FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1

回答

1. 只需要输入更少的代码：

SELECT s1, s2, s3, s4,
       s1 + s2 + s3 + s4 AS total
    FROM ( SELECT
        ( SELECT COUNT(*) FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s1,
        ( SELECT COUNT(*) FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s2,
        ( SELECT COUNT(*) FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s3,
        ( SELECT COUNT(*) FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s4
         ) AS counts;

如果有性能问题，确保每个表都设置了INDEX(state, level)。（在这种情况下，索引中列的顺序并不重要。）

如果这只是许多困难的查询方法中的一个，您可能需要重新考虑给数据配备多个数据库（模式）。

2. 将你的子查询组合起来，并在它们上面放个SELECT子句

SELECT
(SELECT COUNT(*) AS cnt FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1) s1
,
(SELECT COUNT(*) AS cnt FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1) s2
,
(SELECT COUNT(*) AS cnt FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1) s3
,
(SELECT COUNT(*) AS cnt FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1) s4

这应该会给你输出一个单行四列的结果。

如果你需要在一个查询中对他们进行求和，请使用下面这个查询。

你可以把它们放进一个select子句中，让它给你求和。这是一个例子。

SELECT SUM(cnt) FROM
(
SELECT COUNT(*) AS cnt FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) AS cnt FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) AS cnt FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) AS cnt FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1
) tmp

内容来源于Database Administrators Stack Exchange, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接：Obtain the sum of count of multiple schema and tables in one query in mysql