PinkGuy / rownum和rowid伪列
Created 2019-11-25 Modifyd 2019-11-25

976 Words

伪列介绍：

  • 伪列不存在于任何一张表中，但是会被所有的表共享
  • 伪列可以从表中查询，但不能进行DML操作
  • 常用的伪列有ROWID和ROWNUM

涩图

现有一张stuInfo表，对该表进行操作—

      STUNO STUNAME                                      STUAGE      STUID    STUSEAT
---------- ---------------------------------------- ---------- ---------- ----------
     1 张三                                             18                     1
     2 李四                                             20                     2
     3 王五                                             15                     3
     4 张三                                             18                     4
     5 张三                                             20                     5

DISTINCT（去重）关键字

  • 不重复显示所有学员姓名和年龄

  • select DISTINCT stuName,stuAge From stuInfo

    STUNAME                                      STUAGE
---------------------------------------- ----------
王五                                             15
张三                                             20
张三                                             18
李四                                             20

可以看到少了一条学号4的数据；

查询学生姓名，年龄，rowid与rownum （注意需要给表加别名）

select rowid,rownum,s.stuname,s.stuage from stuInfo s

ROWID                  ROWNUM  STUNAME                                      STUAGE
-------------------------------------------------------------------------
AAARUyAAEAAAAGQAAA          1 张三                                             18
AAARUyAAEAAAAGQAAB          2 李四                                             20
AAARUyAAEAAAAGQAAC          3 王五                                             15
AAARUyAAEAAAAGQAAD          4 张三                                             18
AAARUyAAEAAAAGQAAE          5 张三                                             20

什么是网格

ROWNUM ：逻辑序列

  • 不用的sql语句在执行时，rownum的值不一致；

  • 相同的sql语句在执行时，rownum的值不变；（在第一次查询产生row，之后保持不变）

##### top-n查询

例：查询年龄最大的三条学生信息；

通常我们会想到

  select stuAge FROM stuInfo  
  where rownum <= 3   -- 第一次查询
  order by stuAge desc;	-- 然后再根据rownum进行排序

結果：

  STUAGE
  ------
      20
      18
      15

但是这种做法是错误的，要先进行排序；（根据第一次产生的结果查，有可能是错误的，）

  • 正确做法：

  • select rownum,stuname,stuage from (select * from stuInfo order by stuAge desc)
where rownum <= 3

    使用子查询 得到正确的rownum 排序 最后进行降序获取前三；

    （获取top-n类型查询语句通用做法）

什么是网格

ROWID ：物理序列（18位）

  • 根据插入的顺序，依次递增

    ROWID
------------------
AAARUyAAEAAAAGQAAA
AAARUyAAEAAAAGQAAB
AAARUyAAEAAAAGQAAC
AAARUyAAEAAAAGQAAD
AAARUyAAEAAAAGQAAE

前6位：数据对象编号；

后数3位：数据文件编号

后数6位：文件块编号

后数3位：行号

当对象满了后加一位；

存储情况

删除重复stuage数据

通常是根据id来删除，当id一样，，可以使用伪列（AAARUyAAEAAAAGQAAA ，1）；

可是这样需要写两条sql，一条查询，一跳删除；

通过一条sql删除，当然有人会想。。上面讲了去重

delete from stuInfo where stuage in(
       select distinct stuage from stuInfo
);

这样是不行的，select distinct stuage from stuInfo 得到的是↓

STUAGE

    20
    18
    15

这样就成了删除（20,18,15）的学生了。。全删了。。

  • 正确做法：

思路：根据编号分组（将重复的数据分到一组）然后在每组中只保留一个（每组的最大或最小都只有一个）保留最小的；

  select s.stuage,rowid  stuage from stuinfo s

  STUAGE STUAGE

  18 AAARUyAAEAAAAGQAAA
  20 AAARUyAAEAAAAGQAAB
  15 AAARUyAAEAAAAGQAAC
  18 AAARUyAAEAAAAGQAAD
  20 AAARUyAAEAAAAGQAAE

 select stuage,min(rowid) from stuInfo group by stuage;

STUAGE MIN(ROWID)

    20 AAARUyAAEAAAAGQAAB
    18 AAARUyAAEAAAAGQAAA
    15 AAARUyAAEAAAAGQAAC

将每组里的最小值已经取出来了；

删除：

delete from stuInfo
not in(
	select stuage,min(rowid) from stuInfo group by stuage
)

————————————结束

涩图