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

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 删除了重复的值。尝试使用 UNION ALL，它返回所有行，而不仅仅是唯一的行。

Answer 2

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)

Answer 3

如果您可以忍受近似值，只需一次性计算所有表格

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
   (index_id < 2) --cover both heaps and clustered indexes
   AND
   OBJECT_SCHEMA_NAME (object_id) <> 'sys' --ignore system stuff

这将在一瞬间运行

采用您的原始数据，您可以一次性获得每个表格和整体的计数。

SELECT
    *,
    SUM(count) OVER () AS GrandTotal
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)