高频 SQL 50 题（基础版）【一】

1757. 可回收且低脂的产品

编写解决方案找出既是低脂又是可回收的产品编号。返回结果 无顺序要求 。

我们用WHERE语句筛选即可。

SELECT product_id FROM Products WHERE(
    low_fats = 'Y' AND 
    recyclable = 'Y'
);

584. 寻找用户推荐人

MySQL 对于比较有三种结果TRUE, FLASE, UNKNOWN，其中NULL和任何值比较（包含NULL）结果均是UNKNOWN。在MySQL中判断一个值是不是NULL应该使用IS NULL, IS NOT NULL两种

SELECT 
    name
FROM
    Customer
WHERE
    referee_id IS NULL or referee_id != 2

595. 大的国家

这题的难度不高，一个简单的与条件就好

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

1148. 文章浏览 I

首先最基础的筛选还是很简单

SELECT 
    author_id
FROM
    Views
WHERE
    author_id = viewer_id

如果要排序只需要使用ORDER BY 关键字

SELECT 
    author_id
FROM
    Views
WHERE
    author_id = viewer_id
ORDER BY
    author_id

题目说了可能会重复，因此要去重

SELECT 
    DISTINCT author_id
FROM
    Views
WHERE
    author_id = viewer_id
ORDER BY
    author_id

最后在注意表头是id，只需要给author_id取一个别名

SELECT 
    DISTINCT author_id AS id
FROM
    Views
WHERE
    author_id = viewer_id
ORDER BY
    author_id

1683. 无效的推文

获取一个字符串的长度可以用LENGTH函数。

SELECT
    tweet_id
FROM
    Tweets
WHERE
    LENGTH(content) > 15

但是要注意，这里的LENGTH是返回的是字节的长度，但是会有一些字符长度不是一个字节的，比如￥。如果遇到了这种情况可以使用CHAR_LENGTH，这个函数是返回的字符的个数。

SELECT
    tweet_id
FROM
    Tweets
WHERE
    CHAR_LENGTH(content) > 15

链接表 JOIN 及其使用方法

有一些查询，需要把两张或更多的表进行结合，共同得到结果，这是就要用到链接表

INNER JOIN，内链接。仅返回两张表中满足条件的匹配行

SELECT A.column1, B.column2 
FROM table1 A 
INNER JOIN table2 B ON A.key = B.key;

LEFT JOIN，左连接。返回左表的全部数据，右表无匹配时填充NULL

SELECT A.*, B.class_name 
FROM students A 
LEFT JOIN class B ON A.class_id = B.class_id;

RIGHT JOIN，右链接。与左连接相反，返回右表的全部数据，左表无匹配时填充NULL
```
(SELECT * FROM A LEFT JOIN B ON A.id = B.id)
UNION
(SELECT * FROM A RIGHT JOIN B ON A.id = B.id);
```
全链接，返回两表所有记录，无匹配时填充 NULL。

MySQL原生不支持，但可通过 LEFT JOIN + RIGHT JOIN + UNION 模拟。
```
(SELECT * FROM A LEFT JOIN B ON A.id = B.id)
UNION
(SELECT * FROM A RIGHT JOIN B ON A.id = B.id);
```

左表（Left Table）：JOIN 关键字左侧的表（即 FROM 后的第一个表）。

右表（Right Table）：JOIN 关键字右侧的表（即 JOIN 后的表）。

所有 LEFT JOIN 查询均可改写为 RIGHT JOIN（反之亦然），只需调换左右表顺序。

1378. 使用唯一标识码替换员工ID

题目要求了输出所有的名字，没有唯一标识的用NULL填充，因此用Employees作为左表，采用左连接。

SELECT 
    EmployeeUNI.unique_id,
    Employees.name
FROM
    Employees
LEFT JOIN
    EmployeeUNI ON Employees.id = EmployeeUNI.id;

当然也可以写成右链接的形式

SELECT 
    EmployeeUNI.unique_id,
    Employees.name
FROM
    EmployeeUNI
RIGHT JOIN
    Employees ON Employees.id = EmployeeUNI.id;

1068. 产品销售分析 I

题目给了一个外键，其实就是保证了Sales.product_id一定可以在Product.product_id中找到。所有这里左链接、右链接和内链接都是一样的。

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

1581. 进店却未进行过交易的顾客

我们观察到，每一次就进店都要被统计，因此我们选择Visits作为左表，采用左链接

SELECT
    Visits.*,
    Transactions.*
FROM 
    Visits
LEFT JOIN
    Transactions ON Transactions.visit_id = Visits.visit_id

