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 for count 发生
• 另一条指令，添加或删除行
• 发生 SELECT for data 突然数组大小错误。
  - 在最糟糕的情况下，这将尝试写入超出数组限制的数据并使我的程序崩溃。

方法 2 是否禁止此问题？

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

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

对于那些询问的人，我正在使用 Native C++ 和上述 SQL 驱动程序（前提是由微软开发。）

Answer 1

如果您使用的是 SQL Server，则在查询后您可以选择 @@RowCount 函数（或者，如果您的结果集可能有超过 20 亿行，请使用 RowCount_Big() 函数）。 这将返回上一个语句选择的行数或受插入/更新/删除语句影响的行数。

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'”，因此结果集中的所有行都将具有相同的 my_table.foo 值，因此该列中的值对于所有行都将相同并且相等（在在本例中）这是查询中的行数。

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

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 中所示。

使用这些隔离级别之一很重要，因为任何其他隔离级别都允许其他客户端创建的新行在当前事务中可见。 阅读有关 设置事务隔离 的 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