问题
下面的查询返回了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 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)
。(在这种情况下,索引中列的顺序并不重要。)
如果这只是许多困难的查询方法中的一个,您可能需要重新考虑给数据配备多个数据库(模式)。
- 将你的子查询组合起来,并在它们上面放个
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
欢迎来弦圈一起翻译StackOverflow等国外编程内容👇👇👇
翻译原文:mysql中用一个查询获取多个数据库(模式)和表的计数之和
更多数据库相关翻译内容:数据库 - 弦圈
更多其他趣味内容:弦圈 - 找到属于你的圈子