SQL练习（12/81）

目录

1.找类别最高值

使用子查询

使用窗口函数（MySQL8.X支持）

扩展：查找类别前N高的值

2.删除重复值并保留最小序号

delete实现

筛选无重复且序号最小值

select——where in

select——join&子查询

3.找带条件的连续值

窗口函数实现

日期比较函数

---

1.找类别最高值

184. 部门工资最高的员工 - 力扣（LeetCode）

错误的查询方式：

(看看都会写写全错版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);

问题分析

1. GROUP BY 和 SELECT 列不一致：

   在 SELECT 中选择了 department.name、employee.name 和 employee.salary，但在 GROUP BY 中只按 department.id 分组。这会导致语法错误，因为 GROUP BY 后的列必须与 SELECT 中的非聚合列一致。

2. HAVING 子句的使用：

   HAVING MAX(salary) 是不合法的，因为 HAVING 子句需要一个条件来过滤分组结果。没有提供一个具体的条件，只是写了 MAX(salary)。

3. 逻辑问题：

   目标是筛选每个部门的最高工资员工，但当前的查询逻辑无法实现这一点。需要先找到每个部门的最高工资，然后再关联员工表来获取对应的员工信息。

正确的查询方式：

使用子查询

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),departmentIdfrom employeegroup 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 rankFROM Employee eJOIN 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 rankFROM Employee eJOIN Department d ON e.departmentId = d.id
) AS ranked_employees
WHERE rank <= 3;

 

2.删除重复值并保留最小序号

196. 删除重复的电子邮箱 - 力扣（LeetCode）

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
WHEREp1.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 personGROUP BY email
);

• 子查询：SELECT email, MIN(id) FROM Person GROUP BY email 找到每个 email 的最小 id。这个子查询的结果是一个临时表，包含每个 email 和对应的最小 id。

• 主查询：通过 WHERE (email, id) IN (...) 筛选出 Person 表中那些 email 和 id 匹配子查询结果的记录。

select——join&子查询

SELECT p1.id, p1.email
FROM person p1
JOIN (SELECT email, MIN(id) AS min_idFROM personGROUP 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 找到每个 email 的最小 id。这个子查询的结果是一个临时表 p2，包含每个 email 和对应的最小 id。

• 主查询：通过 JOIN 将 person 表和子查询的结果 p2 连接起来，条件是 p1.email = p2.email AND p1.id = p2.min_id，这样可以筛选出每个 email 的最小 id 对应的记录。

（select力扣上运行不了但我觉得逻辑上是这样）

3.找带条件的连续值

197. 上升的温度 - 力扣（LeetCode）

窗口函数实现

# 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_recordDatefrom 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.

586. 订单最多的客户 - 力扣（LeetCode）

select customer_number
from (select customer_number,count(*) as cn from ordersgroup by customer_numberorder by cn DESC
) as subquery
limit 1

 

5.

511. 游戏玩法分析 I - 力扣（LeetCode）

select player_id,event_date as first_login
from activity
where (player_id,event_date) in(select player_id,min(event_date)from activitygroup by player_id
)