正文
select case when if
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
select case when if 的一些用法 - 马丁传奇 - 博客园 https://www.cnblogs.com/martinzhang/p/3220595.html
Write a SQL query to get the second highest salary from the
Employee
table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the query should return
200
as the second highest salary. If there is no second highest salary, then the query should return
null
.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
IF (COUNT(Salary)=0 ,"null-str",Salary) AS SecondHighestSalary
if count null
SELECT
CASE COUNT(1)
WHEN 1 THEN
null
ELSE
CAST((
SELECT
Salary
FROM
Employee
GROUP BY
Salary
ORDER BY
Salary DESC
LIMIT 1,
1
) AS SIGNED INT )
END AS SecondHighestSalary
FROM
(
SELECT
COUNT(1) AS c,
Salary AS SecondHighestSalary
FROM
Employee
GROUP BY
Salary
ORDER BY
Salary DESC
) AS t;