SQL关于分页的sql查询语句,函数使用举例
分类:web前端

在sql提供了ROW_NUMBER来做大数据量的操作,很多朋友不知道是ROW_NUMBER性能好还是存储过程的性能好,下面我们来看看测试实例。

语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

在做项目的时候需要些分页,用的数据库是MySQL,之前看到的参考例子是用MS SQL做的,在MS SQL、Oracle里面有ROW_NUMBER() OVER函数可以在数据库里对数据进行分组。百度后的结论总结如下:

语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序,再为降序以后的没条xlh记录返回一个序号。
示例:
xlh           row_num
1700              1
1500              2
1085              3
710                4

(一)先说下在mysql里面分页时的sql语句,转载自第13、14楼:

简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序,再为降序以后的没条xlh记录返回一个序号。 示例: xlh row_num 1700 1 1500 2 1085 3 710 4

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

MySQL中的分页比较容易,只要在程序中中得到是从哪条记录开始,需要取多少个记录就行了。

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

实例:

比如页长 10, 那么第8页就是自 第81 条记录取10条,语句类似于:

实例:

初始化数据

select * from tbl_user order by c_uid limit 80,10;

初始化数据

create table employee (empid int ,deptid int ,salary decimal(10,2))
insert into employee values(1,10,5500.00)
insert into employee values(2,10,4500.00)
insert into employee values(3,20,1900.00)
insert into employee values(4,20,4800.00)
insert into employee values(5,40,6500.00)
insert into employee values(6,40,14500.00)
insert into employee values(7,40,44500.00)
insert into employee values(8,50,6500.00)
insert into employee values(9,50,7500.00)

limit 的用法,可以参考MySQL的官方文档说明。

13.2.7. SELECT语法

create table employee (empid int ,deptid int ,salary decimal(10,2))insert into employee values(1,10,5500.00)insert into employee values(2,10,4500.00)insert into employee values(3,20,1900.00)insert into employee values(4,20,4800.00)insert into employee values(5,40,6500.00)insert into employee values(6,40,14500.00)insert into employee values(7,40,44500.00)insert into employee values(8,50,6500.00)insert into employee values(9,50,7500.00)

数据显示为

LIMIT子句可以被用于限制被SELECT语句返回的行数。LIMIT取一个或两个数字自变量,自变量必须是非负的整数常数(当使用已预备的语句时除外)。

数据显示为

empid       deptid      salary

使用两个自变量时,第一个自变量指定返回的第一行的偏移量,第二个自变量指定返回的行数的最大值。初始行的偏移量为0(不是1):

empid deptid salary----------- ----------- ---------------------------------------1 10 5500.002 10 4500.003 20 1900.004 20 4800.005 40 6500.006 40 14500.007 40 44500.008 50 6500.009 50 7500.00


mysql> SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15为了与PostgreSQL兼容,MySQL也支持LIMIT row_count OFFSET offset语法。

需求:根据部门分组,显示每个部门的工资等级

1           10          5500.00
2           10          4500.00
3           20          1900.00
4           20          4800.00
5           40          6500.00
6           40          14500.00
7           40          44500.00
8           50          6500.00
9           50          7500.00

如果要恢复从某个偏移量到结果集合的末端之间的所有的行,您可以对第二个参数是使用比较大的数。本语句可以恢复从第96行到最后的所有行:

预期结果:

需求:根据部门分组,显示每个部门的工资等级

mysql> SELECT * FROM tbl LIMIT 95,18446744073709551615;

empid deptid salary rank----------- ----------- --------------------------------------- --------------------1 10 5500.00 12 10 4500.00 24 20 4800.00 13 20 1900.00 27 40 44500.00 16 40 14500.00 25 40 6500.00 39 50 7500.00 18 50 6500.00 2

预期结果:

使用1个自变量时,该值指定从结果集合的开头返回的行数:

SQL脚本:

empid       deptid      salary                                  rank

mysql> SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee


换句话说,LIMIT n与LIMIT 0,n等价。

我们再来测试大数据库


