leetcode 176. 第二高的薪水

使用limit/row_number()解决

题干

编写一个 SQL 查询,获取Employee表中第二高的薪水(Salary) 。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

 

例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

 

实现

MySQL

SELECT(
    SELECT DISTINCT
        Salary
    FROM
        Employee
    ORDER BY Salary DESC
    LIMIT 1 OFFSET 1
)AS SecondHighestSalary;
SELECT
    IFNULL((
        SELECT DISTINCT 
            Salary
        FROM
	    Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

 

MS SQL SERVER

SELECT
    NULLIF((
        SELECT
            Salary 
        FROM
            ( SELECT Salary, row_number () OVER ( ORDER BY Salary DESC ) AS rn FROM ( SELECT DISTINCT Salary FROM Employee ) g ) a 
        WHERE
            rn = 2 
    ),NULL)
AS SecondHighestSalary
SELECT
    max( Salary ) SecondHighestSalary 
FROM
    ( SELECT Salary, dense_rank() over ( ORDER BY Salary DESC ) rn FROM Employee ) t 
WHERE
    t.rn =2

 

Oracle

SELECT
    max( Salary ) SecondHighestSalary 
FROM
    ( SELECT Salary, dense_rank() over ( ORDER BY Salary DESC ) rn FROM Employee ) t 
WHERE
    t.rn =2

 

暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