問(wèn)題

下面的查詢返回了4排數(shù)據(jù)，這很好。但我同時(shí)需要對(duì)同一個(gè)查詢中的所有計(jì)數(shù)求和。這如何實(shí)現(xiàn)？我已經(jīng)嘗試了多種不同的方法，但只得到sintax報(bào)錯(cuò)。

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;

如果有性能問(wèn)題，確保每個(gè)表都設(shè)置了INDEX(state, level)。（在這種情況下，索引中列的順序并不重要。）

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

2. 將你的子查詢組合起來(lái)，并在它們上面放個(gè)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

這應(yīng)該會(huì)給你輸出一個(gè)單行四列的結(jié)果。

如果你需要在一個(gè)查詢中對(duì)他們進(jìn)行求和，請(qǐng)使用下面這個(gè)查詢。

你可以把它們放進(jìn)一個(gè)select子句中，讓它給你求和。這是一個(gè)例子。

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

歡迎來(lái)弦圈一起翻譯StackOverflow等國(guó)外編程內(nèi)容👇👇👇

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