13.Oracle 伪列 rownumrowid

西瓜咬人啦

于 2020-09-30 16:28:58 发布

阅读量208

点赞数

分类专栏： Oracle 文章标签： oracle sql

@TMQ

本文链接：https://blog.youkuaiyun.com/weixin_42076050/article/details/108887144

版权

Oracle 专栏收录该内容

13 篇文章

订阅专栏

本文介绍了Oracle数据库中rownum和rowid伪列的使用，rownum用于查询时生成序列，rowid则表示数据的磁盘地址。在处理数据重复问题时，通过rowid的唯一性进行删除操作。首先，通过查询找出重复记录，然后利用rowid的特性删除非分组最大rowid的行，从而实现数据去重。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle 伪列 rownum/rowid

这两个列在表结构中并没有，但是可以在查询中作为列名或者where条件存在！

rownum 在查询结果的基础上，自动添加一个从1开始自增的序列

select rownum, e.* from emp e where job = 'MANAGER';

-- 前三行
select e.* from emp e where rownum <= 3;

注意：

只能使用 = 1，< , <= ，其他>, >=, in, between全部不支持！！！

-- 分页（查找第4到第6行）
select * from (
    select rownum rn, e.* from emp e order by e.empno asc) t
        where t.rn between 4 and 6;

-- 这写法效率比上面要高！！！
select * from (
    select rownum rn, e.* from emp e where rownum <= 6 order by e.empno asc) t
        where t.rn >= 4;

表中数据行的查重处理

drop table t_student;

create table t_student (
    id int,
    name varchar(10),
    addr varchar(10)
);

insert into t_student values (1, 'Tom', 'nanjing');
insert into t_student values (2, 'Sam', 'shanghai');
insert into t_student values (3, 'Sam', 'nanjing');
insert into t_student values (2, 'Sam', 'shanghai');
insert into t_student values (4, 'Sophia', 'beijing');
insert into t_student values (4, 'Sophia', 'qingdao');
insert into t_student values (3, 'Sam', 'nanjing');
insert into t_student values (3, 'Sam', 'nanjing');

select * from t_student;

-- 求哪些人的姓名是有重复的
select name, count(1)
    from t_student
        group by name
            having count(1) > 1;

-- 求哪些人的学号和姓名都有重复
select id, name, count(1)
    from t_student
        group by id, name
            having count(1) > 1;
            
-- 所有列都有重复的行
select id, name, addr, count(1)
    from t_student
        group by id, name, addr
            having count(1) > 1;

-- 删除所有重复的行（不管重复多少次，最后保留一行即可）
-- 由于没有主键，无法通过where条件去指定删除的列值！！！

这时，就引出rowid这个伪列，它是物理数据在磁盘上的唯一坐标地址！

select rowid, s.* from t_student s;

select rowid, e.* from scott.emp e;
select rowid, d.* from scott.dept d;

-- 两个表的rowid出现在同一个查询语句中，需要限定表别名引用！
select e.rowid, d.rowid, e.ename, d.dname
    from emp e join dept d
        on e.deptno = d.deptno;

删重：

在没有主键的情况下，只能利用rowid的唯一性，进行条件删除！

如果你的表中有主键，那么除了主键以外其他所有列值都相同，就可以使用主键作为删除条件！


-- 1、先查询出该表中所有行（已经去除重复行），得到每个组中最小或者最大的rowid
select max(rowid), id, name, addr, count(1)
    from t_student
        group by id, name, addr;

select max(rowid)
    from t_student
        group by id, name, addr;

-- 2、只要不是我们分组结果中最小或者最大的rowid的行全部删除，因为他们肯定是组中重复的行
delete from t_student
    where rowid not in (
        select max(rowid)
            from t_student
                group by id, name, addr
    );

select * from t_student;