Oracle中的行号(rownum)不能为>,>=的原因:
因为Oracle数据库是一个行式数据库,取了第一行才能取第二行,取了第二行才能取到第三行,所以行号从Oracle中永远从第一行开始,既然永远从第一行开始,>,>=的条件永远为假
案例一:分页查询显示员工信息:显示员工号,姓名,月薪
分页查询:
select rownum, r, tmpid, tname, sal, bumen
from (select rownum r, tmpid, tname, sal, bumen
from (select t.tmpid, t.tname, t.bumen, sal, rownum
from tmp t
order by sal desc) table1
where rownum <= 8) table2
where r >= 5;案例二:找到员工表中薪水大于本部门平均薪水的
select t.tmpid,t.tname,t.sal,d.avgsal
from tmp t ,(select bumen,avg(sal) avgsal from tmp group by bumen) d
where t.bumen = d.bumen and t.sal > d.avgsal;
查询执行计划语句(耗费了多少系统资源):
explain plan for –执行
select * from username
select * from table(dbms_xplan.display);–查询
案例三:
select ci.ci_id, to_char(wm_concat(stu.stu_name)) STU_NAME
from pm_ci ci, PM_STU stu
where instr(ci.stu_ids, stu.stu_id) > 0
group by ci.ci_id