sql - 从数据库中的表中选择计数的总和

Question

我有以下查询:

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)

它按预期工作并返回正确的计数。但是现在当我执行以下查询时，我得到了不正确的计数:

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

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

Answer 1

UNION消除重复值，因此如果两个计数碰巧相同，则消除一个并仅对一个求和。尝试使用 UNION ALL反而。

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)