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

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)