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

本文主要是介绍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

回答

只需要输入更少的代码：

SELECT s1, s2, s3, s4,s1 + s2 + s3 + s4 AS totalFROM ( 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)。（在这种情况下，索引中列的顺序并不重要。）

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

将你的子查询组合起来，并在它们上面放个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