SQL - 返回有限的行数，但完整的行数 [英] SQL - Return limited number of rows, but full row count

问题描述

场景:我需要从 Visual FoxPro 数据库中提取信息；然而，针对此运行大型查询有锁定系统的趋势.为了解决这个问题，我们设置了限制，在查询运行超过一定时间后取消查询，并限制它返回的行数.

Scenario: I need to pull information out of a Visual FoxPro database; however, running large queries against this has a tendency to lock the system up. To resolve this, we put limits in place that cancelled the query if it ran past a certain amount of time, and limited the number of rows it would return.

有没有办法使用SELECT TOP ###"进行查询，同时返回通过语句找到的实际行数?或者是运行查询两次的唯一方法?(原因是我们仍然可以运行查询，但通知用户发生了什么.即第一个 ### 显示 ### 找到的项目").

Is there a way to have a query with "SELECT TOP ###", but also return the actual number of rows found through the statement? Or is the only way to run the query twice? (reason being we can still have the query run through, but inform the user of what's going on. i.e. "First ### displayed of ### found items").

我最初的尝试只是简单地在语句的选择部分添加一个COUNT(*)"，但这并没有完全实现我想要的(它返回了正确的行数，但只返回了其余数据的一行).

My initial trial was to just simply add a "COUNT(*)" to the select portion of the statement, but that didn't quite accomplish what I was looking for (it returned the correct number of rows, but only returned one row for the rest of the data).

推荐答案

如果我理解正确，你可以做一个子选择，但这意味着你为返回的每一行调用计数 SQL:

If I understand the question correctly, you could do a subselect, but that will mean you call the count SQL for every row returned:

select top 10 field1, field2, (select count(*) from table) as totalrows from table

这将为您提供前 10 行，每行中有一个额外的列，称为 totalrows，包含表中所有行的计数.

That will give you the top 10 rows with an extra column in each called totalrows, containing the count of all the rows in the table.

不过，就我个人而言，我只是运行一个单独的查询来获取前 n 行和计数.

Personally though, I'd just run a separate query to get the top n rows and the count.

这篇关于SQL - 返回有限的行数，但完整的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！