Try   HackMD

Leetcode - SQL Easy 刷題記錄

  • Mysql 大全_junoir (看上一篇)
  • 初階 Easy，大部份照題號順序刷題
    – 【175. Combine Two Tables】組合兩個表
    – 【182. Duplicate Emails】查找重复的电子邮箱
    – 【183. Customers Who Never Order】从不订购的客户
    – 【196. Delete Duplicate Emails】刪除重复的电子邮箱
    – 【577. Employee Bonus】 员工奖金
    – 【584. Find Customer Referee】寻找用户推荐人，找到沒有被id 2 推薦的用戶
    – 【586. Customer Placing the Largest Number of Orders
    】订单最多的客户
    – 【595. Big Countries】大的國家，area > 3000000 km2 或 population > 25000000

PS 可以去刷中國版 力扣（LeetCode），申請和美國版 LeetCode 帳號紀錄同步，從中國版買Premiere比較便宜~

  • Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →
  • Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →

中國版題目也能中英隨時切換

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

初階

【175. Combine Two Tables】組合兩個表

題目


























Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| personId    | int     |
| lastName    | varchar |
| firstName   | varchar |
+-------------+---------+
personId is the primary key (column with unique values) for this table.
This table contains information about the ID of some persons and their first and last names.
 

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| addressId   | int     |
| personId    | int     |
| city        | varchar |
| state       | varchar |
+-------------+---------+
addressId is the primary key (column with unique values) for this table.
Each row of this table contains information about the city and state of one person with ID = PersonId.


























Input: 
Person table:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Wang     | Allen     |
| 2        | Alice    | Bob       |
+----------+----------+-----------+
Address table:
+-----------+----------+---------------+------------+
| addressId | personId | city          | state      |
+-----------+----------+---------------+------------+
| 1         | 2        | New York City | New York   |
| 2         | 3        | Leetcode      | California |
+-----------+----------+---------------+------------+
Output: 
+-----------+----------+---------------+----------+
| firstName | lastName | city          | state    |
+-----------+----------+---------------+----------+
| Allen     | Wang     | Null          | Null     |
| Bob       | Alice    | New York City | New York |
+-----------+----------+---------------+----------+
Explanation: 
There is no address in the address table for the personId = 1 so we return null in their city and state.
addressId = 1 contains information about the address of personId = 2.

解答:

如果要在地端練習，先照著上方創建和匯入表格


















CREATE TABLE person (
personId INT,
lastname VARCHAR(20),
firstname VARCHAR(20));

INSERT INTO person VALUE(1,'Wang','Allen'), (2, 'Alice', 'Bob')j
SELECT * FROM person;


CREATE TABLE address(
addressId INT,
personId INT,
city VARCHAR(20),
state VARCHAR(20));

INSERT INTO address VALUE(1,2,'New York City','New York'),(2,3,'Leetcode','California');
SELECT * FROM address;

我的解





SELECT p.firstname, p.lastname, a.city, a.state
FROM person AS p
LEFT JOIN address AS a
On a.personId = p.personId;

【182. Duplicate Emails】查找重复的电子邮箱

題目











Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

















Input: 
Person table:
+----+---------+
| id | email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
Output: 
+---------+
| Email   |
+---------+
| a@b.com |
+---------+
Explanation: a@b.com is repeated two times.

解答

如果要在地端練習，先照著上方創建和匯入表格








CREATE TABLE person(
id INT,
email VARCHAR(50));

INSERT INTO person VALUE (1, 'a@b.com'),(2, 'c@d.com'),(3,'a@b.com')j
DROP TABLE person;
select * from person;

我的解






SELECT email FROM(
SELECT email, COUNT(*) AS count_email
FROM person
) AS X 
WHERE count_email > 1;

網友另解





SELECT email
FROM person
GROUP BY email
HAVING COUNT (email) >1;

【183. Customers Who Never Order】从不订购的客户

題目
























Table: Customers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the ID and name of a customer.
 

Table: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+
id is the primary key (column with unique values) for this table.
customerId is a foreign key (reference columns) of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.

























Input: 
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Output: 
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

解答

如果要在地端練習，先照著上方創建和匯入表格











CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(20));
CREATE TABLE orders(
id INT PRIMARY KEY,
customerId INT,
FOREIGN KEY (customerId) REFERENCEs customers(id));

