我有以下查询:
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)
为什么第一个查询得到正确的计数而第二个查询却没有?
由于UNION消除了重复的值,因此如果两个计数恰好相同,则将消除一个,并且只对一个求和。尝试UNION ALL改用。
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)