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屋!