over (partition by)

哎呀我的天吶發表於2015-05-18
班級表

點選(此處)摺疊或開啟

  1. select * from class;

求各個班級的第一名分數。

點選(此處)摺疊或開啟

  1. select *
  2.   from (select name,
  3.                class_no,
  4.                score,
  5.                rank() over(partition by class_no order by score desc) rank
  6.           from class)
  7.  where rank = 1


其中子查詢出來的資料如下：

點選(此處)摺疊或開啟

  1. (select name,
  2.                class_no,
  3.                score,
  4.                rank() over(partition by class_no order by score desc) rank
  5.           from class)


rank()和dense_rank()的區別是：
   --rank()是跳躍排序，有兩個第二名時接下來就是第四名
   --dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名


row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1660361/，如需轉載，請註明出處，否則將追究法律責任。

相關文章