SQL 从多个表中选择 count(*)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/606234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select count(*) from multiple tables
提问by user73118
How can I select count(*)from two different tables (call them tab1and tab2) having as result:
如何count(*)从两个不同的表中进行选择（调用它们tab1和tab2），结果如下：
Count_1 Count_2
123 456
I've tried this:
我试过这个：
select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2
But all I have is:
但我所拥有的是：
Count_1
123
456
回答by Quassnoi
SELECT (
SELECT COUNT(*)
FROM tab1
) AS count1,
(
SELECT COUNT(*)
FROM tab2
) AS count2
FROM dual
回答by dincerm
As additional information, to accomplish same thing in SQL Server, you just need to remove the "FROM dual" part of the query.
作为附加信息，要在 SQL Server 中完成相同的操作，您只需删除查询的“FROM dual”部分。
回答by Mike Woodhouse
Just because it's slightly different:
只是因为它略有不同：
SELECT 'table_1' AS table_name, COUNT(*) FROM table_1
UNION
SELECT 'table_2' AS table_name, COUNT(*) FROM table_2
UNION
SELECT 'table_3' AS table_name, COUNT(*) FROM table_3
It gives the answers transposed (one row per table instead of one column), otherwise I don't think it's much different. I think performance-wise they should be equivalent.
它给出了转置的答案（每表一行而不是一列），否则我认为它没有太大不同。我认为在性能方面它们应该是等效的。
回答by Nic Wise
My experience is with SQL Server, but could you do:
我的经验是使用 SQL Server，但您可以这样做：
select (select count(*) from table1) as count1,
(select count(*) from table2) as count2
In SQL Server I get the result you are after.
在 SQL Server 中，我得到了您想要的结果。
回答by David Aldridge
Other slightly different methods:
其他略有不同的方法：
with t1_count as (select count(*) c1 from t1),
t2_count as (select count(*) c2 from t2)
select c1,
c2
from t1_count,
t2_count
/
select c1,
c2
from (select count(*) c1 from t1) t1_count,
(select count(*) c2 from t2) t2_count
/
回答by Jimmy Stenke
As I can't see any other answer bring this up.
因为我看不到任何其他答案提出这个问题。
Ifyou don't like sub-queries andhave primary keys in each table you can do this:
如果您不喜欢子查询并且在每个表中都有主键，您可以这样做：
select count(distinct tab1.id) as count_t1,
count(distinct tab2.id) as count_t2
from tab1, tab2
But performance wise I believe that Quassnoi's solution is better, and the one I would use.
但在性能方面，我相信 Quassnoi 的解决方案更好，也是我会使用的解决方案。
回答by Jimmy Stenke
SELECT (SELECT COUNT(*) FROM table1) + (SELECT COUNT(*) FROM table2) FROM dual;
SELECT (SELECT COUNT(*) FROM table1) + (SELECT COUNT(*) FROM table2) FROM dual;
回答by Fadzil
Here is from me to share
这是我的分享
Option 1 - counting from same domain from different table
选项 1 - 从不同表的相同域计数
select distinct(select count(*) from domain1.table1) "count1", (select count(*) from domain1.table2) "count2"
from domain1.table1, domain1.table2;
Option 2 - counting from different domain for same table
选项 2 - 从不同域为同一张表计数
select distinct(select count(*) from domain1.table1) "count1", (select count(*) from domain2.table1) "count2"
from domain1.table1, domain2.table1;
Option 3 - counting from different domain for same table with "union all" to have rows of count
选项 3 - 使用“union all”从不同域对同一个表进行计数以获得计数行
select 'domain 1'"domain", count(*)
from domain1.table1
union all
select 'domain 2', count(*)
from domain2.table1;
Enjoy the SQL, I always do :)
享受 SQL，我总是这样做 :)
回答by Vikas Kumar
select
t1.Count_1,t2.Count_2
from
(SELECT count(1) as Count_1 FROM tab1) as t1,
(SELECT count(1) as Count_2 FROM tab2) as t2
回答by Jens Schauder
select (select count(*) from tab1) count_1, (select count(*) from tab2) count_2 from dual;
