Mysql练习题-leetcode上所有题目总结

leetcode上所有数据库题目的练习和总结。

175. 组合两个表

1
2
3
SELECT FirstName, LastName, City,State 
FROM Person LEFT JOIN Address
ON Person.PersonId = Address.PersonId;

176. 第二高的薪水

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

这个题有两个注意点:

  • 如果只有一条数据时,应该;返回NULL,通过IFNULL()函数来实现,第一个参数如果没空,则输出第二个参数。
  • 除掉哪些相同的元素,比如表格中只有两个100时,应该返回NULL,而不是100。

177. 第N高的薪水

1
2
3
4
5
6
7
8
9
10
11
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET N
);
END

主要注意的点:

  • 不能在查询语句里面做计算,比如N-1,必须在开始的时候使用SET进行。
  • 相同的薪水只能算一次

178. 分数排名

1
2
3
SELECT Score, (SELECT COUNT(DISTINCT Score) FROM Scores s WHERE  Scores.score <= s.Score) AS Rank
FROM Scores
ORDER BY Score DESC;

这个题目的思路是:

通过找到大于等于当前分数的数量,即为当前分数的排名。

因为相同的分数排名并列,所以需要计数时需要使用DISTINCT去重。

180. 连续出现的数字

1
2
3
SELECT DISTINCT a.Num As ConsecutiveNums
FROM Logs a, Logs b, Logs c
WHERE a.id = b.id-1 AND b.id = c.id-1 AND a.Num = b.Num AND b.Num = c.Num;

需要注意重复元素,即可能存在4个连续相同的元素,这样就会重复输出,但是只需要一个。

181. 超过经理收入的员工

1
2
3
4
SELECT e1.Name AS Employee
FROM Employee e1 LEFT JOIN Employee e2
ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary;

182. 查找重复的电子邮箱

1
2
SELECT DISTINCT Email
FROM (SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email) > 1) e;

注意,FROM后面的表格一定需要给一个别名。

183. 从不订购的客户

1
2
3
SELECT Name AS Customers
FROM Customers
WHERE Id NOT IN (SELECT Customers.Id FROM Customers, Orders WHERE Customers.Id = CustomerId);

184. 部门工资最高的员工

1
2
3
SELECT d.Name AS Department,  e.Name AS Employee, Salary 
FROM Employee e, Department d
WHERE DepartmentId = d.Id AND Salary = (SELECT max(Salary) FROM Employee WHERE DepartmentId = d.Id);

185. 部门工资前三高的所有员工

1
2
3
4
SELECT d.Name Department, e.Name Employee, Salary
FROM Employee e, Department d
WHERE e.DepartmentId = d.Id
AND 3 > (SELECT COUNT(DISTINCT Salary) FROM Employee WHERE DepartmentId = d.Id AND Salary > e.Salary) ORDER BY d.Id ASC, Salary DESC;

196. 删除重复的电子邮箱

1
2
DELETE FROM Person WHERE Id NOT IN
(SELECT Id FROM (SELECT MIN(Id) AS Id FROM Person GROUP BY Email) e);

这个题目的思路是对Person表格按照Email进行分组,然后取每个分组里面Id最小那个Id组成一个表格,然后删除Id不在该表格中的数据。

197. 上升的温度

1
2
SELECT w1.Id AS Id FROM Weather w1 INNER JOIN Weather w2
ON dateDiff(w1.RecordDate, w2.RecordDate) = 1 AND w1.Temperature > w2.Temperature;

两个日期之间的差通过dataDiff()来求解。

262. 行程和用户

1
2
3
4
5
select temp.request_at Day,
round(sum(case temp.status when 'completed' then 0 else 1 end)/count(temp.status),2) 'Cancellation Rate'
from (select status,request_at from trips t left join users u on t.client_id = u.users_id where u.banned ='no') temp
where request_at between '2013-10-01' and '2013-10-03'
group by temp.request_at;

思路:

  • 首先连接这两个表格,条件用户没有被禁止,同时trips中用户的id等于Users表中的id
  • 然后对这个进行条件限制,只取规定时间段的数据,并对其按照时间分组。
  • 最后计算明天的取消率,统计当天总的订单数量,以及取消的订单数量,然后两者相除取两位小数。

595. 大的国家

1
2
3
SELECT name, population, area 
FROM World
WHERE area > 3000000 || population > 25000000;

596. 超过5名学生的课

1
2
3
4
SELECT class 
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

注意:可能存在一个同学重复选择同一门课,所以计数时需要去重。

601. 体育馆的人流量

1
2
3
4
5
SELECT DISTINCT s1.id, s1.visit_date, s1.people
FROM stadium s1, stadium s2, stadium s3
WHERE ((s1.id = s2.id-1 AND s2.id = s3.id-1) OR (s1.id = s2.id+1 AND s1.id = s3.id-1) oR (s1.id = s2.id+1 AND s2.id = s3.id+1))
AND s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100
ORDER BY s1.id;

思路:

三个表格自连接,考虑下标可能的三种情况。

620. 有趣的电影

1
2
3
4
SELECT id, movie, description, rating
FROM cinema
WHERE description <> "boring" AND id % 2= 1
ORDER BY rating DESC;

626. 换座位

1
2
3
4
5
6
7
8
SELECT (case 
WHEN MOD(id, 2) = 1 AND id = (SELECT COUNT(*) FROM seat)
THEN id
WHEN MOD(id, 2) = 1 THEN id+1
ELSE id -1
END) AS id, student
FROM seat
ORDER BY id;

627. 交换工资

1
2
3
4
5
UPDATE salary 
SET sex = (CASE
WHEN sex = 'm' THEN 'f'
ELSE 'm'
END);

1179. 重新格式化部门表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select 
id
, sum(case `month` when 'Jan' then revenue else null end) as Jan_Revenue
, sum(case `month` when 'Feb' then revenue else null end) as Feb_Revenue
, sum(case `month` when 'Mar' then revenue else null end) as Mar_Revenue
, sum(case `month` when 'Apr' then revenue else null end) as Apr_Revenue
, sum(case `month` when 'May' then revenue else null end) as May_Revenue
, sum(case `month` when 'Jun' then revenue else null end) as Jun_Revenue
, sum(case `month` when 'Jul' then revenue else null end) as Jul_Revenue
, sum(case `month` when 'Aug' then revenue else null end) as Aug_Revenue
, sum(case `month` when 'Sep' then revenue else null end) as Sep_Revenue
, sum(case `month` when 'Oct' then revenue else null end) as Oct_Revenue
, sum(case `month` when 'Nov' then revenue else null end) as Nov_Revenue
, sum(case `month` when 'Dec' then revenue else null end) as Dec_Revenue
from Department group by id;
zxp wechat
欢迎关注微信公众号!