RojerChen.2022.04.25

在練習這個題目的時候，學習到幾個我過去沒用過的語法：

1. ROW_NUMBER
2. RANK
3. DENSE_RANK
4. Partition

※資料範例

假設我們的資料如下，資料依據此方式排序 order by Department , Salary desc

※ROW_NUMBER、RANK、DENSE_RANK

ROW_NUMBER：簡單來說就是流水號

RANK：依據排序的條件遞增序號，遇到同樣的資料，下一筆跳號

DENSE_RANK：依據排序的條件遞增序號，同樣資料不跳號

語法使用方式：ROW_NUMBER() OVER( Order by Column desc)

使用語法後的效果是

select 
ROW_NUMBER() OVER ( ORDER BY Department , salary  DESC ) AS SN,
RANK() OVER ( ORDER BY Department , salary  DESC ) AS Rank ,
DENSE_RANK() OVER ( ORDER BY Department , salary  DESC ) AS DenseRank ,
* from table1
order by Department , salary desc

可以看到 RANK 的跳號方式與 DENSE_RANK 不同，資料相同就會跳號

※Partition

Partition：依據條件將資料分層，以下面的例子來說，依據Department來分層，所以就可以讓資料在排序上，IT 一組、Sales 一組

語法使用方式：ROW_NUMBER() OVER( Partition by Column Order by Column desc)

select 
DENSE_RANK() OVER ( Partition by Department order by Department,
salary desc) AS ParitionDenseRank ,
* from table1
order by Department , salary desc

※LeetCode

select * from table1 as e1
where salary in 
(
select top 3 salary
from table1 as e2
where e1.Department = e2.Department
group by salary
order by salary desc
)
order by Department , salary desc