問題

下面的查詢返回了4排數(shù)據(jù)，這很好。但我同時需要對同一個查詢中的所有計數(shù)求和。這如何實現(xiàn)？我已經(jīng)嘗試了多種不同的方法，但只得到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 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)。（在這種情況下，索引中列的順序并不重要。）

如果這只是許多困難的查詢方法中的一個，您可能需要重新考慮給數(shù)據(jù)配備多個數(shù)據(jù)庫（模式）。

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

這應該會給你輸出一個單行四列的結(jié)果。

如果你需要在一個查詢中對他們進行求和，請使用下面這個查詢。

你可以把它們放進一個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等國外編程內(nèi)容👇👇👇

翻譯原文：mysql中用一個查詢獲取多個數(shù)據(jù)庫（模式）和表的計數(shù)之和
更多數(shù)據(jù)庫相關(guān)翻譯內(nèi)容：數(shù)據(jù)庫 - 弦圈
更多其他趣味內(nèi)容：弦圈 - 找到屬于你的圈子