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