从数据库中的表中选择计数的总和 [英] Selecting Sum of the Count From Tables in a DB

问题描述

我有以下查询：

SELECT SUM(count) 
  FROM (SELECT 'artist' AS table_name, COUNT(*) as count FROM artist
        UNION
        SELECT 'persons' AS table_name, COUNT(*) as count FROM persons
        UNION
        SELECT 'track' AS table_name, COUNT(*) as count FROM track)

它按预期工作，并返回正确的计数。但现在当我做以下查询我得到不正确的计数：

It works as expected and returns the proper count. But now when I do the following query I get the incorrect count:

SELECT SUM(count) 
  FROM (SELECT COUNT(*) as count FROM artist
        UNION
        SELECT COUNT(*) as count FROM persons
        UNION
        SELECT COUNT(*) as count FROM track)

为什么第一个查询获得正确的计数，第二个查询没有？

Why is it the first query gets the proper count and the second one does not?

推荐答案

UNION 可消除重复值，相同，一个被消除，只有一个被相加。尝试改用 UNION ALL 。

The UNION eliminates duplicate values, so if two counts happen to be the same, one is eliminated and only one is summed. Try using UNION ALL instead.

SELECT sum(count) FROM
(SELECT COUNT(*) as count FROM artist
UNION ALL
SELECT COUNT(*) as count FROM persons
UNION ALL
SELECT COUNT(*) as count FROM track)

这篇关于从数据库中的表中选择计数的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！