MySQL 如何对联合查询进行计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11731655/
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
How to do a count on a union query
提问by David542
I have the following query:
我有以下查询：
select distinct profile_id from userprofile_...
union
select distinct profile_id from productions_...
How would I get the count of the total number of results?
我将如何获得结果总数？
回答by Taryn
If you want a total count for all records, then you would do this:
如果你想要所有记录的总数，那么你可以这样做：
SELECT COUNT(*)
FROM
(
select distinct profile_id
from userprofile_...
union all
select distinct profile_id
from productions_...
) x
回答by Gonzalo.-
you should use Union Allif there are equals rows in both tables, because Union makes a distinct
Union All如果两个表中都有相等的行，你应该使用，因为 Union 是一个不同的
select count(*) from
(select distinct profile_id from userprofile_...
union ALL
select distinct profile_id from productions_...) x
In this case, if you got a same Profile_Idin both tables (id is probably a number, so it's possible), then if you use Union, if you got Id = 1in both tables, you will lost one row (it will appear one time instead of two)
在这种情况下，如果你Profile_Id在两个表中得到相同的值（id 可能是一个数字，所以这是可能的），那么如果你使用Union, 如果你Id = 1在两个表中都得到了tables，你将丢失一行（它会出现一次而不是两次）
回答by Bohemian
This will perform pretty well:
这将表现得很好：
select count(*) from (
select profile_id
from userprofile_...
union
select profile_id
from productions_...
) x
The use of unionguarantees distinct values - unionremoves duplicates, union allpreserves them. This means you don't need the distinctkeyword (the other answers don't exploit this fact and end up doing more work).
使用union保证不同的值 -union删除重复项，union all保留它们。这意味着您不需要distinct关键字（其他答案没有利用这个事实并最终做了更多的工作）。
Edited:
编辑：
If you want to total number of different profile_id in each, where given values that appear in both table are considered differentvalues, use this:
如果要计算每个中不同 profile_id 的总数，其中出现在两个表中的给定值被视为不同的值，请使用以下命令：
select sum(count) from (
select count(distinct profile_id) as count
from userprofile_...
union all
select count(distinct profile_id)
from productions_...
) x
This query will out-perform all other answers, because the database can efficiently count distinct values within a table much faster than from the unioned list. The sum()simply adds the two counts together.
此查询将优于所有其他答案，因为数据库可以比联合列表更快地有效地计算表中的不同值。在sum()简单地增加了两项罪名在一起。
回答by Akash KC
As omg ponies has already pointed out that there is no use of using distinct with UNION, you can use UNION ALL in your case.....
正如 omg ponies 已经指出，在 UNION 中使用 distinct 是没有用的，您可以在您的情况下使用 UNION ALL .....
SELECT COUNT(*)
FROM
(
select distinct profile_id from userprofile_...
union all
select distinct profile_id from productions_...
) AS t1
回答by Paulson Peter
Best solution is to add count of two query results. It will not be a problem if the table contains large number of records. And you don't need to use union query. Ex:
最好的解决方案是添加两个查询结果的计数。如果表包含大量记录，则不会有问题。而且您不需要使用联合查询。前任：
SELECT (select COUNT(distinct profile_id) from userprofile_...) +
(select COUNT(distinct profile_id) from productions_...) AS total
回答by Jorge
These will not work if in one of the COUNT(*) the result is equals to 0.
如果在 COUNT(*) 之一中结果等于 0，这些将不起作用。
This will be better:
这样会更好：
SELECT SUM(total)
FROM
(
select COUNT(distinct profile_id) AS total
from userprofile_...
union all
select COUNT(distinct profile_id) AS total
from productions_...
) x