可以得到如下表格

| visit_id | customer_id | transaction_id | visit_id | amount |
| -------- | ----------- | -------------- | -------- | ------ |
| 1        | 23          | 12             | 1        | 910    |
| 2        | 9           | 13             | 2        | 970    |
| 4        | 30          | null           | null     | null   |
| 5        | 54          | 9              | 5        | 200    |
| 5        | 54          | 3              | 5        | 300    |
| 5        | 54          | 2              | 5        | 310    |
| 6        | 96          | null           | null     | null   |
| 7        | 54          | null           | null     | null   |
| 8        | 54          | null           | null     | null   |

我们观察到transaction_id 为空的就是没有购买的的进店。所以加一个WHERE条件

SELECT
    Visits.*,
    Transactions.*
FROM 
    Visits
LEFT JOIN
    Transactions ON Transactions.visit_id = Visits.visit_id
WHERE
    Transactions.transaction_id is NULL

结果如下

| visit_id | customer_id | transaction_id | visit_id | amount |
| -------- | ----------- | -------------- | -------- | ------ |
| 4        | 30          | null           | null     | null   |
| 6        | 96          | null           | null     | null   |
| 7        | 54          | null           | null     | null   |
| 8        | 54          | null           | null     | null   |

此时我们只要在根据customer_id分组，并统计每组中visit_id的个数就可以得到答案了。

SELECT
    Visits.customer_id,
    COUNT(Visits.visit_id) AS count_no_trans
FROM 
    Visits
LEFT JOIN
    Transactions ON Transactions.visit_id = Visits.visit_id
WHERE
    Transactions.transaction_id is NULL
GROUP BY
    Visits.customer_id;

197. 上升的温度

这个题有一个特点就是表要和自己链接，其实我们可以当作是两个表，一个是今天w1，一个是昨天w2。我们可以写出如下代码

SELECT
    w1.*,
    w2.*
FROM
    Weather AS w1
LEFT JOIN
    Weather AS w2 ON w2.recordDate + 1 = w1.recordDate

结果如下

| id | recordDate | temperature | id   | recordDate | temperature |
| -- | ---------- | ----------- | ---- | ---------- | ----------- |
| 1  | 2015-01-01 | 10          | null | null       | null        |
| 2  | 2015-01-02 | 25          | 1    | 2015-01-01 | 10          |
| 3  | 2015-01-03 | 20          | 2    | 2015-01-02 | 25          |
| 4  | 2015-01-04 | 30          | 3    | 2015-01-03 | 20          |

可以看到已经能链接到一起，我们下一步要做的就是删掉没有昨天的，以及今天气温比昨天低的。

SELECT
    w1.id
FROM
    Weather AS w1
LEFT JOIN
    Weather AS w2 ON w2.recordDate + 1 = w1.recordDate
WHERE
    w2.id IS NOT NULL
    AND w1.temperature > w2.temperature

这个代码会运行错误，错误样例如下

| id | recordDate | temperature |
| -- | ---------- | ----------- |
| 1  | 2015-01-31 | 10          |
| 2  | 2015-02-01 | 25          |
| 3  | 2015-02-03 | 20          |
| 4  | 2015-02-04 | 30          |

原因是我们对日期加1是右问题的。也就说日期不能直接做加法，我们可以用ADDDATE函数实现

ADDDATE(date, INTERVAL expr unit)

参数	说明
`date`	原始日期（DATE/DATETIME/TIMESTAMP）
`expr`	需增加的时间数值（正负整数）
`unit`	时间单位（DAY, MONTH, YEAR等）

所以可以写出

SELECT
    w1.id
FROM
    Weather AS w1
LEFT JOIN
    Weather AS w2 ON ADDDATE(w2.recordDate, INTERVAL 1 DAY) = w1.recordDate
WHERE
    w2.id IS NOT NULL
    AND w1.temperature > w2.temperature

这个代码是可以通过的，除此之外DATE_ADD是ADDDATE的别名就不在赘述了。还有另一个函数是SUBDATE是对日期做减法，使用方法和加法相同。

SELECT
    w1.id
FROM
    Weather AS w1
LEFT JOIN
    Weather AS w2 ON w2.recordDate = SUBDATE(w1.recordDate, INTERVAL 1 DAY)
    -- Weather AS w2 ON ADDDATE(w2.recordDate, INTERVAL 1 DAY) = w1.recordDate
WHERE
    w2.id IS NOT NULL
    AND w1.temperature > w2.temperature

