【MySQL基础刷题】总结题型（一）

不重要的知识点

这里记录了几个自己刷题过程中的小tips

1. Type是varchar时统计长度，假如Column Name是content。那么就是char_length(content)
2. 遇到昨天，明天等，需要将日期+1的，用date_add。date_add(w1.recordDate, interval 1 day，w1表中的recordDate这一列加了一天

1.第二高的薪水

找第二高的薪水，有就返回，没有就返回null

思路：
想要第二高，需要排序，使用order by，默认就是asc升序，想要降序可以用desc；
去重，多个重复数据需要使用distinct去重；
判断临界输出，如果不存在第二高的薪水，查询应该返回null，使用ifNull；
查找第二大使用limit，limit(1,1)。这个数据是因为默认从0开始，所以第一个1是查询第二大的数，第二个1是表示往后显示多少条数据，这里只需要一条。所以是limit(1,1)

select ifNull((select distinct Salary from Employee order by Salary desc limit 1, 1), null) 
as SecondHighestSalary;

注意limit没有括号

2.分数排名

要求将score排名，从高到低，新加一列rank，score相等的rank排名也相等

引入窗口函数的做法

select score, 
	   dense_rank() over(order by score desc) as 'rank' 
from Scores;

• dense_rank()函数为结果集中的每一行返回其在指定列排序后的排名
• 与 rank() 函数类似，dense_rank () 在有相同值的行中会返回相同的排名，但排名之间不会有间隔。例如，如果两行并列第一，则下一行的排名就是第二，而不是第三。但 rank() 会将排名跳过，比如两行并列第一，下一行就是第三。
• 窗口函数通常与 over() 子句结合使用，over() 子句定义了窗口函数操作的数据窗口。

3.连续出现的数字

找出所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。

自连接，查看当前行与前两行的关系，是不是num相同，id差1和2

select distinct a.num as 'ConsecutiveNums'
from Logs a
join Logs b on a.id = b.id + 1 and a.num = b.num
join Logs c on a.id = c.id + 2 and a.num = c.num

3.部门工资最高的员工

查出每个部门中薪资最高的员工
输出要求：

SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
WHERE e.salary >= ALL (
    SELECT salary
    FROM Employee
    WHERE e.departmentId = departmentId
);

主查询从 e 表中选择所有员工，并与 d 表连接，获取部门名称。子查询从 Employee 表中选择同部门的所有薪资。通过 >= ALL 确定选出的员工在其所在部门中的薪资是最高的，即他们的薪资大于或等于他们部门中任何其他人的薪资。

4.部门工资前三高的人

相同工资的人排名相同
还是要用窗口函数
rank() 函数的特性是，当多个员工具有相同的工资时，它会给这些员工相同的排名，并且之后的排名会跳过相应的数量。这意味着如果两个或更多员工共享同一排名，随后的排名数字会相应地跳过。
所以这里用的不是rank()而是dense_rank()，他不会跳过任何排名数量

partition by是sql窗口函数的一个组成部分，用于执行窗口函数计算时对数据进行分组

窗口函数只能写在select语句中
where子句不能用于过滤使用窗口函数计算的结果，因为窗口函数在 where 子句处理后才执行。

select Department, Employee, salary
from(
    select d.name as Department, e.name as Employee, e.salary as salary,
           dense_rank() over(partition by e.departmentId order by e.salary desc) as salaryrank
    from Employee e left join Department d on e.departmentId = d.id
)ranked
where salaryrank <= 3;

这个ranked是指代的别名

5.游戏玩法分析II

有张activity表，列名有
现在需要找到每一个玩家首次登陆的设备名称，返回player_id和device_id

select player_id, device_id 
from Activity
where (player_id, event_date) in
(select player_id, min(event_date)
from Activity
group by player_id
)

6.游戏玩法分析III

还是activity表
报告玩家到 目前为止 玩了多少游戏。通过date累加的
还是要用窗口函数
sum() 窗口函数会计算截至每个 event_date 的 games_played 的累积总和。

select 
  player_id, 
  event_date, 
  sum(games_played) over (partition by player_id order by event_date asc) as games_played_so_far
from
  Activity
order by
  player_id, 
  event_date;

• sum(games_played) over (…)：这是一个窗口聚合函数，用于计算累积总和。
• partition by player_id表示窗口函数在每个player_id上独立计算。也就是说，每个玩家的游戏次数累加是独立的。
• order by event_date asc表示在每个玩家的分区内，累积计算是按照日期顺序进行的。从最早的记录开始，一直加到当前行日期
• 最后的 order by 确保结果首先按玩家ID排序，其次按日期排序，这使得结果更易读。

7.购买了A和B但没有购买C

Customers表和Orders表

现在要求输出买了A和B但没买C的顾客信息，返回格式如下：
customer_id | customer_name

select o.customer_id, c.customer_name 
from orders o left join customers c
on o.customer_id = c.customer_id
group by customer_id
having 
    SUM(if(product_name = 'A', 1, 0)) > 0 and
    SUM(if(product_name = 'B', 1, 0)) > 0 and
    SUM(if(product_name = 'C', 1, 0)) = 0

8.机器的进程平均运行时间

输入一张表：

输出每台机器的平均运行时间

select
    machine_id,
    round(2*avg(if(activity_type = 'start', -1, 1) * timestamp), 3) as processing_time
from Activity
group by machine_id;

9.每位学生的最高成绩

用窗口函数

select student_id, course_id, grade 
from 
(select *,
dense_rank() over (partition by student_id order by grade desc, course_id) rk 
from enrollments) t
where rk=1

dense_rank()用于分配唯一的排名给结果集中的每一行，当有相同行时，得到相同的排名，并且不跳过任何数字（rank(）会跳过）

partition by student_id order by grade desc， course_id
其中partition by意味着，排名是针对每个学生的，而不是整个结果集，所以说，每个学生都有自己独立的成绩排名
order by grade desc，course_id在为每个学生的成绩排名时，首先根据成绩降序，相同时根据课程ID升序

先select *是为了快速选择所有列，是为了方便
as rk为排名指定了一个别名

10.学生们参加各科测试的次数

输入学生表，学科表和考试表


输出学生的考试信息以及每门课考试的次数

select s.student_id, s.student_name, su.subject_name, count(e.subject_name) as attended_exams
from Students s join Subjects su left join Examinations e on
    e.student_id = s.student_id and e.subject_name = su.subject_name
group by student_id, su.subject_name
order by student_id, su.subject_name