【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