LeetCode:185. Department Top Three Salaries

 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

    Blogger Comment

0 意見: