在 SELECT 语句后需要行计数：最佳 SQL 方法是什么？

Question

我试图从一个表中选择一列（没有联接），我需要行数的计数，最好是在开始检索行之前。我已经找到了两种方法来提供我需要的信息。

方法1:

SELECT COUNT( my_table.my_col ) AS row_count
  FROM my_table
 WHERE my_table.foo = 'bar'

然后

SELECT my_table.my_col
  FROM my_table
 WHERE my_table.foo = 'bar'

或方法2

SELECT my_table.my_col, ( SELECT COUNT ( my_table.my_col )
                            FROM my_table
                           WHERE my_table.foo = 'bar' ) AS row_count
  FROM my_table
 WHERE my_table.foo = 'bar'

我这样做是因为我的SQL驱动程序（SQL Native Client 9.0）不允许我在SELECT语句上使用SQLRowCount，但在向其分配信息之前，我需要知道结果中的行数，以便分配数组。不幸的是，在我的程序的这一领域，使用动态分配的容器不是一个选项。

我担心可能会出现以下情况：

• 发生计数的SELECT
• 出现另一条指令，添加或删除一行
• 发生数据SELECT，突然数组大小错误
  -在更糟糕的情况下，这将试图写入超出数组限制的数据，并使我的程序崩溃

方法2是否禁止此问题？

另外，两种方法中的一种会更快吗？如果是，哪个？

最后，我是否应该考虑一种更好的方法（也许是指示驱动程序使用SQLRowCount返回SELECT结果中的行数的方法？）

对于那些询问的人，我使用的是带有上述SQL驱动程序（由Microsoft提供）的Native C++。

Answer 1

如果您使用的是SQL Server，则在查询后可以选择@@RowCount函数（或者如果您的结果集可能有超过20亿行，则使用RowCount_Big（）函数）。这将返回上一条语句所选的行数或insert/update/delete语句所影响的行数。

SELECT my_table.my_col
  FROM my_table
 WHERE my_table.foo = 'bar'
SELECT @@Rowcount

或者，如果您希望类似于方法#2发送的结果中包含行计数，则可以使用OVER子句。

SELECT my_table.my_col,
    count(*) OVER(PARTITION BY my_table.foo) AS 'Count'
  FROM my_table
 WHERE my_table.foo = 'bar'

使用OVER子句将比使用子查询来获取行计数具有更好的性能。使用@@RowCount将具有最佳性能，因为select@@RowCount语句不会有任何查询成本

更新以回应评论：我给出的示例将给出分区中的行数-在本例中由"partition by my_table.foo"定义。每行中的列值是具有相同my_table.foo值的行数。由于您的示例查询有子句"WHERE my_table_foo='bar'"，结果集中的所有行都将具有相同的mytable.foo值，因此列中的值对于所有行都是相同的，并且等于（在本例中）查询中的行数。

下面是一个更好/更简单的例子，说明如何在每一行中包含一列，即结果集中的行总数。只需删除可选的PartitionBy子句。

SELECT my_table.my_col, count(*) OVER() AS 'Count'
  FROM my_table
 WHERE my_table.foo = 'bar'

Answer 2

只有两种方法可以100%确定COUNT(*)和实际查询将给出一致的结果：

• 将COUNT(*)与查询组合在一起，如方法2中所示。我推荐您在示例中显示的表单，而不是kogus注释中显示的相关子查询表单
• 在SNAPSHOT或SERIALIZABLE隔离级别中启动事务后，使用两个查询，如方法1所示

使用其中一个隔离级别非常重要，因为任何其他隔离级别都允许其他客户端创建的新行在当前事务中可见。有关更多详细信息，请阅读SET TRANSACTION ISOLATION上的MSDN文档。

Answer 3

方法2将始终返回与结果集匹配的计数。

不过，我建议您将子查询链接到外部查询，以确保计数上的条件与数据集上的条件匹配。

SELECT 
  mt.my_row,
 (SELECT COUNT(mt2.my_row) FROM my_table mt2 WHERE mt2.foo = mt.foo) as cnt
FROM my_table mt
WHERE mt.foo = 'bar';

Answer 4

如果您担心满足条件的行数可能会在执行查询和检索结果后的几毫秒内发生变化，您可以/应该在事务中执行查询：

BEGIN TRAN bogus
SELECT COUNT( my_table.my_col ) AS row_count
FROM my_table
WHERE my_table.foo = 'bar'
SELECT my_table.my_col
FROM my_table
WHERE my_table.foo = 'bar'
ROLLBACK TRAN bogus

这将始终返回正确的值。

此外，如果您使用SQL Server，您可以使用@ROWCOUNT来获取受上一条语句影响的行数，并将real查询的输出重定向到临时表或表变量，这样您就可以完全返回所有内容，而不需要事务：

DECLARE @dummy INT
SELECT my_table.my_col
INTO #temp_table
FROM my_table
WHERE my_table.foo = 'bar'
SET @dummy=@@ROWCOUNT
SELECT @dummy, * FROM #temp_table

Answer 5

以下是一些想法：

• 使用方法#1，调整数组的大小以容纳额外的结果，或者使用一个根据需要自动调整大小的类型（你没有提到你使用的是什么语言，所以我再具体不过了）
• 如果数据库支持，您可以在事务中执行方法#1中的两条语句，以确保两次计数相同
• 我不确定你对数据做了什么，但如果可以在不首先存储所有结果的情况下处理结果，这可能是最好的方法

Answer 6

如果您真的担心您的行数会在select计数和select语句之间发生变化，为什么不先将您的行选择到临时表中呢？这样，你就知道你会同步。

Answer 7

为什么不将结果放入向量中？这样你就不必事先知道尺寸了。

Answer 8

您可能需要考虑一种更好的模式来处理这种类型的数据。

在返回行之前，没有一个自预检测SQL驱动程序会告诉您查询将返回多少行，因为答案可能会改变（除非您使用事务，否则会产生自己的问题）

行数不会改变-用谷歌搜索ACID和SQL。

Answer 9

IF (@@ROWCOUNT > 0)
BEGIN
SELECT my_table.my_col
  FROM my_table
 WHERE my_table.foo = 'bar'
END

Answer 10

只是添加了这个，因为这是谷歌上这个问题的最高结果。在sqlite中，我用它来获取行数。

WITH temptable AS
  (SELECT one,two
   FROM
     (SELECT one, two
      FROM table3
      WHERE dimension=0
      UNION ALL SELECT one, two
      FROM table2
      WHERE dimension=0
      UNION ALL SELECT one, two
      FROM table1
      WHERE dimension=0)
   ORDER BY date DESC)
SELECT *
FROM temptable
LEFT JOIN
  (SELECT count(*)/7 AS cnt,
                        0 AS bonus
   FROM temptable) counter
WHERE 0 = counter.bonus