INSERT INTO customers VALUE (1,'Joe'),(2,'Henry'),(3,'Sam'), (4, 'Max');
INSERT INTO orders VALUE(1,3),(2,1);

我的解 (因為要找沒訂購過的，最後下WHERE條件)






SELECT c.name AS Customers
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customerId
WHERE o.customerId IS NULL;

網友另解





SELECT customers.name AS Customers
FROM customers
WHERE customers.id NOT IN (
SELECT customerId FROM orders);

【196. Delete Duplicate Emails】刪除重复的电子邮箱

題目











Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.


















Input: 
Person table:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Output: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.

解答

如果要在地端練習，先照著上方創建和匯入表格






CREATE TABLE person(
id INT PRIMARY KEY,
email VARCHAR(50));

INSERT INTO person VALUE (1,'john@example.com'),(2,'bob@example.com'),(3,'john@example.com');

我的解 (但Leetcode 顯示錯誤)





SELECT MIN(id) AS id, email
FROM person
GROUP BY email
HAVING COUNT(email) >= 1;

網友另解




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

DELETE FROM person WHERE id NOT IN (SELECT MIN(id) FROM person GROUP BY email)









pandas 解法
import pandas as pd

def delete_duplicate_emails(person: pd.DataFrame) -> None:
    min_id = person.groupby('email')['id'].transform('min')
    removed_person = person[person['id'] != min_id] 
    person.drop(removed_person.index, inplace=True)
    return

【577. Employee Bonus】 员工奖金 < 1000

題目


























Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| empId       | int     |
| name        | varchar |
| supervisor  | int     |
| salary      | int     |
+-------------+---------+
empId is the column with unique values for this table.
Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager.
 

Table: Bonus

+-------------+------+
| Column Name | Type |
+-------------+------+
| empId       | int  |
| bonus       | int  |
+-------------+------+
empId is the column of unique values for this table.
empId is a foreign key (reference column) to empId from the Employee table.
Each row of this table contains the id of an employee and their respective bonus.


























Input: 
Employee table:
+-------+--------+------------+--------+
| empId | name   | supervisor | salary |
+-------+--------+------------+--------+
| 3     | Brad   | null       | 4000   |
| 1     | John   | 3          | 1000   |
| 2     | Dan    | 3          | 2000   |
| 4     | Thomas | 3          | 4000   |
+-------+--------+------------+--------+
Bonus table:
+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+
Output: 
+------+-------+
| name | bonus |
+------+-------+
| Brad | null  |
| John | null  |
| Dan  | 500   |
+------+-------+

解答

如果要在地端練習，先照著上方創建和匯入表格


























CREATE TABLE Employee (
    empId INT PRIMARY KEY,
    name VARCHAR(255),
    supervisor INT,
    salary INT
);

INSERT INTO Employee (empId, name, supervisor, salary)
VALUES
    (3, 'Brad', NULL, 4000),
    (1, 'John', 3, 1000),
    (2, 'Dan', 3, 2000),
    (4, 'Thomas', 3, 4000);
    

CREATE TABLE Bonus (
    empId INT PRIMARY KEY,
    bonus INT,
    FOREIGN KEY (empId) REFERENCES Employee(empId)
);

INSERT INTO Bonus (empId, bonus)
VALUES
    (2, 500),
    (4, 2000);

我的解






SELECT e.name, b.bonus FROM employee AS e
LEFT JOIN bonus AS b
ON e.empId = b.empId
WHERE b.bonus < 1000 or b.bonus IS NULL
ORDER BY b.bonus;

【584. Find Customer Referee】寻找用户推荐人，找到沒有被id 2 推薦的用戶

題目












Table: Customer

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| referee_id  | int     |
+-------------+---------+
In SQL, id is the primary key column for this table.
Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.






















Input: 
Customer table:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1  | Will | null       |
| 2  | Jane | null       |
| 3  | Alex | 2          |
| 4  | Bill | null       |
| 5  | Zack | 1          |
| 6  | Mark | 2          |
+----+------+------------+
Output: 
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

解答

如果要在地端練習，先照著上方創建和匯入表格















CREATE TABLE Customer (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    referee_id INT
);

