SQL over的作用及用法

2022-09-23 01:22:03 字數 1692 閱讀 4090

sql over的作用及用法

rank ( ) over ( [query_partition_clause] order_by_clause )

dense_rank ( ) over ( [query_partition_clause] order_by_clause )

可實現按指定的欄位分組排序,對於相同分組欄位的結果集進行排序,

其中partition by 為分組欄位,order by 指定排序欄位

over不能單獨使用,要和分析函式:rank(),dense_rank(),row_number()等一起使用。

其引數:over(partition by columnname1 order by columnname2)

含義:按columname1指定的欄位進行分組排序,或者說按欄位columnname1的值進行分組排序。

例如:employees表中,有兩個部門的記錄:department_id =10和20

select department_id,rank() over(partition by department_id order by salary) from employees就是指在部門10中進行薪水的排名,在部門20中進行薪水排名。如果是partition by org_id,則是在整個公司內進行排名。

select *, row_number() over (partition by deptid order by salary desc) rank from employee

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

--row_number 還可以用查重複資料,1代表的是出現的次數,保留id2最大的,並把其他的刪除掉.

delete a from

(select row_number()over(partition by id1 order by id2 desc) orderid from #t1 ) a

where a.orderid>1

select * from (select row_number() over(order by id asc) as rownum,

idfrom company ) as d

where rownum between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize

order by id asc

with cte as(

select dense_rank() over(order by score desc) rank,* from @t

)select * from cte where rank < 6

sql over的作用及用法

from rank over query partition clause order by clause dense rank over...

SQL over的作用及用法

sql over的作用及用法 rank over query partition clause order by clause dense rank over query partition clause order by clause 可實現按指定的欄位分組排序,對於相同分組欄位的結果集進行排序 ...

SQL over的作用及用法

sql over的作用及用法 rank over query partition clause order by clause dense rank over query partition clause order by clause 可實現按指定的欄位分組排序,對於相同分組欄位的結果集進行排序 ...