- 论坛徽章:
- 1
|
本帖最后由 hanfeng122525 于 2014-05-12 14:30 编辑
SQL> select e.*,rownum as rn from (select * from wages_table order by base_wage desc) e;
USERID NAME BASE_WAGE LIVE_SUBSIDE CHU_QIN_TIAN_SHU CHU_QIN_AWARD FINAL_WAGE RU_ZHI RN
---------- -------------------- ---------- ------------ ---------------- ------------- ---------- ---------- ----------
4 luzhenyu 4000 600 25 500 5100 2012-10-21 1
5 xiaoxue 3500 500 26 520 4520 2013-10-01 2
6 chenqian 3500 500 26 520 4520 2013-10-01 3
2 wangkang 3000 680 20 400 4100 2013-01-23 4
3 lijunguang 2500 400 30 600 3500 2013-03-24 5
9 zhenshen 2400 21 420 2820 2014-02-26 6
7 wuzhen 2200 400 31 620 3220 2014-03-27 7
8 chenzhen 2100 0 15 300 2400 2014-03-15 8
1 hanfeng 2000 340 22 440 2780 2014-01-01 9
SQL> select * from ( select e.*,rownum as rn from (select * from wages_table order by base_wage desc) e where rownum <=7) where rn >=5;
USERID NAME BASE_WAGE LIVE_SUBSIDE CHU_QIN_TIAN_SHU CHU_QIN_AWARD FINAL_WAGE RU_ZHI RN
---------- -------------------- ---------- ------------ ---------------- ------------- ---------- ---------- ----------
3 lijunguang 2500 400 30 600 3500 2013-03-24 5
9 zhenshen 2400 21 420 2820 2014-02-26 6
7 wuzhen 2200 400 31 620 3220 2014-03-27 7
在使用rownum时,查询整张表,要用到如代码中的 e.*,请各位能不能帮我解释下 e.* 这个概念?万分感谢,如有实例更好!e 是子查询临时表的别名。
从整个查询结果来看 e.* 也是 子查询的全部结果。就是这个概念以及用法不理解。
以上是返回整张表的数据;下面是部分列的数据,这两个有什么区别?当然,在下面的例子里也可以将整张表的列写出来,也能达到与上面例子的效果,但是为什么整张表的用e.* ?
SQL> select * from (select rownum rn,userid,name,base_wage from (select userid,name,base_wage from wages_table order by base_wage desc) where rownum <= 7) where rn >= 3;
RN USERID NAME BASE_WAGE
---------- ---------- -------------------- ----------
3 6 chenqian 3500
4 2 wangkang 3000
5 3 lijunguang 2500
6 9 zhenshen 2400
7 7 wuzhen 2200
|
|