您如何选择 TOP x 但仍然获得整个查询的 COUNT? [英] How do you Select TOP x but still get a COUNT of the whole query?

问题描述

我正在编写一个网页，以根据用户指定的过滤条件以交互方式过滤结果.我只想从 SQL 返回前 20 行，但我想知道有多少行符合条件(计数).我希望能够告诉用户:这是符合您的条件的前 20 行，顺便说一句，还有 2,000 行我没有在此处显示".

I'm writing a webpage to interactively filter results based on filter criteria as it is specified by the user. I only want to return from SQL the TOP 20 rows but I want to know how many rows met the criteria (Count). I want to be able to tell the user: "here are the top 20 rows matching your criteria, and BTW, there were 2,000 additional rows I'm not showing here".

我知道我可以简单地运行两次查询，但 EWWWW 既昂贵又浪费.如何在不对数据库过度征税的情况下实现我想要的?

I know I could simply run the query twice but EWWWW that's expensive and wasteful. How can I achieve what I want without over taxing the database?

推荐答案

可以使用 COUNT(*) OVER()

SELECT TOP 20 *, 
       COUNT(*) OVER() AS TotalMatchingRows
FROM master..spt_values
WHERE type='P'
ORDER BY number

执行两个查询可能会更有效，但特别是如果您有更窄的索引，可用于确定匹配的行数但不涵盖整个 SELECT 列表.

Doing two queries may work out more efficient however especially if you have narrower indexes that can be used in determining the matching row count but don't cover the entire SELECT list.

这篇关于您如何选择 TOP x 但仍然获得整个查询的 COUNT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！