SQL高频50题（基础版）

wayzinx

已于 2024-03-01 21:32:26 修改

阅读量111

点赞数

分类专栏：刷题文章标签： sql 数据库

于 2024-02-25 13:51:14 首次发布

原文链接：https://leetcode.cn/studyplan/sql-free-50/

版权

刷题专栏收录该内容

4 篇文章 0 订阅

订阅专栏

（1）1757. 可回收且低脂的产品

关键字 SELECT 用于指定我们想要从表 Products 中检索的列。在这种情况下，我们想要检索 product_id 列。

关键字 WHERE 用于根据特定条件过滤表 Products 中的行，条件是 low_fats 列的值为"Y"且 recyclable 列的值为"Y"。我们使用逻辑运算符 AND 将两个条件组合起来，确保最终结果只包含既是 low_fats又是recyclable 的产品ID。

SELECT
    product_id
FROM
    Products
WHERE
    low_fats = 'Y' AND recyclable = 'Y'

(2)584. 寻找用户推荐人

方法：使用 <> (!=) 和 IS NULL

有的人也许会非常直观地想到如下解法。

SELECT name FROM customer WHERE referee_Id <> 2;

然而，这个查询只会返回一个结果：Zach，尽管事实上有 4 个顾客都不是 Jane 推荐的（包括 Jane 她自己）。所有没有推荐人（referee_id 字段值为 NULL) 的全部都消失了。为什么?

MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。

