SQL在每个SELECT请求上获取ROW_NUMBER和COUNT [英] SQL get ROW_NUMBER and COUNT on every SELECT request

问题描述

我正在建立一种网格机制，需要从数据库中检索总数或找到的记录，只检索其中有row_number的这些记录的范围。

I´m building a grid mechanism where I need to retrieve data from Database the total or records found, retrieving just a range of these records with a row_number in it.

我正在使用SqlServer进行测试，但是我也需要在Oracle和MySql上支持它。

I´m using SqlServer for testing, but I need to support that on Oracle and MySql as well.

这是我正在尝试的方法，但是我可以

That´s what I´m trying, but I can´t make it work:

    SELECT * FROM 
           (SELECT ROW_NUMBER() AS RN, 
                   COUNT(*) AS TOTALCN, 
                   Id, 
                   Name, 
                   Phone 
            FROM MyTable WHERE Deleted='F')
            WHERE RN > 100 AND RN < 150;

想法是：

MyTable -> number of records: 1000
Select Id, Name, Phone from MyTable where Deleted='F' -> number of records: 850
Get the records 100 to 150 from the 850.

我想要得到类似这样的选择：

I want to get a select like:

RN   TOTALCN Id    Name   Phone
1    850     Data  Data   Data
2    850     Data  Data   Data
3    850     Data  Data   Data
4    850     Data  Data   Data
5    850     Data  Data   Data
6    850     Data  Data   Data

CN（850）将是我的网格记录总数。
RN 将是数据的网格索引。

The CN (850) will be my grid total number of records. The RN will be grid index for data.

CAn有人可以帮助我实现这一目标吗？

CAn someone help me to accomplish that ?

感谢您的帮助。

因此，我将在每个SELECT语句上添加一个ORDER BY。这是我到目前为止所得到的：

So, I will defenetely add an ORDER BY on every SELECT. Here is what I´m getting so far:

SELECT * FROM (SELECT ROW_NUMBER() 
              OVER (ORDER BY ID)  AS RN, 
              COUNT(*) OVER (ORDER BY (SELECT NULL) AS CNT) 
              Id, Name Phone FROM MyTable WHERE Deleted='F') 
T WHERE RN > 100 AND RN < 500;

我要用正确的方式吗？

SQL的成本是多少？

How expensive will be that to SQL ?

推荐答案

包含 COUNT（*） SELECT 子句中的code>带有大量详细信息行将失败。这是因为 COUNT（*）是一个聚合函数。在没有 GROUP BY 的情况下，其结果集中仅返回一行。因此，您需要在自己的 SELECT 语句中查询计数。

To include a COUNT(*) in the same SELECT clause with a bunch of detail rows is to fail. That's because COUNT(*) is an aggregate function. In the absence of GROUP BY it returns only one row in its result set. So, you need to query the count in its own SELECT statement.

SQL Server的 ROW_NUMBER（）函数。 Oracle具有一个名为 ROWNUM 的伪列。 MySQL对于获取行号确实是个悲惨的黑客。在这里描述。 MySQL-在选择时获取行号

SQL Server has the ROW_NUMBER() function. Oracle has the pseudocolumn named ROWNUM. And MySQL has a truly miserable hack for getting row numbers. It's described here. MySQL - Get row number on select

这篇关于SQL在每个SELECT请求上获取ROW_NUMBER和COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！