oracle 的SQL 我想更新一个表的数据，先对一个日期字段升序排序再取前十条，一条SQL能实现吗

-- 根据你的提问，作答：

-- 首先建表

-- Create table

create table T_KONGXIANJI537

(

TYPEID VARCHAR2(20),

MUSICNAME VARCHAR2(2000),

MUSICSTARTTIME DATE,

MUSICENDTIME DATE

);

-- 你可以插入数条数据

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '一帘幽梦mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:03:15', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '人鬼情未了mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:03:33', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '十五的月亮mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:02:46', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '同桌的你mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:03:51', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '回家mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:05:32', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '在水一方mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:06:06', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '天路mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:04:57', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '女人花mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:05:08', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '情非得已mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:04:53', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '我只在乎你mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:04:02', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '我的中国心mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:03:09', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '把悲伤留给自己mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:04:15', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '故乡的原风景mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:04:42', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '新鸳鸯蝴蝶梦mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:03:58', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_MUSIC_DETAIL (TYPEID, MUSICNAME, MUSICSTARTTIME, MUSICENDTIME)

values ('01', '明天会更好mp3', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-01-2000 00:03:24', 'dd-mm-yyyy hh24:mi:ss'));

commit;

-- 通过一条sql实现先对MUSICENDTIME日期字段升序排序再取前十条,如下：

SELECT

FROM (SELECT FROM t_kongxianji537 t ORDER BY tmusicendtime DESC) b

WHERE ROWNUM <11;

比如想取出100-150条记录,按照tname排序 select tname,tabtype from (select tname,tabtype,row_number() over ( order by tname ) rn from tab)where rn between 100 and 150; 2 使用rownum 虚列 select tname,tabtype from (select tname,tabtype,rownum rn from tab where rownum <= 150)where rn >= 100; 注释：使用序列时不能基于整个记录集合来进行排序,假如指定了order by子句,排序的的是选出来的记录集的排序。 在ORACLE如果想取一张表按时间排序后的前5条最新记录: 方法一\二对,方法三错 SELECT GUID,title,content FROM (SELECT GUID,title,content, row_number() over (order by releasetime desc)tm FROM web_LO_Article WHERE funID=20 and content like '%<img %') WHERE tm between 1 and 5 或者:select from (select from web_LO_Article where funID=20 and content like '%<img %' order by releasetime desc)where rownum<6 ROW_NUMBER() 就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ReportID) SELECT GUID,title,content,releaseTimeFROM web_LO_Article WHERE funID=20 and rownum<6 and content like '%<img %' order by releaseTime desc 方法三表示:查询数据库中的前5条数据，然后在对它们按时间降序排列。 select from (select row_number() over(order by id) rn from et_sys_treebase) where rn between 3 and 5表的记录就可以查询出来，结果是从3到5的记录 //河南省，点击率数最高的前8个地市

select

from (select t, rownum as rn from tablename t where rownum <= 20)

where rn > 10

1、通过rownum对数据库的记录进行编号

SELECT ROWNUM RN1,A FROM A;//从表A中获取记录，并用rownum生成RN1

2、通过RN1的标记进行倒序排序

SELECT B,ROWNUM RN2 FROM (SELECT ROWNUM RN1,A FROM A) ORDER BY RN1 DESC;

3、通过RN2取小于等于10的数据记录

SELECT FROM (SELECT B,ROWNUM RN2 FROM (SELECT ROWNUM RN1,A FROM A) ORDER BY RN1 DESC) WHERE RN2<=10;

方法是：

select from (select from t order by col1) where rownum<10

选取排序后的前N条记录：

1 Sql代码

select top 3 e_name from ptempcuishen_temp_20100707   order by id

select top 3 e_name from ptempcuishen_temp_20100707 order by id

Sql代码

select top 3  from ptempcuishen_temp_20100707   order by id

select top 3 from ptempcuishen_temp_20100707 order by id

2分页查询

可以用row_number关键字来进行分页查询，例如：

Sql代码

select  from ptempcuishen_temp_20100707   qualify row_number() over(order by id) >= 2 and row_number() over(order by id) <= 5

select from ptempcuishen_temp_20100707 qualify row_number() over(order by id) >= 2 and row_number() over(order by id) <= 5

以上就是关于oracle 的SQL 我想更新一个表的数据，先对一个日期字段升序排序再取前十条，一条SQL能实现吗全部的内容，包括:oracle 的SQL 我想更新一个表的数据，先对一个日期字段升序排序再取前十条，一条SQL能实现吗、oracle取前几条记录、在Oracle中取第10条记录到第20条记录，效率最高的SQL语句怎么写等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！