神秘的凯

天行健君子以自强不息,地势坤君子以厚德载物。

Oracle,SQLserver 分页语句

SQL Server,Oracle 常用分页SQL

今天无聊和朋友讨论分页,发现网上好多都是错的。网上经常查到的那个Top Not in 或者Max 大部分都不实用,很多都忽略了Order和性能问题。为此上网查了查,顺带把2000和2012版本的也补上了。

先说说网上常见SQL的错误或者说局限问题

1
2
3
4
5
select top 10 *
from table1
where id not in(
    select top 开始的位置 id
    from table1)

这样的确是可以取到分页数据,但是这是默认排序的,如果要按其中一列排序呢?那order by 加在哪里呢?里外都加,显然不行,外面的Order不起作用,只能嵌套,Oh my god,编程三个Select了,这效率。

为了好用效率高,总体思路还是老老实实的用RowNumber解决,但是SQL2000没有RowNumber,其实我们可以通过临时表自增列搞定,不多说,上例子。

 

SQL 2000 用临时表解决,通过在临时表中增加自增列解决RowNumber。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050
 
 
CREATE TABLE #employees (RowNumber INT IDENTITY(1,1),
LastName VARCHAR(100),FirstName VARCHAR(100),
EmailAddress VARCHAR(100))
 
 
INSERT INTO #employees (LastName, FirstName, EmailAddress)
SELECT LastName, FirstName, EmailAddress
FROM Employee
ORDER BY LastName, FirstName, EmailAddress
SELECT LastName, FirstName, EmailAddress
FROM #employees
WHERE RowNumber > @Start AND RowNumber <= @End
 
 
DROP TABLE #employees
 
 
GO

  


SQL 2005/2008 由于支持了Row_Number于是通过派生表的方式解决(两个嵌套)

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050
 
 
SELECT LastName, FirstName, EmailAddress
FROM (SELECT LastName, FirstName, EmailAddress,
ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
FROM Employee) EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

  


SQL 2005/2008 或者用CTE的方式实现,和派生表一样,就是好看点,执行计划都一样。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050;
 
 
WITH EmployeePage AS
(SELECT LastName, FirstName, EmailAddress,
ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
FROM Employee)
SELECT LastName, FirstName, EmailAddress
FROM EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

  


SQL SERVER 2012 比较给力支持了OFFSET,于是一个Select结束战斗

1
2
3
4
5
SELECT LastName, FirstName, EmailAddress
FROM Employee
ORDER BY LastName, FirstName, EmailAddress
OFFSET 14000 ROWS
FETCH NEXT 50 ROWS ONLY;

Oracle分页(方式一)

1
2
3
4
5
select * from ( 
     select rownum as rn, source.* from ( 
          select   *  from  house 
     ) source      where rownum <= 5)     result where rn >= 1   order by(title) asc;
     

 

Oracle分页(方式二 推荐)

select * from 
                   (select a.*,rownum rn from 
                                                        (select * from house) a
                    )
                       where rn between 1 and 5   order by(ID)
 


阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/fujiakai/article/details/51544562
个人分类: Oracle
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

Oracle,SQLserver 分页语句

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