在SELECT语句后需要一个行计数:最佳的SQL方法是什么? [英] Need a row count after SELECT statement: what's the optimal SQL approach?

问题描述

我正在尝试从单个表中选择一列(无联接)，并且我需要对行数进行计数，最好是在开始检索行之前.我有两种方法可以提供所需的信息.

I'm trying to select a column from a single table (no joins) and I need the count of the number of rows, ideally before I begin retrieving the rows. I have come to two approaches that provide the information I need.

方法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，但是我需要知道结果中的行数以便在分配信息之前将数组分配给它.不幸的是，在我的程序的这个区域中，动态分配容器的使用不是一种选择.

I am doing this because my SQL driver (SQL Native Client 9.0) does not allow me to use SQLRowCount on a SELECT statement but I need to know the number of rows in my result in order to allocate an array before assigning information to it. The use of a dynamically allocated container is, unfortunately, not an option in this area of my program.

我担心可能会发生以下情况:

I am concerned that the following scenario might occur:

• 选择计数
• 发生另一条指令，添加或删除行
• 发生数据选择，突然数组大小错误.
  -在最坏的情况下，这将尝试写入超出数组限制的数据并使我的程序崩溃.

• SELECT for count occurs
• Another instruction occurs, adding or removing a row
• SELECT for data occurs and suddenly the array is the wrong size.
  -In the worse case, this will attempt to write data beyond the arrays limits and crash my program.

方法2是否禁止此问题?

Does Approach 2 prohibit this issue?

此外，两种方法之一会更快吗?如果是这样，哪个?

Also, Will one of the two approaches be faster? If so, which?

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

Finally, is there a better approach that I should consider (perhaps a way to instruct the driver to return the number of rows in a SELECT result using SQLRowCount?)

对于那些询问的人，我正在使用带有上述SQL驱动程序(由Microsoft提供)的Native C ++.

For those that asked, I am using Native C++ with the aforementioned SQL driver (provided by Microsoft.)

推荐答案

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

There are only two ways to be 100% certain that the COUNT(*) and the actual query will give consistent results:

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

• Combined the COUNT(*) with the query, as in your Approach 2. I recommend the form you show in your example, not the correlated subquery form shown in the comment from kogus.
• Use two queries, as in your Approach 1, after starting a transaction in SNAPSHOT or SERIALIZABLE isolation level.

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

Using one of those isolation levels is important because any other isolation level allows new rows created by other clients to become visible in your current transaction. Read the MSDN documentation on SET TRANSACTION ISOLATION for more details.

这篇关于在SELECT语句后需要一个行计数:最佳的SQL方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！