对于已预备的语句,您可以使用位置保持符。以下语句将从tb1表中返回一行:

几种写法分别如下:

1           10          5500.00                                 1
2           10          4500.00                                 2
4           20          4800.00                                 1
3           20          1900.00                                 2
7           40          44500.00                                1
6           40          14500.00                                2
5           40          6500.00                                 3
9           50          7500.00                                 1
8           50          6500.00                                 2

mysql> SET @a=1;mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";
mysql> EXECUTE STMT USING @a;

代码如下复制代码

SQL脚本:

以下语句将从tb1表中返回第二到第六行:

SELECT TOP 20 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber, *FROM dbo.mem_member) _myResultsWHERE RowNumber 10000

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

mysql> SET @skip=1; SET @numrows=5;mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";
mysql> EXECUTE STMT USING @skip, @numrows;

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber, *FROM dbo.mem_member) _myResultsWHERE RowNumber between 10000 and 10020

转载:

 

WITH OrderedResults AS

(二)在MS SQL里面,有ROW_NUMBER() OVER函数:

(SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member)

转载自

SELECT *

 

FROM OrderedResults

语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

WHERE RowNumber between 10000 and 10020

简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序,再为降序以后的没条xlh记录返回一个序号。
示例:
xlh           row_num
1700              1
1500              2
1085              3
710                4

不管哪种写法,性能都不理想。在8,9万条数据的情况下要运行6秒左右。

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

2、使用临时表再加存储过程

实例:

代码如下复制代码

初始化数据

BEGIN DECLARE @PageLowerBound int DECLARE @PageUpperBound int -- Set the page bounds SET @PageLowerBound = 10000 SET @PageUpperBound = 10020

create table employee (empid int ,deptid int ,salary decimal(10,2))
insert into employee values(1,10,5500.00)
insert into employee values(2,10,4500.00)
insert into employee values(3,20,1900.00)
insert into employee values(4,20,4800.00)
insert into employee values(5,40,6500.00)
insert into employee values(6,40,14500.00)
insert into employee values(7,40,44500.00)
insert into employee values(8,50,6500.00)
insert into employee values(9,50,7500.00)

-- Create a temp table to store the select results Create Table #PageIndex ( [IndexId] int IDENTITY (1, 1) NOT NULL, [Id] varchar(18) ) -- Insert into the temp table declare @SQL as nvarchar(4000) SET @SQL = 'INSERT INTO #PageIndex (Id)' SET @SQL = @SQL

数据显示为

  • ' SELECT' SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound) SET @SQL = @SQL + ' m_id' SET @SQL = @SQL + ' FROM dbo.mem_member' SET @SQL = @SQL + ' ORDER BY NameC' -- Populate the temp table exec sp_executesql @SQL

empid       deptid      salary

-- Return paged results SELECT O.* FROM dbo.mem_member O, #PageIndex PageIndex WHERE PageIndex.IndexID @PageLowerBound AND O.[m_Id] = PageIndex.[Id] ORDER BY PageIndex.IndexID drop table #PageIndex END


而使用这种方法,在同样的情况下用时只需1秒,这样看来我们就不要说了ROW_NUMBER的性能显示不如存储过程了。

1           10          5500.00
2           10          4500.00
3           20          1900.00
4           20          4800.00
5           40          6500.00
6           40          14500.00
7           40          44500.00
8           50          6500.00
9           50          7500.00

需求:根据部门分组,显示每个部门的工资等级

预期结果:

empid       deptid      salary                                  rank



1           10          5500.00                                 1
2           10          4500.00                                 2
4           20          4800.00                                 1
3           20          1900.00                                 2
7           40          44500.00                               1
6           40          14500.00                               2
5           40          6500.00                                 3
9           50          7500.00                                 1
8           50          6500.00                                 2

SQL脚本:

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

本文由10bet手机官网发布于web前端,转载请注明出处:SQL关于分页的sql查询语句,函数使用举例

上一篇:编写简单的事务应用,常用的格式 下一篇:一条sql语句搞定数据库分页
猜你喜欢
热门排行
精彩图文