【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,20
 
-- 或者
select * from student limit 20 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