这个代码也可以通过。上面我计算了昨天的后一天，也可以计算两天的差值，我们用DATEDIFF函数。用法就是DATEDIFF(date1, date2)这样就能date1 - date2差值。

SELECT
    w1.id
FROM
    Weather AS w1
LEFT JOIN
    Weather AS w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE
    w2.id IS NOT NULL
    AND w1.temperature > w2.temperature

本题是计算差几天，当然也可以计算其他时间差，可以用TIMESTAMPDIFF，用法就是TIMESTAMPDIFF(unit, date2, date1)，就可以计算出date1 - date2的时间差单位是unit。代码如下

SELECT
    w1.id
FROM
    Weather AS w1
LEFT JOIN
    -- Weather AS w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
    Weather AS w2 ON TIMESTAMPDIFF(DAY, w2.recordDate, w1.recordDate) = 1
WHERE
    w2.id IS NOT NULL
    AND w1.temperature > w2.temperature

要注意，DATEDIFF和TIMESTAMPDIFF的返回结果都不是绝对值。

1661. 每台机器的进程平均运行时间

我们先把表链接起来，然后按照machine_id分组，分组后对时间差求和，再除以线程数就就能得到平均值。

SELECT
    a1.machine_id,
    ROUND(SUM(a2.timestamp - a1.timestamp) / COUNT(a1.process_id), 3) AS processing_time
FROM 
    Activity AS a1
INNER JOIN Activity AS a2 
    ON a1.machine_id = a2.machine_id
    AND a1.process_id = a2.process_id
    AND a1.activity_type = 'start'
    AND a2.activity_type = 'end'
GROUP BY
    a1.machine_id

当然了，求平均数这一个步也可以直接用AVG函数实现。

SELECT
    a1.machine_id,
    ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time
FROM 
    Activity AS a1
INNER JOIN Activity AS a2 
    ON a1.machine_id = a2.machine_id
    AND a1.process_id = a2.process_id
    AND a1.activity_type = 'start'
    AND a2.activity_type = 'end'
GROUP BY
    a1.machine_id

577. 员工奖金

这个题目比前面的题目要简单不少

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

交叉链接 CROSS JOIN

MySQL 中的 CROSS JOIN 是一种特殊的表连接操作，用于生成两个或多个表的笛卡尔积（即所有可能的行组合）。以下是其核心概念、用法及注意事项的综合解析：

1. 定义与作用

• 笛卡尔积：若表 A 有 n 行，表 B 有 m 行，CROSS JOIN 会生成 n*m 行结果集，每一行是两表的任意组合。
• 无需连接条件：与 INNER JOIN 或 LEFT JOIN 不同，CROSS JOIN 不依赖 ON 或 USING 条件，直接组合所有行。

2. 语法形式

• 显式语法：使用 CROSS JOIN 关键字：

SELECT * FROM table1 CROSS JOIN table2;

• 隐式语法：通过逗号分隔表名（效果相同）：

SELECT * FROM table1, table2;

两者的执行结果完全一致。

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

首先我们先用一个查询，统计一下学生参加某门课考试的次数。

SELECT 
    student_id,
    subject_name,
    COUNT(*) AS attended_exams
FROM
    Examinations
GROUP BY
    student_id,
    subject_name;

结果如下

| student_id | subject_name | attended_exams |
| ---------- | ------------ | -------------- |
| 1          | Math         | 3              |
| 1          | Physics      | 2              |
| 1          | Programming  | 1              |
| 2          | Programming  | 1              |
| 13         | Math         | 1              |
| 13         | Programming  | 1              |
| 13         | Physics      | 1              |
| 2          | Math         | 1              |

然后要得到所有的学生和课程组合的所有结果，这里用交叉链接实现

SELECT
    *
FROM
    Students
CROSS JOIN
    Subjects;

结果如下

| student_id | student_name | subject_name |
| ---------- | ------------ | ------------ |
| 1          | Alice        | Programming  |
| 1          | Alice        | Physics      |
| 1          | Alice        | Math         |
| 2          | Bob          | Programming  |
| 2          | Bob          | Physics      |
| 2          | Bob          | Math         |
| 13         | John         | Programming  |
| 13         | John         | Physics      |
| 13         | John         | Math         |
| 6          | Alex         | Programming  |
| 6          | Alex         | Physics      |
| 6          | Alex         | Math         |

现在的思路就是把学生和课程所有组合的表和学生参加考试次数的表组合起来。

SELECT
    Students.student_id,
    Students.student_name,
    Subjects.subject_name,
    Counted.attended_exams
FROM
    Students
