SQL Server 中ROW_NUMBER() OVER基本用法

項目中遇到的分頁情況，用傳統(tǒng)SQL select top 10 from a where guid not in (select top 10 from a) 這種分頁 一但添加條件 數(shù)據(jù)量在百萬級的話 執(zhí)行的會很慢 ，如果加入ROW_NUMBER效率 會有大幅提升?；驹硎菫閟ql構造一個自己的默認序號，外圍SQL 通過查詢這個已經(jīng)排列好的序列號 ，就可實現(xiàn)分頁 序號>1000 and 序號<2000 ，也就是1000-2000內的數(shù)據(jù)。

?

實際項目中應用的SQL：

select?*?from
(
select?ROW_NUMBER()over(order?by?[基金賬號])?序號,

'0'?as?checkid,?a.行名?as?支行編號,?a.[Guid],a.[基金賬號],a.姓名,a.證件號碼,?a.理財師ID,?a.聯(lián)系電話?聯(lián)系電話,?a.是否有效,

CASE?when?c.理財師姓名?is?null?then?'否'?else?'是'?end?as?是否分配,

CASE?when?a.是否邀約?is?null?then?'否'?else?'是'?end?as?是否邀約,

a.分配時間,a.診斷時間,

case?when?b.理財師姓名?is?null?then?'--'?else?b.理財師姓名?end?as?所屬理財師?,

case?when?a.理財師工作證號?is?null?then?'--'?else?a.理財師工作證號?end?as?所屬理財師工作證號?,

case?when?(select?top?1?序列號?from?序列號?where?理財師工作證號=b.理財師工作證號?and?理財師工作證號?<>?'')

is?null?then?'--'?else?(select?top?1?序列號?from?序列號?where?理財師工作證號=b.理財師工作證號?and?理財師工作證號?<>?''

)

end?as?所屬理財師序列號,

case?when?c.理財師姓名?is?null?then?'--'?else?c.理財師姓名?end?as?分配理財師,

case?when?c.理財師工作證號?is?null?then?'--'?else?c.理財師工作證號?end?as?分配理財師工作證號,

case?when?c.序列號?is?null?then?'--'?else?c.序列號?end?as?分配理財師序列號

from

客戶視圖?a

left?join?理財師?b?on?a.理財師工作證號=b.理財師工作證號

left?join?序列號?c?on?a.理財師序列號=c.序列號

left?join?理財師?d?on?c.理財師工作證號=d.理財師工作證號

left?join?機構字典?e?on?a.行名=e.代碼

where?c.理財師姓名?like?'%谷谷~~~%'

)?a?where?a.序號>0?and?a.序號<=1000

?

為方便理解再重新寫一個簡單的分頁

建表和數(shù)據(jù)

?

數(shù)據(jù)較少,只查6-10的5條數(shù)據(jù).

select?*?from?(
select?ROW_NUMBER()over(?order?by?id1)?orderid,*?from?#t1
)?a?where?a.orderid?between?6?and?10

?

ROW_NUMBER 還可以用查重復數(shù)據(jù)，1代表的是出現(xiàn)的次數(shù),保留id2最大的,并把其他的刪除掉.

delete?a?from?
(select?ROW_NUMBER()over(partition?by?id1?order?by?id2?desc)?orderid?from?#t1?)?a
where?a.orderid>1

?

其中partition翻譯為分區(qū) 分組,可以理解為group by

查詢語句

select?ROW_NUMBER()?over(order?by?id1)?odid,*?from?#t1
select?ROW_NUMBER()?over(partition?by?id1?order?by?id1)?odid,*?from?#t1
select?ROW_NUMBER()?over(partition?by?id1,id2?order?by?id1)?odid,*?from?#t1
select?ROW_NUMBER()?over(partition?by?id1,id2,id3?order?by?id1)?odid,*?from?#t1

對應結果分別為

? ?? ?? ?

通過結果看,跟group by的效果差不多,更具體點區(qū)別暫時還未找到,google了一下,英文能力有限,并沒有找到理想的答案,只知道group by在效率上要好一些,有空還是要找一下.

?

去重還有distinct

select?distinct?id1,id2,id3?from?#t1

select?*?from?(
select?ROW_NUMBER()?over(partition?by?id1,id2,id3?order?by?id1)?odid,*?from?#t1?)a
where?a.odid<2

結果都一樣,只不過,distinct無法獲取重復的項,如果大數(shù)據(jù)量去重的話,不知道效率如何,有待比較.