在Leetcode网刷了几个数据库题目，顿感渣渣，有好几题都copy了别人的答案，有点无力啊。做个备忘吧，前事不忘。Leetcode只能提交mysql语法的sql语句。
1.列出工资比经理更高的雇员
Employee表，包含了所有雇员及他们的经理。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
如上表所示，工资比经理更高的雇员：
+----------+
| Employee |
+----------+
| Joe |
+----------+
主要是表的自连接，看成雇员表与经理表关联比较
select e.Name as Employee from Employee e,Employee m where e.ManagerId = m.id and e.Salary > m.Salary
写成join on格式
select e1.Name from Employee e1 join Employee e2 on e1.ManagerId = e2.Id and e1.Salary>e2.Salary
2.找出重复的电子邮件
Person表数据如下：
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
如上表，所给示例答案：
+---------+
| Email |
+---------+
| a@b.com |
+---------+
一种方法就是按email分组，并查找总数大于1的email
select email from Person group by email having count(email) > 1
还可以自连接，查找email相同但不同主键的email
SELECT distinct p1.Email from Person p1 INNER JOIN Person p2 ON p1.Email = p2.Email WHERE p1.Id <> p2.Id;
3.删除重复的电子邮件
Person表，数据如下：
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
保留Id最小，电子邮件唯一的数据，示例：
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
采用自连接，可以删除email相同但Id更大的数据
delete a from Person a,Person b where a.email = b.email and a.id > b.id
当然，也可以先查出所有不同email中的最小Id，然后删除剩下的数据
delete from Person where Id not in
(select Id
from
(select min(Id) as Id
from Person
group by Email
) p
);
4.查询没有下过订单的客户
Customers表
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders表
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
示例答案：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
主要判断一个表的数据在不在另一个表里，可以写成很多：
select c.Name from Customers c where c.Id not in (select customerId from Orders)
select c.Name from Customers c where (select count(*) from Orders o where o.customerId=c.id)=0
select c.Name from Customers c where not exists (select * from Orders o where o.customerId=c.id)
select c.Name as Customers from Customers c left join Orders o on c.Id=o.CustomerId where o.CustomerId is null
5.查询所有气温比前一天高的日期id
Weather表
+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------+------------------+
示例答案：
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
也主要采用自连接方法
select today.id from Weather today, Weather yesterday where subdate(today.date,1)=yesterday.date and today.Temperature > yesterday.Temperature
mysql相隔一天的天数比较
datediff(today.date,yesterday.date)= 1 TO_DAYS(t1.Date) = TO_DAYS(t2.Date) + 1
6.查询第二高的工资
Employee表，如果没有第二高则返回null
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
一种方法取是除掉最大值后的最大值
SELECT max(Salary) FROM Employee WHERE Salary < (SELECT max(Salary) FROM Employee)
还可以利用mysql特有的limit语句
select (
select distinct Salary from Employee order by Salary Desc limit 1 offset 1
)as second
7.查询连续出现3次以上的数
Logs表，示例答案：1
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
自连接3个表
select distinct pre.num from Logs pre, Logs cur, Logs nxt where pre.id = cur.id – 1 and pre.id = nxt.id – 2 and pre.num = cur.num and pre.num = nxt.num
采用变量的方式
select distinct r.num from
(select num,
case when @last = num then @count:=@count+1
when @last<>@last:=num then @count:=1
end as n
from Logs
) r ,(select @count:=0,@last:=(select num from Logs limit 0,1)) temp where r.n>=3
8.成绩分等级
Scores表
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
示例答案：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
排名可以看做排序后排在当前分数前面的个数
SELECT a.score,(select count(distinct score) from Scores b where a.score<= b.score) FROM Scores a order by score desc
利用group by实现
SELECT T2.Score Score, (SELECT COUNT(*) + 1 FROM (SELECT T1.Score FROM Scores T1 GROUP BY Score ORDER BY Score DESC) TEMP WHERE T2.Score < TEMP.Score) Rank FROM Scores T2 ORDER BY Score DESC;
9.列出各个部门的最高工资
Employee表
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department表
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
利用group by 和 max函数
select b.Name,a.Name,a.Salary from Employee a , Department b, (select max(Salary) Salary,DepartmentId from Employee group by DepartmentId) c where a.DepartmentId = b.id and c.DepartmentId = a.DepartmentId and a.Salary = c.Salary
其他实现
select b.Name Department, a.Name Employee, a.Salary from
(
select a.Name, a.Salary, a.DepartmentId
from Employee a left outer join Employee b
on a.DepartmentId = b.DepartmentId
and a.Salary < b.Salary
where b.Id is null
) a join Department b
on a.DepartmentId = b.Id;
SELECT dep.Name as Department, emp.Name as Employee, emp.Salary from Department dep, Employee emp
where emp.DepartmentId=dep.Id and emp.Salary=(Select max(Salary) from Employee e2 where e2.DepartmentId=dep.Id)
10.列出各个部门的前三高工资
Employee表
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+
Department表
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
示例答案：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
用个数比较
Select dep.Name as Department, emp.Name as Employee, emp.Salary
from Department dep, Employee emp where emp.DepartmentId=dep.Id and
(Select count(distinct Salary) From Employee where DepartmentId=dep.Id and Salary>emp.Salary)<3
用复杂的join on语句，不利于扩展
SELECT
a. NAME AS Department,
g. NAME AS Employee,
g.Salary
FROM
Department a
INNER JOIN Employee g ON a.Id = g.DepartmentId
LEFT JOIN (
SELECT
e.DepartmentId,
max(e.Salary) AS Salary
FROM
Employee e
INNER JOIN (
SELECT
c.DepartmentId,
max(c.Salary) AS Salary
FROM
Employee c
INNER JOIN (
SELECT
DepartmentId,
max(Salary) AS Salary
FROM
Employee
GROUP BY
DepartmentId
) b ON c.DepartmentId = b.DepartmentId
WHERE
c.Salary < b.Salary
GROUP BY
c.DepartmentId
) d ON e.DepartmentId = d.DepartmentId
WHERE
e.Salary < d.Salary
GROUP BY
e.DepartmentId
) f ON g.DepartmentId = f.DepartmentId
WHERE
f.DepartmentId IS NULL
OR g.Salary >= f.Salary
11.查询每日订单取消率
Trips表
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
Users表
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
按日期分组，主要算出每日的订单取消数
SELECT Request_at as Day,ROUND(COUNT(IF(Status != ‘completed’, TRUE, NULL)) / COUNT(*), 2) AS ‘Cancellation Rate’
FROM Trips
WHERE (Request_at BETWEEN ‘2013-10-01’ AND ‘2013-10-03’)
AND Client_id NOT IN (SELECT Users_Id FROM Users WHERE Banned = ‘Yes’)
GROUP BY Request_at
类似实现
select a.Request_at as Day, round( (select count(*) from Trips as b left join Users as c on b.Client_Id = c.Users_Id where (b.Status = ‘cancelled_by_client’ or b.Status =’cancelled_by_driver’) and c.Banned = ‘No‘ and b.Request_at = a.Request_at)/count(a.Status),2) as Cancellation_Ratefrom Trips as a left join Users as don a.Client_Id = d.Users_Id where d.Banned = ‘No‘ and a.Request_at >= date(“2013-10-01”) and a.Request_at <= date(“2013-10-03”) group by 1order by 1;
12.写个函数，返回第N高工资
Employee表，如果没有则返回null
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
利用limit语句
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select IF(count(*) >= N, Min(rank.Salary), NULL) salary
from (select distinct salary
from Employee
order by salary desc
limit N
) rank
);
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
SELECT e1.Salary
FROM (SELECT DISTINCT Salary FROM Employee) e1
WHERE (SELECT COUNT(*) FROM (SELECT DISTINCT Salary FROM Employee) e2 WHERE e2.Salary > e1.Salary) = N – 1 LIMIT 1
);
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select IF(count(*) >= N, Min(rank.Salary), NULL) salary
from (select distinct salary
from Employee
order by salary desc
limit N
) rank
);
END
总之，要写出一个华丽的SQL也不是一件容易的事，要尽可能少的查库，还要使查询的性能达到最优，如何优化SQL语句等等这些都是值得学习和深思的地方。
本文由 wenqy 创作，采用 知识共享署名4.0
国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: Nov 8,2020