※資料範例

假設我們的資料如下，資料依據此方式排序 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

RojerChen.2018.10.08

Given a binary matrix A, we want to flip the image horizontally, then invert it, and return the resulting image.

To flip an image horizontally means that each row of the image is reversed. For example, flipping [1, 1, 0] horizontally results in [0, 1, 1].

To invert an image means that each 0 is replaced by 1, and each 1 is replaced by 0. For example, inverting [0, 1, 1] results in [1, 0, 0].

Example 1:

Input: [[1,1,0],[1,0,1],[0,0,0]]
Output: [[1,0,0],[0,1,0],[1,1,1]]
Explanation: First reverse each row: [[0,1,1],[1,0,1],[0,0,0]].
Then, invert the image: [[1,0,0],[0,1,0],[1,1,1]]

Example 2:

Input: [[1,1,0,0],[1,0,0,1],[0,1,1,1],[1,0,1,0]]
Output: [[1,1,0,0],[0,1,1,0],[0,0,0,1],[1,0,1,0]]
Explanation: First reverse each row: [[0,0,1,1],[1,0,0,1],[1,1,1,0],[0,1,0,1]].
Then invert the image: [[1,1,0,0],[0,1,1,0],[0,0,0,1],[1,0,1,0]]

Notes:

1 <= A.length = A[0].length <= 20
0 <= A[i][j] <= 1

RojerChen.2018.09.12

You're given strings J representing the types of stones that are jewels, and S representing the stones you have. Each character in Sis a type of stone you have. You want to know how many of the stones you have are also jewels.

The letters in J are guaranteed distinct, and all characters in J and S are letters. Letters are case sensitive, so "a" is considered a different type of stone from "A".

Example 1:

Input: J = "aA", S = "aAAbbbb"
Output: 3

Example 2:

Input: J = "z", S = "ZZ"
Output: 0

Note:

S and J will consist of letters and have length at most 50.
The characters in J are distinct.

RojerChen.2018.08.31

今天在解 LeetCode 196. Delete Duplicate Emails 這題，剛好遇到了問題紀錄一下，以下是這題的題目。

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.

For example, after running your query, the above Person table should have the following rows:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

Note:

Your output is the whole Person table after executing your sql. Use delete statement.

訂閱：文章 ( Atom )