网站特色怎么写,网站使用费用,威县建设局网站,周口网站seo问题
下面的查询返回了4排数据#xff0c;这很好。但我同时需要对同一个查询中的所有计数求和。这如何实现#xff1f;我已经尝试了多种不同的方法#xff0c;但只得到sintax报错。
SELECT COUNT(*) FROM schema1.table WHERE STATE 17 AND LEVEL 1
UNION ALL
SELECT CO…问题
下面的查询返回了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欢迎来弦圈一起翻译StackOverflow等国外编程内容
翻译原文mysql中用一个查询获取多个数据库模式和表的计数之和 更多数据库相关翻译内容数据库 - 弦圈 更多其他趣味内容弦圈 - 找到属于你的圈子
