如何使用TOP检索查询的总行数 [英] How to retrieve the total row count of a query with TOP

问题描述

我有一个SQL Server 2008查询

  SELECT TOP 10 * 
 FROM T 
 WHERE。 .. 
 ORDER BY ... 
  

我想获得总数的行。有趣的方法是进行第二次查询

  SELECT COUNT（*）
 FROM T 
 WHERE。 .. 
 ORDER BY ... 
  

有没有有效的方法？

感谢

解决方案

您想要第二次查询吗？

  SELECT TOP 10 
 *，foo.bar 
 FROM 
 T 
 CROSS JOIN 
 （SELECT COUNT（*）AS bar FROM T WHERE ...）foo 
 WHERE 
 ... 
 ORDER BY 
 ... 
  

或

  DECLARE @bar int 
 SELECT @bar = COUNT（*）AS bar FROM T WHERE ... 
 SELECT TOP 10 
 *，@bar 
 FROM 
 T 
 CROSS JOIN 
（SELECT COUNT（*）AS bar FROM T WHERE ...）foo 
 WHERE 
 ... 
 ORDER BY 
 ... 
  
 
 
 
或（编辑：使用WITH）
 code> WITH cTotal AS 
（
 SELECT COUNT（*）AS bar FROM T WHERE ...）
）
 SELECT TOP 10 
 *，cTotal .bar 
 FROM 
 T 
 WHERE 
 ... 
 ORDER BY 
 ... 
  
 
I have a SQL Server 2008 query
SELECT TOP 10 *
FROM T
WHERE ...
ORDER BY ...
I'd like to get also the total number of the rows. The obious way is to make a second query
SELECT COUNT(*)
FROM T
WHERE ...
ORDER BY ...
Is there an efficient method?

Thanks
解决方案
Do you want a second query?
SELECT TOP 10
    *, foo.bar
FROM
    T
    CROSS JOIN
    (SELECT COUNT(*) AS bar FROM T WHERE ...) foo
WHERE
    ...
ORDER BY
    ...
OR
DECLARE @bar int
SELECT @bar = COUNT(*) AS bar FROM T WHERE ...
SELECT TOP 10
    *, @bar
FROM
    T
    CROSS JOIN
    (SELECT COUNT(*) AS bar FROM T WHERE ...) foo
WHERE
    ...
ORDER BY
    ...
Or (Edit: using WITH)
WITH cTotal AS
(
    SELECT COUNT(*) AS bar FROM T WHERE ...)
)
SELECT TOP 10
    *, cTotal .bar
FROM
    T
WHERE
    ...
ORDER BY
    ...


                        
这篇关于如何使用TOP检索查询的总行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！