1.第二高的薪水
SELECT IFNULL(
(SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1,1),NULL) AS SecondHighestSalary
- 思路:采用分页思想,用到了LIMIT函数和IFNULL函数
- LIMIT m,n从m+1开始取n个
- IFNULL(expression_1,expression_2) 如果expression_1为空就返回expression_2否则返回expression_1
- LIMIT m OFFSET n 表示跳过n个数据读取m条数据
2.第N高的薪水
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
#如果不减一,取得就是第n+1高的
set N=N-1;
RETURN (
# Write your MySQL query statement below.
SELECT IFNULL(
(SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET N),NULL) AS SecondHighestSalary
);
END
3.分数排名
Score |
Rank |
4.00 |
1 |
4.00 |
1 |
3.00 |
2 |
2.00 |
3 |
SELECT a.Score AS Score,b.Rank
FROM Scores a
LEFT JOIN
(
SELECT s.Score,@rank:=@rank+1 AS Rank from (
SELECT DISTINCT Score FROM Scores ORDER BY Score DESC
) s,(SELECT @rank:=0) r
) b
ON a.Score=b.Score
ORDER BY a.Score DESC
- 思路:先去重并排名,再用左连接去关联
- 定义变量
- 直接set @rank=0或者set @rank=0这里用不用“:”都可以
- 查询结果若被用为子查询则不可用set,在from后定义(SELECT @rank:=0),在查询中必须要带“:”,不带表示的是一个比较语句
4.连续出现的数字
Id |
Num |
1 |
1 |
2 |
1 |
3 |
1 |
4 |
2 |
5 |
1 |
6 |
2 |
select distinct Num as ConsecutiveNums
from (
select Num,
case
when @prev = Num then @count := @count + 1
when (@prev := Num) is not null then @count := 1
end as CNT
from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3
5.超过经理收入的员工
- 要求:Employee表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。
Id |
Name |
Salary |
ManagerId |
1 |
Joe |
70000 |
3 |
2 |
Henry |
80000 |
4 |
3 |
Sam |
60000 |
NULL |
4 |
Max |
90000 |
NULL |
SELECT a.Name as Employee FROM (
SELECT a.Name,a.Salary,b.Salary as SalaryB
from Employee a
LEFT JOIN Employee b
on a.ManagerId=b.id
WHERE a.ManagerId IS NOT NULL
) a
WHERE a.Salary>a.SalaryB
- 思路:利用左连接将数据查成如下格式,再比较一下即可(SalaryB为所属经理的薪水)
Name |
Salary |
SalaryB |
Joe |
70000 |
60000 |
Henry |
80000 |
90000 |
6.部门工资前三高的所有员工
- 思路:可以使用变量先排名在关联如第3题,此处提供一种全新解法
Id |
Name |
Salary |
DepartmentId |
1 |
Joe |
85000 |
1 |
2 |
Henry |
80000 |
2 |
3 |
Sam |
60000 |
2 |
4 |
Max |
90000 |
1 |
5 |
Janet |
69000 |
1 |
6 |
Randy |
85000 |
1 |
7 |
Will |
70000 |
1 |
SELECT e1.Salary,e1.name,e1.Department
FROM Employee AS e1
WHERE 3 >
(SELECT count(DISTINCT e2.Salary)
FROM Employee AS e2
WHERE e1.Salary < e2.Salary AND e1.Department = e2.Department)
ORDER BY e1.department,e1.salary desc
7.上升的温度
- 要求:给定一个 Weather 表,编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。
SELECT b.Id
FROM Weather as a,Weather as b
WHERE a.Temperature < b.Temperature and DATEDIFF(a.RecordDate,b.RecordDate) = -1;
-
思路:本题使用了DATEDIFF函数来计算两个日期的时间差。
+ DATEDIFF('2007-12-31','2007-12-30'); # 1
+ DATEDIFF('2010-12-30','2010-12-31'); # -1