SQL:从多个表中组合Select count(*)

How do you combine multiple select count(*) from different table into one return?

如何将不同表中的多个select count(*)组合为一个返回?

I have a similar sitiuation as this post

我也有类似的意见。

but I want one return.

但我想要一次回报。

I tried Union all but it spit back 3 separate rows of count. How do you combine them into one?

我试了联合所有，但它吐出了3行不同的计数。如何将它们组合成一个呢?

select count(*) from foo1 where ID = '00123244552000258'
union all 
select count(*) from foo2 where ID = '00123244552000258'
union all
select count(*) from foo3 where ID = '00123244552000258'

edit: I'm on MS SQL 2005

编辑:我是SQL女士

8 个解决方案

#1

SELECT 
(select count(*) from foo1 where ID = '00123244552000258')
+
(select count(*) from foo2 where ID = '00123244552000258')
+
(select count(*) from foo3 where ID = '00123244552000258')

This is an easy way.

这是一个简单的方法。

#2

I'm surprised no one has suggested this variation:

我很惊讶没有人提出这样的变化:

SELECT SUM(c)
FROM (
  SELECT COUNT(*) AS c FROM foo1 WHERE ID = '00123244552000258'
  UNION ALL
  SELECT COUNT(*) FROM foo2 WHERE ID = '00123244552000258'
  UNION ALL
  SELECT COUNT(*) FROM foo3 WHERE ID = '00123244552000258'
);

#3

select 
  (select count(*) from foo) as foo
, (select count(*) from bar) as bar
, ...

#4

Basically you do the counts as sub-queries within a standard select.

基本上，您将计数作为标准选择中的子查询。

An example would be the following, this returns 1 row, two columns

下面是一个例子，它返回一行，两列

SELECT
 (SELECT COUNT(*) FROM MyTable WHERE MyCol = 'MyValue') AS MyTableCount,
 (SELECT COUNT(*) FROM YourTable WHERE MyCol = 'MyValue') AS YourTableCount,

#5

You can combine your counts like you were doing before, but then you could sum them all up a number of ways, one of which is shown below:

你可以像以前一样合并你的计数，但是你可以用多种方法对它们进行求和，其中一种方法如下所示:

SELECT SUM(A) 
FROM
(
    SELECT 1 AS A
    UNION ALL 
    SELECT 1 AS A
    UNION ALL
    SELECT 1 AS A
    UNION ALL
    SELECT 1 AS A
) AS B

#6

you could name all fields and add an outer select on those fields:

您可以命名所有字段，并在这些字段上添加一个外部选择:

SELECT A, B, C FROM ( your initial query here ) TableAlias

That should do the trick.

这应该很管用。

#7

select sum(counts) from (
select count(1) as counts from foo 
union all
select count(1) as counts from bar)

#8

For oracle:

oracle:

select( 
select count(*) from foo1 where ID = '00123244552000258'
+
select count(*) from foo2 where ID = '00123244552000258'
+
select count(*) from foo3 where ID = '00123244552000258'
) total from dual;