【SQL】Oracle和Mysql的分页、重复数据查询(limit、rownum、rowid)

上周三面试题有两道涉及oracle的分页查询，没有意外地凉了，现在总结一下。

 

· mysql

mysql的分页可以直接使用关键字limit，句子写起来比较方便。

 

语法：

① limit m,n

-- (m+1)为取出行的起始序号
-- n 为取出的全部行数
select * from table where...
limit m,n

-- 上述语句等价于
select * from table shere...
limit pagesize offset firstindex

 

示例：

-- 显示student表31~50行，共20行的结果
select * from student limit 30,50

-- 或者
select * from student limit 50 offset 30

 

② limit m

-- 表示从第一条记录行开始取出m条数据
select * from table where...
limit m

 

示例：

-- 表示 85分以上的前十名学生
select * from student where score>85 order by score desc
limit 10

 

 · oracle

oracle有两个方法进行分页，一个是row_number() over函数，一个是自带的rownum关键词。

 

①rownum（伪行列）

rownum表示一条记录的行数，如果需要分页，至少有两层查询，内层查询符合条件的全部rownum，分页信息在外层控制。

注意：rownum是对结果集的编序排列，始终是从1开始，所以rownum直接使用时不允许使用>号

select * from
　　(select a.*,rownum rn from(sql) a 
　　where rownum<=(firstindex+pagesize)) -- 内层控制最大值
where rn>firstindex -- 外层控制最小值

 

示例，结果集每页20行，现在要展示第二页的数据（即21~40行）：

select * from  
　　(  
　　select a.*, rownum rn  
　　from (select * from table_name) a  
　　where rownum <= 40  -- 末序号，20*2 
　　)  
where rn >= 21 -- 起始序号

 

②row_number() over函数

通常更多地应用于排序的场景。例如，根据成绩倒叙，选取前21~40名学生

select * from
　　(select a.*,row_number() over(order by a.score desc) ordernum 
　　from student a)
where ordernum between 21 and 40

 

③ rowid

rowid是数据的详细地址（表示每一列对应的十六进制物理地址值），通过rowid，oralce可以快速的定位某行具体的数据的位置。

对于同一条记录, 查询条件不同, rownum会不同, 但是rowid将不变。通常rowid可用于剔除重复数据

 

一、重复数据根据单字段判断

-- 首先查出该字段重复的数据
select * from [table] group by [id] having count[id] > 1

-- 字段重复的数据中，找出rowid较小的那些数据
select min(rowid) from [table] group by [id] having count(*) > 1

-- 删除多余的数据，只保留重复数据中rowid较小的那个
delete * from [table]
where [id] in ( select * from [table] group by [id] having count[id] > 1 )
and
rowid not in ( select min(rowid) from [table] group by [id] having count(*) > 1 )

 

二、重复数据根据多字段判断

-- 首先查出多字段重复的数据
select * from 表 a
where (a.[id],a.[field]) in
(
select [id], [filed] from [table]  
group by [id],[field] having count(*) > 1
)

-- 删除多余的数据，且rowid为小的
delete from 表 a 
where (a.id,a.[field]) in 
(select id,[field] from 表 group by id,[field] having count(*) > 1) 
and 
rowid not in (select min(rowid) from 表 group by id,[field] having count(*)>1)

 

sqlserver的top分页法

select top num from table_name
where...

 举例：

选择student表中score排行最高的10位

select top 10 from student order by score