Mysql数据库刷题1

1、查找入职员工时间排名倒数第三的员工的所有信息

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

SELECT *
FROM employees e1
where 3 = 
(SELECT count(DISTINCT hire_date)  
FROM employees e2
WHERE e2.hire_date >= e1.hire_date)

distinct去重，那排名靠前并且同一天入职的员工只需要统计一次。

 

2、第二高的薪水。如果不存在第二高的薪水，那么查询应返回null

SELECT (SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1)

法二

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

3、分数排名

编写一个SQL查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。

SELECT Score,
(SELECT COUNT(DISTINCT Score)
FROM Scores
WHERE Score >= S.Score)
AS Rank
FROM Scores S
ORDER BY Score DESC