INSERT INTO Customer (id, name, referee_id)
VALUES
    (1, 'Will', NULL),
    (2, 'Jane', NULL),
    (3, 'Alex', 2),
    (4, 'Bill', NULL),
    (5, 'Zack', 1),
    (6, 'Mark', 2);

我的解，不能寫 IS NOT 2，要寫 <>2



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

【586. Customer Placing the Largest Number of Orders】订单最多的客户

題目











Table: Orders

+-----------------+----------+
| Column Name     | Type     |
+-----------------+----------+
| order_number    | int      |
| customer_number | int      |
+-----------------+----------+
order_number is the primary key (column with unique values) for this table.
This table contains information about the order ID and the customer ID.




















Input: 
Orders table:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1            | 1               |
| 2            | 2               |
| 3            | 3               |
| 4            | 3               |
+--------------+-----------------+
Output: 
+-----------------+
| customer_number |
+-----------------+
| 3               |
+-----------------+
Explanation: 
The customer with number 3 has two orders, which is greater than either customer 1 or 2 because each of them only has one order. 
So the result is customer_number 3.

解答

如果要在地端練習，先照著上方創建和匯入表格












CREATE TABLE Orders (
    order_number INT AUTO_INCREMENT PRIMARY KEY,
    customer_number INT
);

INSERT INTO Orders (order_number, customer_number)
VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 3);

我的解，這樣不對，LIMIT 1 還是會出現同樣id的兩個結果








SELECT customer_number
FROM Orders
WHERE customer_number = (
    SELECT MAX(customer_number)
    FROM Orders
    LIMIT 1
);

網友另解






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

【595. Big Countries】大的國家，area > 3000000 km2 或 population > 25000000

題目














Table: World

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
| area        | int     |
| population  | int     |
| gdp         | bigint  |
+-------------+---------+
name is the primary key (column with unique values) for this table.
Each row of this table gives information about the name of a country, the continent to which it belongs, its area, the population, and its GDP value.



















Input: 
World table:
+-------------+-----------+---------+------------+--------------+
| name        | continent | area    | population | gdp          |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  |
| Albania     | Europe    | 28748   | 2831741    | 12960000000  |
| Algeria     | Africa    | 2381741 | 37100000   | 188681000000 |
| Andorra     | Europe    | 468     | 78115      | 3712000000   |
| Angola      | Africa    | 1246700 | 20609294   | 100990000000 |
+-------------+-----------+---------+------------+--------------+
Output: 
+-------------+------------+---------+
| name        | population | area    |
+-------------+------------+---------+
| Afghanistan | 25500100   | 652230  |
| Algeria     | 37100000   | 2381741 |
+-------------+------------+---------+

解答

如果要在地端練習，先照著上方創建和匯入表格
















CREATE TABLE World (
    name VARCHAR(255) PRIMARY KEY,
    continent VARCHAR(255),
    area INT,
    population INT,
    gdp BIGINT
);

INSERT INTO World (name, continent, area, population, gdp)
VALUES
    ('Afghanistan', 'Asia', 652230, 25500100, 20343000000),
    ('Albania', 'Europe', 28748, 2831741, 12960000000),
    ('Algeria', 'Africa', 2381741, 37100000, 188681000000),
    ('Andorra', 'Europe', 468, 78115, 3712000000),
    ('Angola', 'Africa', 1246700, 20609294, 100990000000);

我的解

SELECT name, population, area FROM world WHERE area > 3000000 OR population > 25000000;
Last changed by 
 
Work Cata
Hi, this is Catalina!原本的西語開發業務，2023轉職資料領域，正在補齊計算機組織, 微積分, 線代, 機率論，忙碌中做點筆記提醒自己 🤲🤝https://github.com/06Cata
0
1848

Read more

【Part5 - Mysql JOIN 連接的使用】

CROSS JOIN 笛卡爾積連接、交叉連接除非兩者列數順序、數量相等，不然不能用~ 會變成兩者相乘的大數據及

Jun 23, 2025
【Part6 電商GA4分析 - Mysql 是什麼？Mysql 優勢、指令】

Part1

Jun 22, 2025
【Part3 - Mysql 是什麼？Mysql 優勢、指令】

Part1

Jun 22, 2025
【Part4 - Mysql 是什麼？Mysql 優勢、指令】

Part1

Jun 22, 2025
Read more from Work Cata
Published on HackMD