17,086
社区成员
发帖
与我相关
我的任务
分享oracle多表查询中如何用rownum分页？
我要实现分页，有两表： info 和 salary。user_id 为info 的主码，salary 的外码。现将两表连接查询，之后选出第１到５行。因此，order 应放于子查询之外，对吧？可下边的程序通不过。其中子查询ok了，就是外边的列名不认。我该怎么写？我试了在列名前加上表名，也不成。
下边仅 user_id 和user_name 是info 表中的。
select rownum, user_id, user_name, user_dept,
user_position, user_salary from
( select info.user_id, info.user_name, salary.*
from info, salary where info.user_id=salary.user_id
order by info.user_id )
where rownum between 1 and 5;
下边仅 user_id 和user_name 是info 表中的。
select rownum, user_id, user_name, user_dept,
user_position, user_salary from
( select info.user_id, info.user_name, salary.*
from info, salary where info.user_id=salary.user_id
order by info.user_id )
where rownum between 1 and 5;
...全文
请发表友善的回复…
发表回复
dawnzdy 2003-04-29
- 打赏
- 举报
楼上的说的对，可这仍是单表查询啊。我自己搞定了。谢谢各位：）
select * from
(select rownum rid,user_id,user_name,user_dept,user_position,user_salary
from
(select info.user_name,salary.*
from zdy_userinfo info, zdy_salary salary
where info.user_id=salary.user_id
order by info.user_id )
) where rid between 1 and 5;
在二层查询中不可用表名，无效，只需用列原名即可。
二层查询中不该有info_id,否则外部的user_id成为未明确定义的列。
select * from
(select rownum rid,user_id,user_name,user_dept,user_position,user_salary
from
(select info.user_name,salary.*
from zdy_userinfo info, zdy_salary salary
where info.user_id=salary.user_id
order by info.user_id )
) where rid between 1 and 5;
在二层查询中不可用表名，无效，只需用列原名即可。
二层查询中不该有info_id,否则外部的user_id成为未明确定义的列。
dawnzdy 2003-04-29
- 打赏
- 举报
感谢几位的热心，问题我自己解决了，版主，我要结贴，这分奖励给我吧。呵呵
dawnzdy 2003-04-28
- 打赏
- 举报
我试了，可第一行到了第二个a，就是那个a.user_id就又不认识了。
thank you all the same:)
thank you all the same:)
nicholaz 2003-04-28
- 打赏
- 举报
select a.rm, a.user_id, a.user_name, a.user_dept,a.user_position,a.user_salary
from ( select rownum rm,info.user_id, info.user_name, salary.*
from info, salary where info.user_id=salary.user_id
order by info.user_id ) a
where rm between 1 and 5;
from ( select rownum rm,info.user_id, info.user_name, salary.*
from info, salary where info.user_id=salary.user_id
order by info.user_id ) a
where rm between 1 and 5;
dawnzdy 2003-04-28
- 打赏
- 举报
谢谢两位，可是我试了，还是一样的错误：首行user_id 说是未定义的列！到底是怎么回事呢？
bjshasha 2003-04-28
- 打赏
- 举报
我搞定了
like this:
select * from (select rownum as m,a.* from scc_user a) where m between 3 and 8
like this:
select * from (select rownum as m,a.* from scc_user a) where m between 3 and 8
beckhambobo 2003-04-25
- 打赏
- 举报
select rm, user_id, user_name, user_dept,user_position,user_salary
from ( select rownum rm,info.user_id, info.user_name, salary.*
from info, salary where info.user_id=salary.user_id
order by info.user_id )
where rm between 1 and 5;
from ( select rownum rm,info.user_id, info.user_name, salary.*
from info, salary where info.user_id=salary.user_id
order by info.user_id )
where rm between 1 and 5;
