oracle mysql排序分页,Oracle、Mysql分页 排序

Oracle分页  排序

数据库在项目开发中主要承担存储的任务，可以根据查询条件查询想要查询内容。以下是普通的排序查询：

with t as

(

select '北京' name from dual union all

select '天津' from dual union all

select '成都' from dual union all

select '重庆' from dual

)

select name from t order by name ;

可以通过以下代码进行会话修改：

ALTER SESSION SET NLS_SORT='SCHINESE_PINYIN_M';--拼音

ALTER SESSION SET NLS_SORT='SCHINESE_STROKE_M';--按照笔划(第一顺序)、部首(第二顺序)排序

ALTER SESSION SET NLS_SORT='SCHINESE_RADICAL_M';--按照部首(第一顺序)、笔划(第二顺序)排序

或者直接执行以下代码块，查看显示结果

拼音

SELECT name FROM　t ORDER BY NLSSORT(name,'NLS_SORT = SCHINESE_PINYIN_M')

笔划

SELECT name FROM　t ORDER BY NLSSORT(name,'NLS_SORT = SCHINESE_STROKE_M')

部首

SELECT name FROM　t ORDER BY NLSSORT(name,'NLS_SORT = SCHINESE_RADICAL_M')

以下是排序在分页查询中的应用：

*注意*：把表名(INFO)，排序字段(name)更改为您要用的表和字段名

1：

select *

from (select A.*, rownum r

from (select *

from INFO ti

order by nlssort(ti.name,

'NLS_SORT=SCHINESE_PINYIN_M')) A

where rownum <= 20) B

where r > 10;

2：

SELECT *

FROM (SELECT A.*, rownum r

FROM (select *

from INFO ti

order by nlssort(ti.name,

'NLS_SORT=SCHINESE_STROKE_M')) A

WHERE rownum <= 20) B

WHERE r > 10;

3：

SELECT *

FROM (SELECT A.*, rownum r

FROM (select *

from INFO ti

order by nlssort(ti.name,

'NLS_SORT=SCHINESE_RADICAL_M')) A

WHERE rownum <= 20) B

WHERE r > 10;

示例：

/*

查询条件为：

当日8点至前一天晚上8点

*/

select

to_date(to_char(sysdate,'yyyymmdd')||' 20:00:00','yyyy-mm-dd hh24:mi:ss'),

to_date(to_char(trunc(sysdate-1),'yyyymmdd')||' 08:00:00','yyyy-mm-dd hh24:mi:ss')

from dual;

select to_char(trunc(sysdate-2),'yyyymmdd')from dual

Mysql

select * from

(

select a.*,rownum rn from

(

select * from testtable

)a where rn<=6

)where rn >3

select * from limit 3,6

SELECT * FROM aricles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10

select b.* from (

select a.*,rownum rn form

(

select * from testtable

) a where rn <=6

) b where rn >3