因此，在 WHERE 语句中我们需要做一个额外的条件判断 `referee_id IS NULL'。

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

或者

SELECT name FROM customer WHERE referee_id != 2 OR referee_id IS NULL;

下面的解法同样是错误的，错误原因同上。避免错误的秘诀在于使用 IS NULL 或者 IS NOT NULL 两种操作来对 NULL 值做特殊判断。

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

(3)595. 大的国家

要确定一个国家是否被认为是“大国”，有两个条件需要验证，如描述中所述：
该国家的面积至少为 300 万平方千米，表示为 area >= 3,000,000。

该国家的人口至少为 2500 万，表示为 population >= 25,000,000。

首先，我们应用行过滤来识别满足条件的国家。

SELECT 
    * 
FROM 
    world 
WHERE 
    area >= 3000000 OR population >= 25000000

此步骤会过滤掉不满足条件的国家行，我们需要根据问题的要求返回三列，相对顺序为：name、population 和 area。

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

(4)1148. 文章浏览 I

在 SQL 中，我们可以在 SELECT 语句中使用 DISTINCT 关键字来从表 Views 中检索唯一元素。我们还使用 WHERE 子句应用条件。此条件过滤掉仅包含 author_id 等于 viewer_id 的行。

SELECT 
    DISTINCT author_id 
FROM 
    Views 
WHERE 
    author_id = viewer_id

我们将通过AS给 author_id 列取别名 id 来重命名该列。

SELECT 
    DISTINCT author_id AS id 
FROM 
    Views 
WHERE 
    author_id = viewer_id

注意，我们还应该根据 id 列按升序对结果表进行排序，这可以通过使用 ORDER BY 关键字实现。完整的代码如下所示：

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

(5)1683. 无效的推文

对于SQL表，用于计算字符串中字符数的最佳函数是 CHAR_LENGTH(str)，它返回字符串 str 的长度。

另一个常用的函数 LENGTH(str) 在这个问题中也适用，因为列 content 只包含英文字符，没有特殊字符。否则，LENGTH() 可能会返回不同的结果，因为该函数返回字符串 str 的字节数，某些字符包含多于 1 个字节。

以字符 '¥' 为例：CHAR_LENGTH() 返回结果为 1，而 LENGTH() 返回结果为 2，因为该字符串包含 2 个字节。

SELECT 
    tweet_id
FROM 
    tweets
WHERE 
    CHAR_LENGTH(content) > 15

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

我们首先执行LEFT JOIN操作，将两个表的数据基于 id 列进行组合。同样，我们使用 LEFT JOIN 来确保将所有 Employees 表中的行都包含在结果中，即使在 EmployeeUNI 表中没有匹配的行。

SELECT 
    * 
FROM
    Employees 
LEFT JOIN 
    EmployeeUNI 
ON 
    Employees.id = EmployeeUNI.id;

由于我们想要从组合表中检索列 unique_id 和 name ，所以我们将从 EmployeeUNI 表选择 unique_id 列，从 Employees 表选择 name 列。完整代码如下：

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

(7)1068. 产品销售分析 I

理论和第7题差不多，但是需要注意，先查Sales和后查Sales的区别：

返回所有产品，无论是否有销售记录：这个查询将返回Product表中所有产品的名称，即使某些产品在Sales表中没有任何销售记录。因此，如果你的目标是仅获取有销售记录的产品，这个查询可能会包含一些你不想要的结果（即，那些没有销售的产品）。
可能包含空的销售信息：对于那些在Sales表中没有匹配product_id的产品，year和price字段将会是NULL。这可能会使得结果集包含一些不完整的行，其中只有product_name而没有相应的销售年份和价格。
结果集的解读：这种查询方式更适合于场景，其中你需要列出所有产品，并查看它们的销售情况（如果有的话）。这对于生成产品目录以及可能的销售记录来说是有意义的，但不适合于仅分析销售数据的场景。

总的来说，如果你的目标是分析销售数据，特别是想要查看每个销售ID对应的产品名称以及该产品的销售年份和价格，从Sales表开始的查询更为合适。这样做确保了你只获取那些实际上已经发生销售的产品记录。而从Product表开始的查询则适用于需要获取产品全列表及其可能的销售记录的情况。

SELECT

    Product.product_name, Sales.year, Sales.price

FROM

    Sales

LEFT JOIN

    Product

ON

    Product.product_id = Sales.product_id

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

第一步：找到没有进行消费的访问记录

首先，我们需要找出那些有访问记录但没有对应的交易记录的情况。这可以通过将Visits表和Transactions表进行LEFT JOIN操作来实现，然后查找那些在Transactions表中没有对应visit_id的记录（即交易ID为NULL的情况）。

SELECT 
    v.visit_id, v.customer_id 
FROM 
    Visits v 
LEFT JOIN 
    Transactions t 
ON 
    v.visit_id = t.visit_id 
WHERE 
    t.transaction_id IS NULL;

这个查询会返回所有访问过但没有进行任何交易的访问记录。这里，LEFT JOIN确保了所有的访问记录都会被包括进来，而WHERE t.transaction_id IS NULL条件则过滤掉了那些实际进行了交易的访问记录。

第二步：统计每个顾客未进行交易的访问次数

一旦我们找到了所有未进行交易的访问记录，下一步就是对这些记录按照customer_id进行分组，并计算每组的记录数。这个记录数代表了每个顾客未进行交易的访问次数。

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

在这个查询中，我们对第一步的结果进行了分组和计数操作：

GROUP BY v.customer_id确保了我们按顾客ID对未进行交易的访问记录进行了分组。
COUNT(v.visit_id) AS count_no_trans计算了每个分组（即每个顾客）的记录数，这个数值即为每个顾客未进行交易的访问次数。

(9)197. 上升的温度

为了解决这个问题，你可以使用SQL的窗口函数或自连接特性来比较连续日期的温度。这里，我们将使用自连接的方法来找出温度上升的日期。基本思路是将Weather表与其自身连接，条件是第二个实例的recordDate是第一个实例的recordDate的下一天。然后，我们比较这两个日期的温度，以找出温度上升的情况。

下面是一个SQL查询的示例：

SELECT 
    a.id 
FROM 
    Weather a 
JOIN 
    Weather b 
ON 
    a.recordDate = DATE_ADD(b.recordDate, INTERVAL 1 DAY) 
WHERE 
    a.temperature > b.temperature;

这个查询中，我们假设DATE_ADD(b.recordDate, INTERVAL 1 DAY)这个函数能够计算出b.recordDate的下一天，这样a.recordDate就是b.recordDate的后一天。请注意，不同的数据库系统可能有不同的日期增加函数，例如，MySQL使用DATE_ADD()，而SQL Server使用DATEADD()。确保根据你所使用的数据库系统调整查询。

这个查询的关键点是：

使用JOIN子句将Weather表自连接。
在连接条件中，确保a表的记录日期是b表记录日期的下一天。
在WHERE子句中，选择温度在a表中比在b表中高的记录。
最后选择a.id，因为题目要求找出温度比前一天高的日期的id。

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

要解决这个问题，你可以使用SQL来先计算每个进程在每台机器上的耗时，然后计算每台机器上所有进程的平均耗时。这个问题可以通过以下步骤解决：

计算每个进程的耗时：首先，你需要为每台机器上的每个进程计算耗时。这可以通过连接Activity表的两个副本来实现，一个副本用于start活动，另一个用于end活动，然后计算两者的时间戳差。
计算平均耗时：得到每个进程的耗时后，接下来对每台机器计算所有进程耗时的平均值。

以下是一个可能的SQL查询实现：

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

这个查询的解释如下：

JOIN操作：通过连接Activity表的两个副本，一个用于start记录，一个用于end记录，来计算每个进程的运行时间。这里通过machine_id和process_id来确保是同一进程的开始和结束记录，同时通过activity_type区分开始和结束。
计算耗时：使用end.timestamp - start.timestamp来计算每个进程的耗时。
计算平均值：使用AVG()聚合函数和ROUND()函数来计算每台机器上所有进程耗时的平均值，并保留三位小数。
分组：使用GROUP BY start.machine_id来确保结果按照机器ID分组，为每台机器计算平均耗时。

这个查询假设每个process_id在每台machine_id上都有唯一的一对start和end记录，且start总在end之前。

(11)577. 员工奖金

首先需要知道每个员工的奖金数量，因此需要首先将 Employee 表与 Bonus 表连接。注意需要使用外连接，以处理员工没有出现在 Bonus 表上的情况。这里因为不存在员工只出现在 Bonus 表中的情况，所以只需要使用左外连接（left join 或 left outer join）。

select name, bonus
from Employee left join Bonus
on Employee.EmpId = Bonus.EmpId

对于题目中的样例，上面的代码运行可以得到如下输出：

| name | bonus |
|--------|-------|
| Dan | 500 |
| Thomas | 2000 |
| Brad | null |
| John | null |
其中 Brad 和 John 的 bonus 值为空，空值在数据库中的表示为 null。我们使用 bonus is null（而不是 bonus = null）判断奖金是否为 null。随后即可用 where 子句筛选奖金小于 1000 或者为空的员工。

select name, bonus
from Employee left join Bonus
on Employee.EmpId = Bonus.EmpId
where bonus is null or bonus < 1000

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

我们通过一个子查询创建了表 grouped，它统计每个学生参加每个科目的考试次数。

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

为了获得 (student_id，subject_name) 的所有组合，我们使用交叉联接将表 Student 中的每一行与表 Subject 中的每一行组合在一起，从而得到两个表中的 student_id 和 subject_name 的所有可能组合。

SELECT 
    *
FROM
    Students s
CROSS JOIN
    Subjects sub

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

接下来，我们对上面的表与表 grouped 执行左连接，使用 (student_id，subject_name) 对作为标识符来保留所有组合，同时合并两个表。类似地，在左连接之后， grouped.attended_exams 列可能有 null 值，我们使用 IFNULL() 函数将其替换为0。

下面是完整代码：

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

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

算法一
首先查询每个人的下属员工数。将两份 Employee 表用 join 操作连接。Manager 表代表经理，Report 表代表下属，每对 Manager.Id=Report.ManagerId 的情况代表此经理的一名下属。再根据 Manager.Id 分组，对 Report.Id 求和得到每个经理对应的下属数量。

select Manager.Name as Name, count(Report.Id) as cnt
from
Employee as Manager join Employee as Report
on Manager.Id = Report.ManagerId
group by Manager.Id

将此表命名为 ReportCount，再在其中筛选 cnt>=5 的数据项即可。最终查询为

select Name
from (
  select Manager.Name as Name, count(Report.Id) as cnt
  from
  Employee as Manager join Employee as Report
  on Manager.Id = Report.ManagerId
  group by Manager.Id
) as ReportCount
where cnt >= 5

算法二
上述查询在分组后，可以直接使用 having 字句筛选下属大于 5 的经理，代码如下

select Manager.Name as Name
from
Employee as Manager join Employee as Report
on Manager.Id = Report.ManagerId
group by Manager.Id
having count(Report.Id) >= 5

算法三
上面的查询为了得到经理的名字，首先对两份 Employee 表进行了连接，但是我们其实可以先对经理进行筛选，再通过连接操作得到经理的名字。
要筛选员工数大于等于 5 的经理，直接将 Employee 表根据 ManagerId 进行分组，每组中的 Id 即为每个经理对应的每个下属，取下属数量大于等于 5 的条目。

select ManagerId as Id
from Employee
group by ManagerId
having count(Id) >= 5

将此表命名为 Manager，随后与 Employee 表做连接操作，得到每个经理的名字。

select Employee.Name as Name
from (
  select ManagerId as Id
  from Employee
  group by ManagerId
  having count(Id) >= 5
) as Manager join Employee
on Manager.Id = Employee.Id

(14)1934. 确认率

需求分析：
        确认率是confirmed消息的数量除以请求的确认消息的总数。
        没有请求任何确认消息的用户的确认率为0。
        确认率四舍五入到小数点后两位
        难点解析：
这道题其实我觉得是考察AVG函数的使用。
        根据需求可以看出，答案也就是一个公式：confirmed消息的数量 / 总数。
        可以考虑使用AVG函数，需要注意的是AVG函数是可以写条件判断的。

        使用AVG函数计算confirmed的平均值，如果不存在则为NULL
        使用IFNULL把NULL值转换为0
        最后使用ROUND精确到小数点后两位
SQL语句：

SELECT
    s.user_id,
    ROUND(IFNULL(AVG(c.action='confirmed'), 0), 2) AS confirmation_rate
FROM
    Signups AS s
LEFT JOIN
    Confirmations AS c
ON
    s.user_id = c.user_id
GROUP BY
    s.user_id

(15)620. 有趣的电影

方法：使用 MOD() 函数

我们可以使用 mod(id,2)=1 来确定奇数 id，然后添加 description != 'boring' 来解决问题。

select *
from cinema
where mod(id, 2) = 1 and description != 'boring'
order by rating DESC
;

(16)1251. 平均售价

思路

本题需要计算每个产品的平均售价，平均售价 = 销售总额 / 总数量，因此我们只需要计算除每个产

品的销售总额和总数量即可。总数量可以直接使用 UnitsSold 计算得出，使用 GROUP BY 和 SUM 函数即可：

SELECT product_id, SUM(units) FROM UnitsSold GROUP BY product_id

因为每个产品不同时期的售价不同，因此在计算销售总额之前要先分别计算每个价格的销售总额。

每个价格的销售总额为对应时间内的价格∗对应时间内的数量对应时间内的价格 * 对应时间内的数

量对应时间内的价格∗对应时间内的数量。因为价格和时间在 Prices 表中，数量在 UnitsSold 表

中，这两个表通过 product_id 关联，那么我们就可以使用 LEFT JOIN 将两个表连接，通过

WHERE 查询对应时间内每个产品的价格和数量，并计算出对应的销售总额。

SELECT
    Prices.product_id AS product_id,
    Prices.price * UnitsSold.units AS sales,
    UnitsSold.units AS units
FROM Prices 
LEFT JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
AND (UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date)

计算出产品每个价格的销售总额后，同样的使用 SUM 函数计算出产品所有时间的销售总额，然后除以总数量并使用 ROUND 函数保留两位小数即可。

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

(17)1075. 项目员工 I

根据题目分析：
每一个项目中员工(GROUP BY分组)
平均工作年限(AVG函数)
精确到小数点后两位(ROUND函数)

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

(18)1633. 各赛事的用户注册率

首先我们观察这两个表以及问题，可以发现：
1.不同赛事是需要进行分别统计的
2.两个表虽然有共同的字段，但是并不需要连接。因为统计一门赛事注册的user_id在Register表中，统计user_id总数量在Users表中，两者都是可以独立查询出来的

那么梳理一下做题步骤：
1.不同赛事进行分组
2.分别统计一门赛事中注册了的user_id数量、user_id总数量
3.保留两位小数
4.结果按percentage降序排序，相同按contest_id的升序排序

# Write your MySQL query statement below
select contest_id , round(count(user_id) * 100/ (select count(*) from users), 2) percentage 
from Register
group by contest_id
order by percentage desc, contest_id

(19)1211. 查询结果的质量和占比

方法一：GROUP BY

本题主要考察在 MySQL 内做简单的计算操作，比如求平均值，求和等。在解题前先回顾一下相关的函数。

SUM()：返回某列的和。
AVG()：返回某列的平均值。
COUNT() ：返回某列的行数。
MAX() ：返回某列的最大值。
MIN() ：返回某列的最小值。

根据题意我们需要计算的是每个名称的数据，可以使用 GROUP BY 对名称（query_name）进行聚合，然后再处理数据。

计算质量 quality：
各查询结果的评分与其位置之间比率的平均值。

评分与其位置之前的比率为 rating/position，平均值为：

AVG(rating/position)
计算劣质查询百分比 poor_query_percentage：
评分小于 3 的查询结果占全部查询结果的百分比。

评分小于 3 的数量可以使用 SUM 和 IF，如果 rating 小于 3，那么数量加 1。全部查询结果可以直接使用 COUNT()。因为要求的是百分比，所以分子需要乘 100。

SUM(IF(rating < 3, 1, 0)) * 100 / COUNT(*)
最后不要忘记使用 ROUND() 函数将结果四舍五入到小数点后两位。

SELECT 
    query_name, 
    ROUND(AVG(rating/position), 2) quality,
    ROUND(SUM(IF(rating < 3, 1, 0)) * 100 / COUNT(*), 2) poor_query_percentage
FROM Queries
GROUP BY query_name

(20)

wayzinx

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
SQL高频50题（基础版）

关键字 SELECT 用于指定我们想要从表 Products 中检索的列。在这种情况下，我们想要检索 product_id 列。关键字 WHERE 用于根据特定条件过滤表 Products 中的行，条件是 low_fats 列的值为"Y"且 recyclable 列的值为"Y"。我们使用逻辑运算符 AND 将两个条件组合起来，确保最终结果只包含既是 low_fats又是recyclable 的产品ID。
复制链接

扫一扫

专栏目录