如何使用 TOP 检索查询的总行数

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

问题描述

我有一个 SQL Server 2008 查询

I have a SQL Server 2008 query

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

我还想获得总行数.obious 方法是进行第二次查询

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 ...

有什么有效的方法吗?

谢谢

推荐答案

您想要第二个查询吗?

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)

WITH cTotal AS
(
    SELECT COUNT(*) AS bar FROM T WHERE ...)
)
SELECT TOP 10
    *, cTotal .bar
FROM
    T
WHERE
    ...
ORDER BY
    ...

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