select rownum from (select l.* from users u left join log l on u.id=l.user_id where u.id = 1)t where rownum>0 and rownum<45;这样能查到44条数据,
可是我把前面那个条件 rownum>0 随便改成>1的就一条数据都查不到了.晕哦.不知道在回事
可是我把前面那个条件 rownum>0 随便改成>1的就一条数据都查不到了.晕哦.不知道在回事
(
select l.* , rownum cnt from users u left join log l on u.id=l.user_id where u.id = 1
) t
where cnt > 1 and cnt < 45select * from
(
select l.* , rownum cnt from users u left join log l on u.id=l.user_id where u.id = 1
) t
where cnt between 2 and 44
不能用>或者>=
如果要取1<rownum<45的话可以用 minus
select rownum from (select l.* from users u left join log l on u.id=l.user_id where u.id = 1)t where rownum<45
minus
select rownum from (select l.* from users u left join log l on u.id=l.user_id where u.id = 1)t where rownum<1;
rownum是本次查询结构的序号从1开始,你规定rownum > 1,数数怎么能从2开始
rownum > 0的条件也是没意义的,楼上说得很对rownum只能与<或者<=做比较另外,一定要避免rownum直接与order by连用,根据表的定义和数据,执行的先后顺序及结果是不确定的,
遇到这种情况要用两层查询来做
(
select l.* , rownum cnt from users u left join log l on u.id=l.user_id where u.id = 1
) t
where cnt > 1 and cnt < 45
select t.* from (select l.*,rownum rn from users u left join log l on u.id=l.user_id where u.id = 1 and rownum<=45 )t where rn<=44;
(SELECT ROWNUM R,e.* FROM (select l.* from users u left join log l on u.id=l.user_id where u.id = 1) e WHERE ROWNUM <= 1000) a where r>1
select * from
(SELECT ROWNUM R,e.* FROM (
select l.* from users u left join log l on u.id=l.user_id where u.id = 1
) e WHERE ROWNUM <= 45) a where r>1