oracle将查询结果声明为伪表,Oracle的伪列和伪表

ORACLE有几个函数专门用来产生伪列的,rownum,rowid,row_number(),rank,dense_rank,lan

样表:

SQL> select * from stu_score;

STU_NO     SUB_NO      SCORE

---------- ---------- ----------

1          1         99

2          1         98

3          1        100

4          2         88

5          2         87

6          2         88

7          3         99

8          3         88

9          3        100

9 rows selected.

rownum按行的顺序自动增加产生

SQL> select rownum, stu_score.* from stu_score;

ROWNUM     STU_NO     SUB_NO      SCORE

---------- ---------- ---------- ----------

1          1          1         99

2          2          1         98

3          3          1        100

4          4          2         88

5          5          2         87

6          6          2         88

7          7          3         99

8          8          3         88

9          9          3        100

9 rows selected.

rowid内部ID号,这个是真正存在表中的

SQL> select rownum from dual connect by rownum<10;

ROWNUM

----------

1

2

3

4

5

6

7

8

9

10

10 rows selected.

这个是一个比较有用的语句

SQL> select to_date('2006-03-31','yyyy-mm-dd')+rownum rn from dual connect by ro

wnum < 10;

RN

--------------

01-4月 -06

02-4月 -06

03-4月 -06

04-4月 -06

05-4月 -06

06-4月 -06

07-4月 -06

08-4月 -06

09-4月 -06

10-4月 -06

10 rows selected.

SQL> select rowid, stu_score.* from stu_score;

ROWID                  STU_NO     SUB_NO      SCORE

------------------ ---------- ---------- ----------

AAAMGBAAQAAAEdqAAA          1          1         99

AAAMGBAAQAAAEdqAAB          2          1         98

AAAMGBAAQAAAEdqAAC          3          1        100

AAAMGBAAQAAAEdqAAD          4          2         88

AAAMGBAAQAAAEdqAAE          5          2         87

AAAMGBAAQAAAEdqAAF          6          2         88

AAAMGBAAQAAAEdqAAG          7          3         99

AAAMGBAAQAAAEdqAAH          8          3         88

AAAMGBAAQAAAEdqAAI          9          3        100

9 rows selected.

给每个组内的不同记录进行排号,如果要查询每门课程成绩的前两名的记录,就可以用这个实现

SQL> SELECT stu_no,sub_no,score,row_number() over(PARTITION BY sub_no ORDER BY s

core DESC) rn FROM stu_score

2  ;

STU_NO     SUB_NO      SCORE         RN

---------- ---------- ---------- ----------

3          1        100          1

1          1         99          2

2          1         98          3

4          2         88          1

6          2         88          2

5          2         87          3

9          3        100          1

7          3         99          2

8          3         88          3

9 rows selected.

SQL> select * from

2  (SELECT stu_no,sub_no,score,row_number() over(PARTITION BY sub_no ORDER BY

score DESC) rn FROM stu_score)

3  WHERE rn<3;

STU_NO     SUB_NO      SCORE         RN

---------- ---------- ---------- ----------

3          1        100          1

1          1         99          2

4          2         88          1

6          2         88          2

9          3        100          1

7          3         99          2

6 rows selected.

与上面类似,略与不同,一看就知道了

SQL> SELECT stu_no,sub_no,score,RANK() over(PARTITION BY sub_no ORDER BY score D

ESC) rn FROM stu_score

2  ;

STU_NO     SUB_NO      SCORE         RN

---------- ---------- ---------- ----------

3          1        100          1

1          1         99          2

2          1         98          3

4          2         88

1         6          2         88

1         5          2         87

3         9          3        100          1

7          3         99          2

8          3         88          3

9 rows selected.

与RANK类似,可以看出区别来

SQL> SELECT stu_no,sub_no,score,dense_RANK() over(PARTITION BY sub_no ORDER BY s

core DESC) rn FROM stu_score;

STU_NO     SUB_NO      SCORE         RN

---------- ---------- ---------- ----------

3          1        100          1

1          1         99          2

2          1         98          3

4          2         88

1         6          2         88

1         5          2         87

2

9          3        100          1

7          3         99          2

8          3         88          3

9 rows selected.

对列进行偏移

SQL> SELECT stu_no,sub_no,score,lag(score,1,null) over(PARTITION BY sub_no ORDER

BY score DESC) rn FROM stu_score

2  ;

STU_NO     SUB_NO      SCORE         RN

---------- ---------- ---------- ----------

3          1        100

1          1         99        100

2          1         98         99

4          2         88

6          2         88         88

5          2         87         88

9          3        100

7          3         99        100

8          3         88         99

9 rows selected.