CROSS JOIN
    Subjects
LEFT JOIN(
    SELECT 
        student_id,
        subject_name,
        COUNT(*) AS attended_exams
    FROM
        Examinations
    GROUP BY
        student_id,
        subject_name
) AS Counted
ON 
    Students.student_id = Counted.student_id
    AND Subjects.subject_name = Counted.subject_name
ORDER BY
    Students.student_id,
    Subjects.subject_name

这样得到一个结果就是如果遇到了NULL的情况，没有输出0。对于这种情况我门可以用IFNULL函数解决。

IFNULL 接受两个参数，若第一个参数为 NULL，则返回第二个参数的值；否则直接返回第一个参数的值。

所以代码如下

SELECT
    Students.student_id,
    Students.student_name,
    Subjects.subject_name,
    IFNULL(Counted.attended_exams, 0) AS attended_exams
FROM
    Students
CROSS JOIN
    Subjects
LEFT JOIN(
    SELECT 
        student_id,
        subject_name,
        COUNT(*) AS attended_exams
    FROM
        Examinations
    GROUP BY
        student_id,
        subject_name
) AS Counted
ON 
    Students.student_id = Counted.student_id
    AND Subjects.subject_name = Counted.subject_name
ORDER BY
    Students.student_id,
    Subjects.subject_name

当然了，也可以使用隐式的交叉链接，代码如下。

SELECT
    Students.student_id,
    Students.student_name,
    Subjects.subject_name,
    IFNULL(Counted.attended_exams, 0) AS attended_exams
FROM
    (Students, Subjects)
LEFT JOIN(
    SELECT 
        student_id,
        subject_name,
        COUNT(*) AS attended_exams
    FROM
        Examinations
    GROUP BY
        student_id,
        subject_name
) AS Counted
ON 
    Students.student_id = Counted.student_id
    AND Subjects.subject_name = Counted.subject_name
ORDER BY
    Students.student_id,
    Subjects.subject_name

为什么这里隐式的情况需要加一个()？因为这里还有一个左链接，左链接中左表是FROM后第一个表，如果不加就是Students。

570. 至少有5名直接下属的经理

先和自己进行链接，然后再分组，统计出每个经理有多少个下属。

SELECT
    e1.name,
    COUNT(*) AS underling
FROM
    Employee AS e1
INNER JOIN
    Employee AS e2 ON e2.managerId = e1.id
GROUP BY
    e2.managerId

这里使用内链接是为了去掉NULL的情况。

然后我们再根据这个筛选出下属大于等于5的经理。

SELECT
    name
FROM (
    SELECT
        e1.name,
        COUNT(*) AS underling
    FROM
        Employee AS e1
    INNER JOIN
        Employee AS e2 ON e2.managerId = e1.id
    GROUP BY
        e2.managerId
) AS Counted
WHERE
    underling >= 5

还有一种做法是，当我们统计出来后可以直接用HAVING 关键字对GROUP BY后的行进行筛选。

SELECT
    e1.name
FROM
    Employee AS e1
INNER JOIN
    Employee AS e2 ON e2.managerId = e1.id
GROUP BY
    e2.managerId
HAVING COUNT(*) >= 5

`WHERE` 和 `HAVING` 的作用与对比分析

一、核心作用

WHERE
• 数据过滤：作用于原始数据行，在查询开始时直接筛选符合条件的记录。
• 执行阶段：在 GROUP BY 分组和聚合计算之前执行，减少后续处理的数据量。
HAVING
• 分组后过滤：对 GROUP BY 分组后的结果进行筛选，通常基于聚合函数（如 SUM、AVG 等）。
• 执行阶段：在分组和聚合操作完成后执行，过滤不符合条件的分组。

二、相同点与不同点总结

特性	WHERE	HAVING
作用范围	行级数据（原始数据）	分组后的聚合数据
执行顺序	在 `GROUP BY` 和聚合操作之前执行	在 `GROUP BY` 和聚合操作之后执行
聚合函数支持	不可直接使用聚合函数（如 `WHERE SUM(price) > 100` 会报错）	必须与聚合函数配合使用（如 `HAVING SUM(price) > 100`）
使用场景	筛选单行数据（如 `WHERE score > 60`）	筛选分组后的结果（如 `HAVING AVG(score) > 60`）
字段别名支持	不支持字段别名（如 `SELECT id AS a WHERE a > 10` 报错）	支持字段别名（如 `SELECT id AS a GROUP BY id HAVING a > 10`）
多表查询中的应用	可在外连接前过滤数据（如 `LEFT JOIN ... ON ... WHERE`）	仅用于分组后过滤，与多表连接无关
性能影响	优先使用，减少后续处理的数据量，效率更高	需先完成分组和聚合计算，效率相对较低

