在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语句等等这些都是值得学习和深思的地方。
发表评论