SQL - 返回有限的行数，但返回完整的行数
场景：我需要从 Visual FoxPro 数据库中提取信息；但是，对此运行大型查询可能会导致系统锁定。为了解决这个问题，我们设置了限制，如果查询运行超过一定时间，则取消查询，并限制它将返回的行数。
有没有办法使用“SELECT TOP ###”进行查询，同时还返回通过该语句找到的实际行数？或者是运行查询两次的唯一方法？ （原因是我们仍然可以运行查询，但通知用户发生了什么。即“第一个显示 ### 找到的项目”）。
我最初的尝试只是简单地将“COUNT(*)”添加到语句的选择部分，但这并没有完全实现我所寻找的目标（它返回了正确的行数，但只返回了一行其余数据）。
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.
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").
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).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果我正确理解这个问题，你可以做一个子选择，但这意味着你为返回的每一行调用计数SQL：
这将为你提供前10行，每个行中都有一个额外的列，称为totalrows，包含所有行的计数表中的行。
但就我个人而言，我只是运行一个单独的查询来获取前 n 行和计数。
If I understand the question correctly, you could do a subselect, but that will mean you call the count SQL for every row returned:
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.
Personally though, I'd just run a separate query to get the top n rows and the count.
您需要运行 2 个单独的 SELECT。一种方法是检索查询返回的 COUNT 行，然后返回特定页面的记录子集。
您可以通过在检索第一个“页面”时仅检索一次总 COUNT 来优化此操作（即不对后续页面执行整个计数）
You would need to run 2 separate SELECTs. One to retrieve the COUNT of rows returned by the query and then to return the subset of records for a particular page.
You could optimise this by only retrieving the total COUNT once, when the first "page" is retrieved (i.e. don't do the entire count for subsequent pages)