代码之家  ›  专栏  ›  技术社区  ›  antik

select语句后需要行数:什么是最佳SQL方法?

  •  31
  • antik  · 技术社区  · 16 年前

    我正试图从一个表中选择一列(没有联接),我需要行数的计数,最好是在开始检索行之前。我采用了两种方法来提供我需要的信息。

    方法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,但我需要知道结果中的行数,以便在为数组分配信息之前分配数组。不幸的是,在我的程序的这个领域,动态分配容器的使用不是一个选项。

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

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

    方法2是否禁止此问题?

    另外,这两种方法中的一种会更快吗?如果是,那是哪一个?

    最后,是否有更好的方法需要考虑(也许是一种指示驱动程序使用sqlrowcount返回选择结果中的行数的方法?)

    对于那些被问过的人,我使用的是原生C++,前面提到的SQL驱动程序(微软提供)。

    10 回复  |  直到 10 年前
        1
  •  16
  •   Bill Karwin    16 年前

    只有两种方法可以100%确定 COUNT(*) 实际查询结果一致:

    • 结合了 伯爵(*) 使用查询,如方法2所示。我推荐您在示例中显示的表单,而不是Kogus注释中显示的相关子查询表单。
    • SNAPSHOT SERIALIZABLE 隔离等级。

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

        2
  •  28
  •   Adam Porad    16 年前

    如果使用的是SQL Server,则在查询后可以选择@@rowcount函数(或者如果结果集的行数可能超过20亿,则使用bigrow_count()函数)。这将返回上一条语句选择的行数或受insert/update/delete语句影响的行数。

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

    或者,如果希望在类似于方法2发送的结果中包含行计数,则可以使用over子句(请参见 http://msdn.microsoft.com/en-us/library/ms189461.aspx 1 )

    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相同的值,因此列中的值对于所有行都将相同,并且(在本例中)等于查询中的行。

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

    SELECT my_table.my_col, count(*) OVER() AS 'Count'
      FROM my_table
     WHERE my_table.foo = 'bar'
    
        3
  •  3
  •   JosephStyons    16 年前

    方法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';
    
        4
  •  3
  •   Joe Pineda    16 年前

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

    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获取受上一语句影响的行数,并重定向 真实的 查询一个临时表或表变量,这样就可以完全返回所有内容,而不需要事务处理:

    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
    
        5
  •  1
  •   Robert Gamble    16 年前

    以下是一些想法:

    • 使用方法1并调整数组的大小以保存其他结果,或者使用自动调整大小的类型(您没有提到您使用的语言,因此我不能更具体)。
    • 您可以在事务中的方法1中执行这两个语句,以确保如果您的数据库支持这一点,则两次计数相同。
    • 我不确定您对数据做了什么,但是如果可以在不首先存储所有数据的情况下处理结果,这可能是最好的方法。
        6
  •  1
  •   BoltBait    16 年前

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

        7
  •  0
  •   jonnii    16 年前

    你为什么不把你的结果放到一个向量上呢?这样你就不用事先知道尺寸了。

        8
  •  0
  •   dkretz    16 年前

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

    在返回行之前,没有一个自我预检的SQL驱动程序会告诉您查询将返回多少行,因为答案可能会更改(除非您使用事务,否则事务本身会产生问题)。

    行数不会改变-google代表acid和sql。

        9
  •  0
  •   Deepfreezed    14 年前
    IF (@@ROWCOUNT > 0)
    BEGIN
    SELECT my_table.my_col
      FROM my_table
     WHERE my_table.foo = 'bar'
    END
    
        10
  •  0
  •   Tschallacka    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