查询

1757. 可回收且低脂的产品

这题非常简单。题目要求既是低脂又是可回收

sql

select product_id from products where low_fats = 'Y' and recyclable = 'Y';

584. 寻找用户推荐人

两个条件 OR关系：被任何 id != 2 的用户推荐。没有被任何用户推荐。

sql

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

595. 大的国家

两个条件，满足其一 OR

sql

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

1148. 文章浏览 I

sql

SELECT DISTINCT viewer_id AS id FROM Views
WHERE author_id = viewer_id;

1683. 无效的推文

函数	含义	`'Hello'`	`'你好'` (UTF-8)	`'café'` (UTF-8)
`CHAR_LENGTH()`	字符数	5	2	4
`LENGTH()`	字节数	5	6	5

sql

SELECT tweet_id FROM Tweets WHERE CHAR_LENGTH(content) > 15;

连接

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

左连接 LEFT JOIN:以左表为基准，返回右表中所有的行，同时使用on关键返回右表中与左表按照字段匹配的行。如果右表返回的数据中存在空值，则自动使用NULL来进行填充确保右表中所有的数据都能返回

题目提到，如果某位员工没有唯一标识码，使用 null 填充即可。

sql

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

1068. 产品销售分析 I

使用JOIN 而不是 LEFT JOIN 就可以解决

特性	JOIN (INNER JOIN)	LEFT JOIN
匹配行	✅ 返回	✅ 返回
左表未匹配行	❌ 丢弃	✅ 保留（右表字段填 `NULL`）
右表未匹配行	❌ 丢弃	❌ 丢弃
结果集行数	≤ 两表中较小表的匹配行数	≥ 左表的行数
语义	"只取交集"	"保留左表全部，右表有则匹配，无则补NULL"

mysql

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

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

理解：一些顾客去购物会有 Visits表记录足迹。如果交易了会有 Transactions记录用户购买记录

显然 LEFT JOIN 可以让左表Visits关联出没有购买记录

sql

-- 看看有什么灵感
SELECT * FROM Visits v
LEFT JOIN Transactions t ON v.visit_id = t.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

筛选出无交易记录

sql

SELECT * FROM Visits v
LEFT JOIN Transactions t ON v.visit_id = t.visit_id
WHERE t.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

发现刚好对于题目的示例。

最终使用 COUNT()记录次数，并规定分组 GROUP BY

sql

SELECT v.customer_id, COUNT(*) 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;

197. 上升的温度

datediff()函数：日期1比日期2大，结果为正；如果日期1比日期2小，结果为负

MySQL

sql

-- 让两张表交叉联结
SELECT * FROM Weather w1, Weather w2;
-- 结果发现 一行中有两个日期。可以找到某一个固定日期（左边）和前面日期（右边）的所有行

SELECT *
FROM Weather w1
         JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

SELECT w1.id
FROM Weather w1
         LEFT JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

PostgreSQL

公式 w1.recordDate - interval '1 day' = w2.recordDate能保证是昨天

sql

select w1.id
from Weather as w1
join Weather as w2 on w1.recordDate -  interval '1 day' = w2.recordDate
where w1.temperature > w2.temperature;

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

两张表

可以看出开始时间和结束时间对应

sql

-- 内联
-- 同一个机器，且是在统一进程下开始工作
-- 左表代表开始，右表代表结束
SELECT *
FROM activity AS a1
         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';

最总结果(MySQL)

sql

-- 使用AVG()负责计算
SELECT
    a1.machine_id,
    ROUND(AVG(a2.timestamp - a1.timestamp),3) AS 'processing_time'
FROM activity AS a1
         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;

一张表

比较有技巧性。把start为一组使用负号，最后对结果进行修正 x2

sql

-- count(*) 计算出每个机器start+end会有两组
SELECT machine_id, count(*)
FROM activity
GROUP BY machine_id;

sql

-- 对结果进行修正 * 2 MySQL
SELECT machine_id AS 'machine_id',
    ROUND(
        SUM(IF(activity_type = 'start', -timestamp, timestamp))
        / COUNT(*)
        * 2
        ,3) AS 'processing_time'
FROM Activity
GROUP BY machine_id;

577. 员工奖金

先关联表分析一下

sql

SELECT *
FROM Employee
         LEFT JOIN Bonus ON Employee.empId = Bonus.empId;

从题目可以看出使用 OR

sql

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

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

根据题目以及示例，即使没有参加的科目也要列出。

**CROSS JOIN：**交叉连接，对表中每一行进行两两连接，即每个ID都将对应上所有的科目subject_name，不需要匹配条件
sql
```
SELECT *
FROM Students SS
CROSS JOIN Subjects SU;
```

现在有了每个学生对应所有科目。

LEFT JOIN 把Examinations当作右表，这样没参加该科目考试的学生为NULL

sql

SELECT *
FROM Students SS
CROSS JOIN Subjects SU
LEFT JOIN Examinations ES ON SS.student_id = ES.student_id;

分组，以student_id和subject_name分组。并排序

⚠️注：mysql对于没有主键标识的要求比较严格。且默认开启sql_mode=only_full_group_by使用GROUP BY要写完所有SELECT要查的

sql

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

作者：荔枝在学习链接：https://leetcode.cn/problems/students-and-examinations/solutions/3747647/mei-ge-bu-zou-xiang-jie-xiao-bai-ye-neng-m6le/ 来源：力扣（LeetCode）

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

方案一

LEFT JOIN获取每个员工与下属的关系表

sql

SELECT *
FROM Employee E1
         LEFT JOIN Employee E2 ON E1.id = E2.managerId;

查询以 E1.id分组每个员工下有多少个下属

sql

SELECT E1.name AS Name, COUNT(*) AS cnt
      FROM Employee E1
               LEFT JOIN Employee E2 ON E1.id = E2.managerId
      GROUP BY E1.id

最后讲这个表进行命名为新表后使用 WHERE

sql

SELECT Name
FROM (SELECT E1.name AS Name, COUNT(*) AS cnt
      FROM Employee E1
               LEFT JOIN Employee E2 ON E1.id = E2.managerId
      GROUP BY E1.id) AS ReportCount
WHERE ReportCount.cnt >= 5;

方案二

HAVING WHERE 子句在 GROUP BY 之前执行，它无法对聚合函数（如 COUNT()、SUM()、AVG()）的结果进行筛选。而 HAVING 在 GROUP BY 之后执行，可以基于聚合结果做条件判断。

下面将查询结果按照次数筛选

sql

SELECT E1.name AS Name
FROM Employee E1
         LEFT JOIN Employee E2 ON E1.id = E2.managerId
GROUP BY E1.id
HAVING count(E1.id) >= 5;

1934. 确认率

这题不看答案有点难懂Signups表是干嘛的

根据题目：确认率 = confirmed数 / 操作数

左表 Signups 右表 Confirmations

sql

SELECT *
FROM Signups AS S
         LEFT JOIN Confirmations AS C ON S.user_id = C.user_id;

可以看出该用户的操作结果 action。有NULL、确认和超时

AVG以 user_id分组，分子为确认confirmed的数量。

IFNULL将用户只登录了，没有操作数记录为0才能符合题意，这样就能记录总登录数了

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;

力扣高频SQL基础题笔记

查询

连接

两张表

一张表