1934. 确认率

首先我们可以用左连接把两张表链接，并按照user_id进行分组。分组后我们对每一组可以用COUNT统计出请求的次数。我们如何统计确认从次数呢？我们可以判断action = 'confirmed'的个数，求个数可用SUM函数实现。但是要注意在MySQL中布尔的值有三种，还有一种NULL这样直接转换成整型会出问题。因此我们可以用IF函数，IF(Confirmations.action = 'confirmed', 1, 0)这样就能准确的进行转换。最后做除法、四舍五入就比较简单了。

SELECT
    Signups.user_id,
    ROUND(SUM(IF(Confirmations.action = 'confirmed', 1, 0)) / COUNT(*), 2) AS confirmation_rate    
FROM
    Signups
LEFT JOIN
    Confirmations ON Confirmations.user_id = Signups.user_id
GROUP BY
    Signups.user_id

620. 有趣的电影

如果不使用聚合函数，就是简单的筛选，排序。

SELECT 
    *
FROM
    cinema
WHERE
    id % 2 = 1
    AND description != 'boring'
ORDER BY
    rating DESC

这里的取模也可以用聚合函数实现。

SELECT 
    *
FROM
    cinema
WHERE
    MOD(id, 2) = 1
    AND description != 'boring'
ORDER BY
    rating DESC;

1251. 平均售价

考虑到即使没有卖出去，我们也要输出平均值为0，因此这里让Prices作为左表，进行左连接。这里可能会有NULL的情况，所以我们要用IFNULL来对NULL进行替换。

SELECT
    Prices.product_id,
    ROUND(IFNULL(SUM(Prices.price * UnitsSold.units) / SUM(UnitsSold.units), 0), 2) AS average_price
FROM    
    Prices
LEFT JOIN
    UnitsSold ON
    UnitsSold.product_id = Prices.product_id
    AND Prices.start_date <= UnitsSold.purchase_date
    AND UnitsSold.purchase_date <= Prices.end_date
GROUP BY
    Prices.product_id

上面的写法，对于日期是否在区间内用到了两个比较，这里也可以用BETWEEN来实现。

SELECT
    Prices.product_id,
    ROUND(IFNULL(SUM(Prices.price * UnitsSold.units) / SUM(UnitsSold.units), 0), 2) AS average_price
FROM    
    Prices
LEFT JOIN
    UnitsSold ON
    UnitsSold.product_id = Prices.product_id
    AND (UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date)
GROUP BY
    Prices.product_id

posted @ 2025-03-30 17:45 PHarr 阅读(29) 评论(0) 收藏举报

刷新页面返回顶部

PHarr

生活不止眼前的苟且，还有诗和远方的田野

高频 SQL 50 题（基础版）【一】

1757. 可回收且低脂的产品

584. 寻找用户推荐人

595. 大的国家

1148. 文章浏览 I

1683. 无效的推文

链接表 JOIN 及其使用方法

1378. 使用唯一标识码替换员工ID

1068. 产品销售分析 I

1581. 进店却未进行过交易的顾客

197. 上升的温度

1661. 每台机器的进程平均运行时间

577. 员工奖金

交叉链接 CROSS JOIN

1. 定义与作用

2. 语法形式

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

570. 至少有5名直接下属的经理

`WHERE` 和 `HAVING` 的作用与对比分析

一、核心作用

二、相同点与不同点总结

1934. 确认率

620. 有趣的电影

1251. 平均售价

公告

PHarr

生活不止眼前的苟且，还有诗和远方的田野

高频 SQL 50 题（基础版）【一】

1757. 可回收且低脂的产品

584. 寻找用户推荐人

595. 大的国家

1148. 文章浏览 I

1683. 无效的推文

链接表 JOIN 及其使用方法

1378. 使用唯一标识码替换员工ID

1068. 产品销售分析 I

1581. 进店却未进行过交易的顾客

197. 上升的温度

1661. 每台机器的进程平均运行时间

577. 员工奖金

交叉链接 CROSS JOIN

1. 定义与作用

2. 语法形式

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

570. 至少有5名直接下属的经理

WHERE 和 HAVING 的作用与对比分析

一、核心作用

二、相同点与不同点总结

1934. 确认率

620. 有趣的电影

1251. 平均售价

公告

`WHERE` 和 `HAVING` 的作用与对比分析