这是我的一个查询语句,其中黑体的地方只能从BETWEEN 1 AND n能取到值,BETWEEN 2 AND n等都取不到值是什么原因?
SELECT * FROM(
select ROWNUM RECNO,aaa.new_id,aaa.new_title,aaa.new_user_name,aaa.new_time,aaa.new_restore, c.user_name rev_user_name ,aaa.rep_time,aaa.sta_id,aaa.cha_id
from ( select aa.*,row_number() over (partition by aa.new_id order by aa.rep_time desc) rn
from( select a.new_id,a.new_title,c.user_name new_user_name,a.new_time,a.new_restore,a.sta_id,a.cha_id,nvl(b.user_id,a.user_id) rev_user_id,nvl(b.rep_time,a.new_time) rep_time
from xml_new a,xml_replay b,xml_user c where a.new_id=b.new_id(+) and a.user_id=c.user_id and a.user_id='181') aa order by aa.rep_time desc) aaa, xml_user c
where aaa.rev_user_id=c.user_id and aaa.rn=1 and ROWNUM <= (11 + 10 - 1) ORDER BY ROWNUM ASC)
WHERE ROWNUM BETWEEN 1 AND (11 + 10 - 1)
SELECT * FROM(
select ROWNUM RECNO,aaa.new_id,aaa.new_title,aaa.new_user_name,aaa.new_time,aaa.new_restore, c.user_name rev_user_name ,aaa.rep_time,aaa.sta_id,aaa.cha_id
from ( select aa.*,row_number() over (partition by aa.new_id order by aa.rep_time desc) rn
from( select a.new_id,a.new_title,c.user_name new_user_name,a.new_time,a.new_restore,a.sta_id,a.cha_id,nvl(b.user_id,a.user_id) rev_user_id,nvl(b.rep_time,a.new_time) rep_time
from xml_new a,xml_replay b,xml_user c where a.new_id=b.new_id(+) and a.user_id=c.user_id and a.user_id='181') aa order by aa.rep_time desc) aaa, xml_user c
where aaa.rev_user_id=c.user_id and aaa.rn=1 and ROWNUM <= (11 + 10 - 1) ORDER BY ROWNUM ASC)
WHERE ROWNUM BETWEEN 1 AND (11 + 10 - 1)
解决方案 »
- ORA-00376:此时无法读数据文件 5 ORA-01110:数据文件5:‘D:\DISK2\DAN\ORADATA\EXAMPLE01.DBF'".
- 急求,oracle9i与oracle10g能同时安装吗
- 为什么使用SQLPLUS 登录时会自动执行一段代码,并提示我输入变量值?????
- PL/SQL 怎么测试SQL的性能啊?
- 请问各位大侠,我想把一台机器的数据库整个移值到另一台机器上,最方便的办法是什么?还有,怎么把建整个数据库的sql代码导出来。谢谢
- 关于Solaris下ORACLE的归档日志
- 检查时间段重叠问题?
- 有关临时表数据被清空,求解决办法!分不够再加!
- 请教一下,存储过程怎样返回结果集?能给一个实例吗?
- 请--溺水兄--和各位大虾帮忙,sqlserver数据库恢复。
- ORA-12154: TNS: 无法解析指定的连接标识符
- 分組查詢后排名次
*
FROM
(
SELECT
ROWNUM RECNO ,
AAA.NEW_ID ,
AAA.NEW_TITLE ,
AAA.NEW_USER_NAME ,
AAA.NEW_TIME ,
AAA.NEW_RESTORE ,
C.USER_NAME REV_USER_NAME ,
AAA.REP_TIME ,
AAA.STA_ID ,
AAA.CHA_ID
FROM
(
SELECT
AA.* ,
ROW_NUMBER ( ) OVER ( PARTITION BY AA.NEW_ID
ORDER BY
AA.REP_TIME
DESC ) RN
FROM
(
SELECT
A.NEW_ID ,
A.NEW_TITLE ,
C.USER_NAME NEW_USER_NAME ,
A.NEW_TIME ,
A.NEW_RESTORE ,
A.STA_ID ,
A.CHA_ID ,
NVL ( B.USER_ID , A.USER_ID ) REV_USER_ID ,
NVL ( B.REP_TIME , A.NEW_TIME ) REP_TIME
FROM
XML_NEW A ,
XML_REPLAY B ,
XML_USER C
WHERE
A.NEW_ID=B.NEW_ID ( + )
AND A.USER_ID=C.USER_ID
AND A.USER_ID='181' ) AA
ORDER BY
AA.REP_TIME
DESC ) AAA ,
XML_USER C
WHERE
AAA.REV_USER_ID=C.USER_ID
AND AAA.RN=1
AND ROWNUM <= ( 11 + 10 - 1 )
ORDER BY
ROWNUM
ASC )
WHERE
ROWNUM BETWEEN 1
AND ( 11 + 10 - 1 )
SELECT ID, NAME FROM
(
SELECT ROWNUM RECNO, ID, NAME FROM
(SELECT * FROM CUSTOMER ORDER BY NAME)
WHERE ROWNUM <= (2 + 3 - 1)
ORDER BY ROWNUM ASC
)
WHERE RECNO BETWEEN 2 AND (2 + 3 - 1)
只有通过WHERE中的ROWNUM < N来做