SELECT * FROM (SELECT * FROM (SELECT * FROM student ORDER BY ID DESC) a WHERE ROWNUM <= 4) b WHERE ID NOT IN (SELECT ID FROM (SELECT ID FROM student ORDER BY ID DESC) a WHERE ROWNUM <= 1)
最准确的需要三个select,不过,如果数据量很大的话,性能很差select * from( select a.*,rownum as num from ( select * from student ordery by id desc) a) where num >=5 and num<=8
还是不行啊,你们运行能通过吗? 我还是报错,说order by那里少括号 -_-!!!
SQL> select * from pss_complan where complanid not in( 2 select complanid from pss_complan where rownum <5 3 order by complanid desc) and rownum <9; order by complanid desc) and rownum <9 * ERROR 位于第 3 行: ORA-00907: missing right parenthesis
SQL> create table x ( 2 id number,name varchar2(10));SQL> insert into x values(1,'mike'); SQL> insert into x values(2,'tom'); SQL> insert into x values(3,'jerry'); SQL> insert into x values(4,'kit'); SQL> insert into x values(5,'john'); SQL> insert into x values(6,'sun'); SQL> insert into x values(7,'tonny'); SQL> insert into x values(8,'sarry'); SQL> insert into x values(9,'lizy');SQL> select id,name from (select id,name,rownum rr from x order by id desc) where rr between 5 and 9; ID NAME ---------- ---------- 9 lizy 8 sarry 7 tonny 6 sun 5 john=============================================== 按 waterfirer(水清) 的写法测试结果
order by 不能用在子查询中
SQL> select * from( 2 select a.*,rownum as num from ( 3 select * from pss_complan order by complanid desc) a) where num >=5 and num<=8; select * from pss_complan order by complanid desc) a) where num >=5 and num<=8 * ERROR 位于第 3 行: ORA-00907: missing right parenthesis
上面的*指错地方了select * from pss_complan order by complanid desc) a) where num >=5 and num<=8 * 错误还是指在order by上面 网上说可能是版本的问题 你们是什么版本的啊?9以上的吗?
SQL> select id,name from 2 (select id,name,rownum rr from x order by id desc) 3 where rr between 5 and 9; (select id,name,rownum rr from x order by id desc) * ERROR 位于第 2 行: ORA-00907: missing right parenthesis
我重新给你编写了一下; 你编的太麻烦了; 如果有问题给我发消息! SELECT * FROM ( SELECT ID, NAME, ROW_NUMBER() OVER (ORDER BY ID DESC ) ROW_ORDER FROM STUDENT ) STU WHERE STU.ROW_ORDER BETWEEN 5 AND 8;
FROM (SELECT *
FROM (SELECT *
FROM student
ORDER BY ID DESC) a
WHERE ROWNUM <= 4) b
WHERE ID NOT IN (SELECT ID
FROM (SELECT ID
FROM student
ORDER BY ID DESC) a
WHERE ROWNUM <= 1)
select a.*,rownum as num from (
select * from student ordery by id desc) a) where num >=5 and num<=8
我还是报错,说order by那里少括号
-_-!!!
2 select complanid from pss_complan where rownum <5
3 order by complanid desc) and rownum <9;
order by complanid desc) and rownum <9
*
ERROR 位于第 3 行:
ORA-00907: missing right parenthesis
2 id number,name varchar2(10));SQL> insert into x values(1,'mike');
SQL> insert into x values(2,'tom');
SQL> insert into x values(3,'jerry');
SQL> insert into x values(4,'kit');
SQL> insert into x values(5,'john');
SQL> insert into x values(6,'sun');
SQL> insert into x values(7,'tonny');
SQL> insert into x values(8,'sarry');
SQL> insert into x values(9,'lizy');SQL> select id,name from
(select id,name,rownum rr from x order by id desc)
where rr between 5 and 9; ID NAME
---------- ----------
9 lizy
8 sarry
7 tonny
6 sun
5 john===============================================
按 waterfirer(水清) 的写法测试结果
2 select a.*,rownum as num from (
3 select * from pss_complan order by complanid desc) a) where num >=5 and num<=8;
select * from pss_complan order by complanid desc) a) where num >=5 and num<=8
*
ERROR 位于第 3 行:
ORA-00907: missing right parenthesis
*
错误还是指在order by上面
网上说可能是版本的问题
你们是什么版本的啊?9以上的吗?
2 (select id,name,rownum rr from x order by id desc)
3 where rr between 5 and 9;
(select id,name,rownum rr from x order by id desc)
*
ERROR 位于第 2 行:
ORA-00907: missing right parenthesis
你编的太麻烦了;
如果有问题给我发消息! SELECT *
FROM (
SELECT ID,
NAME,
ROW_NUMBER() OVER (ORDER BY ID DESC ) ROW_ORDER
FROM STUDENT
) STU
WHERE STU.ROW_ORDER BETWEEN 5 AND 8;
上面不是水清他们不是可以运行啊
他们在子查询里就用了order by的啊
而且不能在子查询里用order by我想要的数据抓不出来的啊