[color=blue]1. Oracle 分页中对于分页的处理,以下是没有 Order by 的正确分页Sql语句。 [/color]
select * from
(
SELECT rownum rownumber,a.id,a.userid,a.country,a.realname,a.firstname,a.lastname,a.educationbgcode,
a.updatetime,round((sysdate-a.experienceyear)/360) as textint1,a.gender,a.birthday,a.educationbg,
a.province,a.other,positiontype,positionsubtype
FROM resume a
where lang = '1' AND a.isdeleted = '0' AND a.educationbgcode>=4
rownumber<=20
)
where and rownum>10
[color=blue]2. Oracle 中分页语句中,有 order by 并且错误的查询将语句。[/color]
select * from
(
SELECT rownum rownumber,a.id,a.userid,a.country,a.realname,a.firstname,a.lastname,a.educationbgcode,
a.updatetime,round((sysdate-a.experienceyear)/360) as textint1,a.gender,a.birthday,a.educationbg,
a.province,a.other,positiontype,positionsubtype
FROM resume a
where lang = '1' AND a.isdeleted = '0' AND a.educationbgcode>=4
[color=red]order by updatetime desc [/color]
rownumber<=20
)
where and rownum>10
[color=blue]3. 解决办法,将采用3次子查询来解决带有 Order by 的分页查询。[/color]SELECT x.* from
(
SELECT [color=red]rownum numbers[/color],z.* from
(
SELECT [color=red]rownum rownumber[/color],a.id,a.userid,a.country,a.realname,a.firstname,a.lastname,a.educationbgcode,
a.updatetime,round((sysdate-a.experienceyear)/360) as textint1,a.gender,a.birthday,a.educationbg,
a.province,a.other,positiontype,positionsubtype
FROM resume a
where lang = '1' AND a.isdeleted = '0' AND a.educationbgcode>=4
order by updatetime
) z
where [color=red]rownum[/color]<=10
) x
where [color=red]x.numbers[/color]>5
select * from
(
SELECT rownum rownumber,a.id,a.userid,a.country,a.realname,a.firstname,a.lastname,a.educationbgcode,
a.updatetime,round((sysdate-a.experienceyear)/360) as textint1,a.gender,a.birthday,a.educationbg,
a.province,a.other,positiontype,positionsubtype
FROM resume a
where lang = '1' AND a.isdeleted = '0' AND a.educationbgcode>=4
rownumber<=20
)
where and rownum>10
[color=blue]2. Oracle 中分页语句中,有 order by 并且错误的查询将语句。[/color]
select * from
(
SELECT rownum rownumber,a.id,a.userid,a.country,a.realname,a.firstname,a.lastname,a.educationbgcode,
a.updatetime,round((sysdate-a.experienceyear)/360) as textint1,a.gender,a.birthday,a.educationbg,
a.province,a.other,positiontype,positionsubtype
FROM resume a
where lang = '1' AND a.isdeleted = '0' AND a.educationbgcode>=4
[color=red]order by updatetime desc [/color]
rownumber<=20
)
where and rownum>10
[color=blue]3. 解决办法,将采用3次子查询来解决带有 Order by 的分页查询。[/color]SELECT x.* from
(
SELECT [color=red]rownum numbers[/color],z.* from
(
SELECT [color=red]rownum rownumber[/color],a.id,a.userid,a.country,a.realname,a.firstname,a.lastname,a.educationbgcode,
a.updatetime,round((sysdate-a.experienceyear)/360) as textint1,a.gender,a.birthday,a.educationbg,
a.province,a.other,positiontype,positionsubtype
FROM resume a
where lang = '1' AND a.isdeleted = '0' AND a.educationbgcode>=4
order by updatetime
) z
where [color=red]rownum[/color]<=10
) x
where [color=red]x.numbers[/color]>5