想不出好方法: 下面的试试 select a.*,count(b.*) from (select a.*,row_number() over (order by age ) rn from tab where tab.xxx= (写上限制条件))a ,tab b where a.rn>0 and a.rn<10 and b.xxx=(写上限制条件)
select * from (select a.* ,row_number() over(order by age) rn ,count() over() cnt from tab a where a.age>23 )t where t.rn<=20 ;
实测数据:CREATE TABLE T87 ( ID NUMBER(4), NAME VARCHAR2(20), Age NUMBER(4) ); INSERT INTO T87 VALUES(1, 'A', 12); INSERT INTO T87 VALUES(2, 'B', 23); INSERT INTO T87 VALUES(3, 'C', 18); INSERT INTO T87 VALUES(4, 'D', 25); INSERT INTO T87 VALUES(5, 'E', 27); INSERT INTO T87 VALUES(6, 'F', 21); INSERT INTO T87 VALUES(7, 'G', 30); INSERT INTO T87 VALUES(8, 'H', 22); 实测结果:返回年龄大于23的前3条记录。
下面的试试
select a.*,count(b.*) from (select a.*,row_number() over (order by age ) rn from tab where tab.xxx= (写上限制条件))a ,tab b where a.rn>0 and a.rn<10 and b.xxx=(写上限制条件)
from
(select a.*
,row_number() over(order by age) rn
,count() over() cnt
from tab a
where a.age>23
)t
where t.rn<=20
;
(
ID NUMBER(4),
NAME VARCHAR2(20),
Age NUMBER(4)
);
INSERT INTO T87 VALUES(1, 'A', 12);
INSERT INTO T87 VALUES(2, 'B', 23);
INSERT INTO T87 VALUES(3, 'C', 18);
INSERT INTO T87 VALUES(4, 'D', 25);
INSERT INTO T87 VALUES(5, 'E', 27);
INSERT INTO T87 VALUES(6, 'F', 21);
INSERT INTO T87 VALUES(7, 'G', 30);
INSERT INTO T87 VALUES(8, 'H', 22);
实测结果:返回年龄大于23的前3条记录。
另再请教下,这样子查询合理吗?是否分开2次查询(一次查询出满足条件的记录数目,另一次查询出满足条件并排好序的其中20条记录),哪个效率高些?