mysql数据库练习题（1）

leetcode数据库刷题分享

前言

为了准备秋招，在leetcode进行刷题，在csdn这个平台进行记录和输出。（下面有10道题）

1.超出经理收入的员工

SELECT
    a.Name AS 'Employee'
FROM
    Employee AS a,
    Employee AS b
WHERE
    a.ManagerId = b.Id
    AND a.Salary > b.Salary
;

理解：简单题，where后面的为筛选条件，

2.查找重复的电子邮件

select Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic
where num > 1;

理解：使用group by按照电子邮件进行计数并找出数量大于1的电子邮件

3.查找每个部门工资最高的员工

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

理解：中等题，in后面的子查询是按照部门分类，查出每个部门最高的工资对应的DepartmentId和Salary，然后作为两个表连接的子查询

4.删除重复的电子邮件

DELETE p1 
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id;

理解：与自身的邮箱连接起来，使用where找到重复的邮箱，条件是邮箱相同，id不同，然后使用delete进行删除；

5.上升的温度

select
    t1.id "ID"
from Weather t1,Weather t2
where datediff(t1.RecordDate, t2.RecordDate) = 1 and t1.Temperature > t2.Temperature
;

理解：使用datediff找出时间相差一天的日期

6.找出玩家第一次登陆的设备名称

select a.player_id ,a.device_id 
from Activity a 
where (a.player_id ,a.event_date) 
in
(select player_id,min(event_date) as first_login from Activity group by player_id)

理解：in后面使用子查询，查出玩家首次登陆的id和日期

7.玩家到目前为止玩了多少游戏

select t1.player_id,
       t1.event_date,
       sum(t2.games_played) games_played_so_far
from Activity t1,Activity t2
where t1.player_id=t2.player_id
  and t1.event_date>=t2.event_date
group by t1.player_id,t1.event_date;

理解：中等难度找到时间在自身之前的数据用sum()配合分组语句group by对两个主键进行分组并求和

8.至少有5名下属的经理

SELECT
    Name
FROM
    Employee AS t1 JOIN
    (SELECT
        ManagerId
    FROM
        Employee
    GROUP BY ManagerId
    HAVING COUNT(ManagerId) >= 5) AS t2
    ON t1.Id = t2.ManagerId
;

理解：使用临时表进行连接，首先，我们只需使用此 ManagerId 列就可以获取拥有 5 个以上直接下属的经理的 ID，然后，我们可以通过将该表与 Employee 表连接来获取该经理的名称。

9.统计各专业的学生数

select d.dept_name,ifnull(count(s.student_id),0) as student_number from department d
left join student s 
on d.dept_id = s.dept_id 
group by d.dept_name
order by student_number desc,d.dept_name asc;

理解：left join+count

10.超出5名学生的课

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