一尘不染

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

sql

我有以下查询:

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)

为什么第一个查询得到正确的计数而第二个查询却没有?


阅读 142

收藏
2021-03-08

共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)
2021-03-08