如何取出按时间字段排序后的前10行。 select * from table t where t.status = 1 order by t.sj desc and rownum <= 10; 这样肯定不行嘛。首先要按时间排序,然后只取前10行 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select * from (select t.*,rownum rn from table t where t.status = 1 order by t.sj desc)where rn<=10 select * from ( select temp.*, rownum rn from (select * from t where t.status = 1 order by t.sj desc)temp )where rn<=31楼的对么? --参考SQL> select * from emp 2 order by hiredate;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7839 KING PRESIDENT 1981-11-17 5000.00 10 7900 JAMES CLERK 7698 1981-12-3 950.00 30 --按时间排序的第10行 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3100.00 20 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20SQL> select rownum,e1.* 2 from ( 3 select e.* 4 from emp e 5 order by hiredate) e1 6 where rownum<=10; ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ----- ---------- --------- ----- ----------- --------- --------- ------ 1 7369 SMITH CLERK 7902 1980-12-17 800.00 20 2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 3 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 5 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 6 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 8 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 9 7839 KING PRESIDENT 1981-11-17 5000.00 10 10 7900 JAMES CLERK 7698 1981-12-3 950.00 30 --错误SQL> select rownum,emp.* from emp 2 order by hiredate; ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ----- ---------- --------- ----- ----------- --------- --------- ------ 1 7369 SMITH CLERK 7902 1980-12-17 800.00 20 2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 3 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 6 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 5 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 9 7839 KING PRESIDENT 1981-11-17 5000.00 10 12 7900 JAMES CLERK 7698 1981-12-3 950.00 30 13 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 14 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 8 7788 SCOTT ANALYST 7566 1987-4-19 3100.00 20 11 7876 ADAMS CLERK 7788 1987-5-23 1100.00 2014 rows selectedSQL> SQL> select * 2 from ( 3 select rownum rn,e.* 4 from emp e 5 order by hiredate) 6 where rn<=10; RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ----- ---------- --------- ----- ----------- --------- --------- ------ 1 7369 SMITH CLERK 7902 1980-12-17 800.00 20 2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 3 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 6 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 5 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 9 7839 KING PRESIDENT 1981-11-17 5000.00 10 8 7788 SCOTT ANALYST 7566 1987-4-19 3100.00 20 高分求:怎么在存储过程里调用sqlldr??在线等. SQl 语句(常见) 新建,删除,修改表结构 一个很难实现的脚本! 求助自定义时间转换函数 ORA-27103:内部错误。---急待解决,在线等待,解决马上结贴! rman的问题急!1 [请教]NCLOB的使用 Oracle中写存储过程时,关于字符串的问题 sql问题2: 有Mac的osx用的Oracle版本吗? 授权问题 关于Oracle9i在Windows Server 2008系统上的安装
select *
from (select t.*,rownum rn
from table t
where t.status = 1 order by t.sj desc)
where rn<=10
select * from ( select temp.*, rownum rn
from (select *
from t
where t.status = 1
order by t.sj desc)temp )
where rn<=31楼的对么?
--参考
SQL> select * from emp
2 order by hiredate;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7839 KING PRESIDENT 1981-11-17 5000.00 10
7900 JAMES CLERK 7698 1981-12-3 950.00 30 --按时间排序的第10行
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3100.00 20
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
SQL> select rownum,e1.*
2 from (
3 select e.*
4 from emp e
5 order by hiredate) e1
6 where rownum<=10; ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
1 7369 SMITH CLERK 7902 1980-12-17 800.00 20
2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
3 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
5 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
6 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
8 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
9 7839 KING PRESIDENT 1981-11-17 5000.00 10
10 7900 JAMES CLERK 7698 1981-12-3 950.00 30
--错误
SQL> select rownum,emp.* from emp
2 order by hiredate; ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
1 7369 SMITH CLERK 7902 1980-12-17 800.00 20
2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
3 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
6 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
5 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
9 7839 KING PRESIDENT 1981-11-17 5000.00 10
12 7900 JAMES CLERK 7698 1981-12-3 950.00 30
13 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
14 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
8 7788 SCOTT ANALYST 7566 1987-4-19 3100.00 20
11 7876 ADAMS CLERK 7788 1987-5-23 1100.00 2014 rows selectedSQL>
SQL> select *
2 from (
3 select rownum rn,e.*
4 from emp e
5 order by hiredate)
6 where rn<=10; RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
1 7369 SMITH CLERK 7902 1980-12-17 800.00 20
2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
3 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
6 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
5 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
9 7839 KING PRESIDENT 1981-11-17 5000.00 10
8 7788 SCOTT ANALYST 7566 1987-4-19 3100.00 20