目录
1.找类别最高值
错误的查询方式：
(看看都会写写全错版T^T)
SELECT department.name AS Department, employee.name AS Employee, employee.salary AS Salary
FROM employee
JOIN department ON employee.departmentId = department.id
GROUP BY department.id
HAVING MAX(salary);问题分析
在GROUP BY和SELECT列不一致：SELECT中选择了department.name、employee.name和employee.salary，但在GROUP BY中只按department.id分组。这会导致语法错误，因为GROUP BY后的列必须与SELECT中的非聚合列一致。HAVING子句的使用：HAVING MAX(salary)是不合法的，因为HAVING子句需要一个条件来过滤分组结果。没有提供一个具体的条件，只是写了MAX(salary)。逻辑问题：
目标是筛选每个部门的最高工资员工，但当前的查询逻辑无法实现这一点。需要先找到每个部门的最高工资，然后再关联员工表来获取对应的员工信息。
正确的查询方式：
使用子查询
select department.name as Department,employee.name as Employee,employee.salary as Salary
from employee
join department on employee.departmentId = department.id
where (employee.salary,employee.departmentId) in(
select max(salary),departmentId
from employee
group by departmentId
)
子查询
SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId找到每个部门的最高工资。外层查询通过
WHERE (departmentId, salary) IN (...)筛选出这些最高工资对应的员工。
使用窗口函数（MySQL8.X支持）
SELECT Department, Employee, Salary
FROM (
SELECT
d.name AS Department,
e.name AS Employee,
e.salary AS Salary,
RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rank
FROM Employee e
JOIN Department d ON e.departmentId = d.id
) AS ranked_employees
WHERE rank = 1;
使用窗口函数
RANK()，对每个部门的员工按工资降序排名。
PARTITION BY employee.departmentId：按departmentId分组
ORDER BY employee.salary DESC：按工资降序排列，工资最高的员工排名为1。
外层查询筛选出排名为1的员工，即每个部门的最高工资员工。
扩展：查找类别前N高的值
185. 部门工资前三高的所有员工 - 力扣（LeetCode）
使用方法2窗口函数进行推广
/* Write your T-SQL query statement below */
SELECT Department, Employee, Salary
FROM (
SELECT
d.name AS Department,
e.name AS Employee,
e.salary AS Salary,
dense_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rank
FROM Employee e
JOIN Department d ON e.departmentId = d.id
) AS ranked_employees
WHERE rank <= 3;2.删除重复值并保留最小序号
delete实现
-- 用自连接：
delete p1
from person p1
join person p2 on p1.email=p2.email and p1.id>p2.id
-- 不用自连接
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
筛选无重复且序号最小值
select——where in
SELECT id, email
FROM person
WHERE (email, id) IN (
SELECT email, MIN(id)
FROM person
GROUP BY email
);
子查询：
SELECT email, MIN(id) FROM Person GROUP BY email找到每个id。这个子查询的结果是一个临时表，包含每个id。主查询：通过
WHERE (email, id) IN (...)筛选出Person表中那些id匹配子查询结果的记录。
select——join&子查询
SELECT p1.id, p1.email
FROM person p1
JOIN (
SELECT email, MIN(id) AS min_id
FROM person
GROUP BY email
) p2 ON p1.email = p2.email AND p1.id = p2.min_id;
子查询：
SELECT email, MIN(id) AS min_id FROM person GROUP BY email找到每个id。这个子查询的结果是一个临时表p2，包含每个id。主查询：通过
JOIN将person表和子查询的结果p2连接起来，条件是p1.email = p2.email AND p1.id = p2.min_id，这样可以筛选出每个id对应的记录。
（select力扣上运行不了但我觉得逻辑上是这样）
3.找带条件的连续值
窗口函数实现
# Write your MySQL query statement below
select id as Id from (
select id,temperature,recordDate,
LAG(temperature,1) over (order by recordDate) as pre_temperature,
LAG(recordDate,1) over (order by recordDate) as pre_recordDate
from weather
) as queryweather
where pre_temperature<temperature and DATEDIFF(recordDate,pre_recordDate)=1
子查询:
LAG(temperature, 1) OVER (ORDER BY recordDate) AS pre_temperature：获取前一天的温度。LAG(recordDate, 1) OVER (ORDER BY recordDate) AS pre_recordDate：获取前一天的日期。主查询：
WHERE pre_temperature < temperature：确保当前温度高于前一天的温度。AND DATEDIFF(recordDate, pre_recordDate) = 1：确保当前日期和前一天的日期是连续的。
日期比较函数
DATEDIFF()：计算两个日期之间的天数差SELECT DATEDIFF('2025-05-18', '2025-05-10') AS days_difference;DATE_ADD()和DATE_SUB()：在日期上加上或减去指定的时间间隔SELECT DATE_ADD('2025-05-18', INTERVAL 10 DAY) AS future_date; SELECT DATE_SUB('2025-05-18', INTERVAL 10 DAY) AS past_date;
后面两题都用到了之前遇到过的知识点，就不过多赘述啦
（其实是靠自己写出来了 嘿嘿，虽然他们都是简单的题）
4.
select customer_number
from (
select customer_number,count(*) as cn
from orders
group by customer_number
order by cn DESC
) as subquery
limit 1
5.
select player_id,event_date as first_login
from activity
where (player_id,event_date) in(
select player_id,min(event_date)
from activity
group by player_id
)