SQL Server 中ROW_NUMBER() OVER基本用法

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

?

實(shí)際項(xiàng)目中應(yīng)用的SQL：

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

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

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

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

a.分配時(shí)間,a.診斷時(shí)間,

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

case?when?a.理財(cái)師工作證號(hào)?is?null?then?'--'?else?a.理財(cái)師工作證號(hào)?end?as?所屬理財(cái)師工作證號(hào)?,

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

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

)

end?as?所屬理財(cái)師序列號(hào),

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

case?when?c.理財(cái)師工作證號(hào)?is?null?then?'--'?else?c.理財(cái)師工作證號(hào)?end?as?分配理財(cái)師工作證號(hào),

case?when?c.序列號(hào)?is?null?then?'--'?else?c.序列號(hào)?end?as?分配理財(cái)師序列號(hào)

from

客戶視圖?a

left?join?理財(cái)師?b?on?a.理財(cái)師工作證號(hào)=b.理財(cái)師工作證號(hào)

left?join?序列號(hào)?c?on?a.理財(cái)師序列號(hào)=c.序列號(hào)

left?join?理財(cái)師?d?on?c.理財(cái)師工作證號(hào)=d.理財(cái)師工作證號(hào)

left?join?機(jī)構(gòu)字典?e?on?a.行名=e.代碼

where?c.理財(cái)師姓名?like?'%谷谷~~~%'

)?a?where?a.序號(hào)>0?and?a.序號(hào)<=1000

?

為方便理解再重新寫(xiě)一個(gè)簡(jiǎn)單的分頁(yè)

建表和數(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 還可以用查重復(fù)數(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

查詢語(yǔ)句

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

對(duì)應(yīng)結(jié)果分別為

? ?? ?? ?

通過(guò)結(jié)果看,跟group by的效果差不多,更具體點(diǎn)區(qū)別暫時(shí)還未找到,google了一下,英文能力有限,并沒(méi)有找到理想的答案,只知道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

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