码趣

【复健日记】 重走 SQL 之旅 · 第二弹

2024/12/20 SQL LeetCode

0. 整体总结

其实这批都偏简单，与上一批相比，其实难度都达不到 EASY ，LeetCode 应该定义一批 BASIC 难度的才对吧……

1. 刷题笔记（LeetCode 难度: EASY ）

577. Employee Bonus

非常简单，但自己也犯了非常猪比的错误。判断空值只能用 IS NULL ，而不能用 bonus = NULL ！

Solution

SELECT e.name, b.bonus
FROM Employee e LEFT JOIN Bonus b
ON e.empId = b.empId
WHERE b.bonus IS NULL OR b.bonus < 1000;

584. Find Customer Referee

跟上一题差不多，简单。

My Solution

SELECT name
FROM Customer
WHERE referee_id IS NULL OR referee_id <> 2;

A Better Solution

评论区也有一种解法用到新知识 COALESCE(referee_id,0) <> 2 : replace NULL values with zero before checking whether it is equal to 2 or not

SELECT name
FROM Customer
WHERE COALESCE(referee_id,0) <> 2;

586. Customer Placing the Largest Number of Orders

注意 MySQL 里限制输出结果不能写 SELECT TOP 1 customer_number ,而是用 LIMIT .

Solution

SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1;

595. Big Countries

简单不解释。

Solution

SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000;

596. Classes More Than 5 Students

简单，就是 GROUP BY + HAVING 的基础用法。

Solution

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(*) >= 5;

607. Sales Person

题目看起来很长比较唬人，实际简单，就是多表联结 + 子查询而已。

Solution

SELECT name
FROM SalesPerson
WHERE sales_id NOT IN (
    SELECT s.sales_id
    FROM Orders o, Company c, SalesPerson s
    WHERE o.com_id = c.com_id AND o.sales_id = s.sales_id AND c.name = "RED"
);

610. Triangle Judgement

本题主要是 IF 函数的用法（类似 Excel 公式）：IF(condition, value_if_true, value_if_false)

  • 注意不是 IF … THEN … ELSE !

Solution

SELECT x, y, z, IF(x + y > z AND y + z > x AND z + x > y, "Yes", "No") AS triangle
FROM Triangle;

619. Biggest Single Number

简单，套个子查询。

  • 后来想想 HAVING COUNT(*) = 1 就行，因为不可能有 <1 的。

Solution

SELECT MAX(a.num) AS num
FROM (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING count(*) <= 1
    ) a;

620. Not Boring Movies

简单，就是一个取余 MOD() 的用法。

Solution

SELECT id, movie, description, rating
FROM Cinema
WHERE MOD(id, 2) = 1 AND description <> "boring"
ORDER BY rating DESC;

627. Swap Salary

本题主要有两个知识点，一是 UPDATE 语句的用法，而是 CASE 语句的用法：

  1. UPDATE 语句: UPDATE 表名 SET col = ...
  2. CASE 语句：CASE WHEN ... THEN ... ELSE ... END
    • CASE 后面可以直接加属性名，这样的话 WHEN … THEN … 可以写的更简化
UPDATE salary SET sex =
CASE sex
    WHEN 'm' THEN 'f'
    ELSE 'm'
END;

My Solution

UPDATE Salary
SET sex = 
    CASE
        WHEN sex = 'f' THEN 'm'
        ELSE 'f'
    END;

1050. Actors and Directors Who Cooperated At Least Three Times

简单，就是 GROUP BY 后面可以跟多列。

Solution

SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(*) >= 3;

1068. Product Sales Analysis I

简单，就是左联结的基本用法，别忘了 LEFT JOIN 后面要有 ON 作为条件，不然会报 runtime error.

Solution

SELECT p.product_name, s.year, s.price
FROM Sales s LEFT JOIN Product p
ON s.product_id = p.product_id;

1075. Project Employees I

简单，主要就是 AVG() 和 ROUND() 的用法，跟 Excel 里基本是一样的。

Solution

SELECT p.project_id, ROUND(AVG(e.experience_years),2) AS average_years
FROM Project p LEFT JOIN Employee e
ON p.employee_id = e.employee_id
GROUP BY p.project_id;

Search

    Post Directory