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

编写解决方案找出既是低脂又是可回收的产品编号。

1
2
3

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

2.寻找用户推荐人（简单）

1
2
3

select name 
from Customer
where referee_id!=2 or referee_id IS NULL

注意：

在 SQL 中，NULL 表示未知值，不能直接用 = 或 != 来比较。
正确的写法是：

判断为空：referee_id IS NULL
判断不为空：referee_id IS NOT NULL

所以 referee_id = null 永远返回 false

3.大的国家（简单）

1
2
3

select name,population,area  
from World
where  area>=3000000 or population>=25000000

4.文章浏览（简单）

1
2
3

select distinct author_id as id
from Views
where author_id = viewer_id order by  author_id asc

5.无效的推文（简单）

1
2
3

select  tweet_id 
from Tweets
where  length(content) >15

注意：content.size 在 SQL 里根本不存在。
SQL 没有 size 这种方法，字符串长度是要用 LENGTH() 或者 CHAR_LENGTH()（不同数据库有差别）。

6.使用唯一标识码替换员工ID（简单）

1
2
3

select EmployeeUNI.unique_id ,Employees.name 
from Employees
left join EmployeeUNI on Employees.id = EmployeeUNI.id

7.产品销售分析 I（简单）

1
2
3

select p.product_name,s.year,s.price 
from Sales s 
left join Product p on s.product_id = p.product_id

8.进店却未进行过交易的顾客（简单）

思路分析

表结构
- Visits 表：顾客到访记录，每条记录包含 visit_id 和 customer_id。
- Transactions 表：交易记录，每条记录包含 transaction_id、visit_id、amount。
1
visit_id
是两张表的连接键。
目标
- 找出所有在 Visits 中存在但在 Transactions 中没有对应交易的 visit_id；
- 按照 customer_id 统计这些没有交易的 visit_id 的次数。
实现方法

使用

左连接 + 筛选空交易记录

：
- LEFT JOIN：保证 Visits 表中的所有记录都出现；
- WHERE t.transaction_id IS NULL：只保留没有对应交易的 visit；
- GROUP BY customer_id：统计每个顾客的无交易次数。

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

9.上升的温度（简单）

select cur.id
from Weather cur
join Weather pre on cur.recordDate  = DATE_ADD(pre.recordDate, INTERVAL 1 DAY)
where cur.temperature > pre.temperature

或者

SELECT cur.id
FROM Weather cur
JOIN Weather pre
  ON DATEDIFF(cur.recordDate, pre.recordDate) = 1
WHERE cur.temperature > pre.temperature;

DATEDIFF()是MySQL 中的一个日期时间函数,用于计算两个日期之间的天数差。

10.每台机器的进程平均运行时间（简单）

自连接

#计算每台机器各自完成一个进程任务的平均耗时。
#完成一个进程任务的时间指进程的'end' 时间戳 减去 'start' 时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。
#分组，start一组，end一组
select s.machine_id , round(Avg(e.timestamp - s.timestamp ),3)as processing_time
from Activity s
left join Activity e 
on s.machine_id  = e.machine_id
and s.process_id = e.process_id
and s.activity_type = 'start'
and e.activity_type = 'end'
group by s.machine_id

11员工奖金（简单）

#编写解决方案，报告每个奖金 少于 1000 的员工的姓名和奖金数额。以 任意顺序 返回结果表。
select e.name, b.bonus 
from Employee e
left join Bonus b on e.empId = b.empId
where bonus < 1000 or bonus is null

12学生们参加各科测试的次数（简单）

题意

Students 表：学生信息
Subjects 表：所有科目信息
Examinations 表：学生参加考试的记录（一个学生参加某科目一次考试就是一条记录，可以重复）

题目要求：
查询出 每个学生参加每一门科目测试的次数，即 学生 × 科目 的所有组合（即使没参加过，也要显示，次数为 0）。
按 student_id 和 subject_name 排序。

解题思路

学生 × 科目的所有组合

：
- 这需要 笛卡尔积（cross join）。
- SQL 里可以用 JOIN 或 CROSS JOIN，即让每个学生和每个科目配对。
统计考试次数

：
- 将 Examinations 表按 student_id + subject_name 分组统计 COUNT(*)。
- 然后和上一步的学生-科目对比结果进行 LEFT JOIN，没有考试记录的地方用 0。
排序

：
- 按 student_id, subject_name 升序。

#查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。
select St.student_id, St.student_name,Su.subject_name,Count(e.subject_name) as attended_exams
from Students St
Cross join Subjects Su
Left join Examinations e 
on St.student_id = e.student_id and Su.subject_name = e.subject_name
group by St.student_id, St.student_name,Su.subject_name
order by St.student_id,Su.subject_name;

CROSS JOIN 保证了每个学生和每个科目都会配对，即使没考过。
LEFT JOIN 保证了没有考试记录的组合也会出现。
COUNT(e.subject_name) 遇到没有匹配时（NULL）会自动统计为 0。
GROUP BY 必须包括 student_id, student_name, subject_name。
最后 ORDER BY 确保输出顺序。

13.有趣的电影（简单）

#编写解决方案，找出所有影片描述为 非 boring (不无聊) 的并且 id 为奇数 的影片。返回结果按 rating 降序排列。
select *
from cinema c
where description != 'boring' and mod(id,2) = 1
order by rating  desc

14.平均售价（简单）

#编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。如果产品没有任何售出，则假设其平均售价为 0。
select  p.product_id, ROUND(
    COALESCE( SUM(u.units * p.price) / NULLIF(SUM(u.units), 0), 0 ), 2) as  average_price #产品没有售出时，默认为0
from Prices p 
left join UnitsSold u on p.product_id = u.product_id 
and u.purchase_date between p.start_date and p.end_date   
group by  p.product_id

COALESCE是SQL标准中的关键字，主要用于处理字段空值，确保运算正常进行。其核心功能是为表达式提供默认值，当表达式结果为NULL时返回指定的默认值

15项目员工I（简单）

#查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。以 任意 顺序返回结果表。
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 project_id

16.各赛事的用户注册率（简单）

#统计出各赛事的用户注册百分率，保留两位小数。
#返回的结果表按 percentage 的 降序 排序，若相同则按 contest_id 的 升序 排序。
#(x/y）*100
#x:统计Register 表下contest_id中的数据数量 count(user_id)   并分组group by contest_id
#y = 总人数 select count (*) from u
select r.contest_id,
Round(count(r.user_id) / (select count(*)from Users)*100,2) as percentage
from Register r
group by r.contest_id
order by percentage desc, r.contest_id asc

17.查询结果的质量占比（简单）

#找出每次的 query_name 、 quality 和 poor_query_percentage。
#quality 和 poor_query_percentage 都应 四舍五入到小数点后两位 。
select 
query_name, 
round(Avg(rating / position),2) as quality, 
round(100 * Avg(rating < 3),2) as poor_query_percentage
from Queries 
group by query_name

18至少有5名直接下属的经理（中等）

#找出至少有五个直接下属的经理。
select e.name
from Employee e
 join (
    select managerId,count(*) as num
    from Employee 
    where managerId is not null
    group by managerId 
)c 
on c.managerId = e.id
where c.num>=5

方法2

SELECT name
FROM Employee
WHERE id IN (
  SELECT managerId
  FROM Employee
  WHERE managerId IS NOT NULL
  GROUP BY managerId
  HAVING COUNT(*) >= 5
);

19.每位教师所教师的科目种类的数量（简单）

#查询每位老师在大学里教授的科目种类的数量。
select teacher_id,  count(distinct subject_id) as cnt
from Teacher
group by teacher_id

20.查询近30天活跃用户数（简单）

#编写解决方案，统计截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。
select activity_date as day,count(distinct user_id)as active_users
from  Activity 
where datediff("2019-07-27",activity_date) between 0 AND 29
GROUP BY activity_date

时间差函数TIMESTAMPDIFF、DATEDIFF的用法

我们在写sql语句，尤其是存储过程中，会频繁用到对于日期、时间的比较和判断，那么对于这两个时间差比较函数用法做一个举例介绍。

datediff函数，返回值是相差的天数，不能定位到小时、分钟和秒。

相差2天 select datediff(‘2018-03-22 09:00:00’, ‘2018-03-20 07:00:00’);

TIMESTAMPDIFF函数，有参数设置，可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。

相差1天 select TIMESTAMPDIFF(DAY, ‘2018-03-20 23:59:00‘, ‘2015-03-22 00:00:00‘);
相差49小时 select TIMESTAMPDIFF(HOUR, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);
相差2940分钟 select TIMESTAMPDIFF(MINUTE, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);
相差176400秒 select TIMESTAMPDIFF(SECOND, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);

21.销售分析III（简单）

#编写解决方案，报告 2019年春季 才售出的产品。即 仅 在 2019-01-01 （含）至 2019-03-31 （含）之间出售的商品。
select distinct s.product_id,p.product_name
from Sales s
left join Product p on s.product_id = p.product_id
where s.product_id not in (
    select product_id
    from Sales s
    where s.sale_date not between '2019-01-01' and '2019-03-31'
)

条件聚合

SELECT p.product_id, p.product_name
FROM Product p
JOIN Sales s ON s.product_id = p.product_id
GROUP BY p.product_id, p.product_name
HAVING
    SUM(s.sale_date BETWEEN '2019-01-01' AND '2019-03-31') > 0   -- 区间内至少有一笔
AND SUM(s.sale_date < '2019-01-01' OR s.sale_date > '2019-03-31') = 0;  -- 区间外 0 笔

在 MySQL 中，布尔表达式可当 0/1 使用，SUM(布尔) 就是计数。
这写法通常很快，因为只扫一遍 Sales 并分组。

22.超过5名学生的课（简单）

#查询 至少有 5 个学生 的所有班级。
select class
from Courses
group by class
having count(student) >=5

23.求关注者的数量（简单）

#编写解决方案，对于每一个用户，返回该用户的关注者数量。
select user_id,count(follower_id ) as followers_count
from Followers
group by user_id
order by user_id

24.确认率（中等）

#用户的 确认率 是 'confirmed' 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。
#编写一个SQL查询来查找每个用户的 确认率 。
#confirmed 为该用户确认的消息占发送总消息的比例
select s.user_id,Round(IFNULL(AVG(c.action ='confirmed'),0),2)as confirmation_rate
from  Signups s
left join Confirmations c
on  c.user_id = s.user_id
group by s.user_id

25.每月交易I（中等）

#编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
SELECT
  DATE_FORMAT(trans_date, '%Y-%m') AS month,
  country,
  COUNT(*)                                  AS trans_count,
  SUM(state = 'approved')                   AS approved_count,
  SUM(amount)                               AS trans_total_amount,
  SUM(CASE WHEN state = 'approved'
           THEN amount ELSE 0 END)          AS approved_total_amount
FROM Transactions
GROUP BY DATE_FORMAT(trans_date, '%Y-%m'), country

用一次分组统计把所有指标都算出来。关键点：

用 DATE_FORMAT(trans_date, '%Y-%m') 取“年-月”作为分组的 month。
事务总数：COUNT(*)
批准的事务数：SUM(state='approved')（在 MySQL 中布尔表达式为真记作 1）
事务总金额：SUM(amount)
批准的事务总金额：SUM(CASE WHEN state='approved' THEN amount ELSE 0 END)

26.即时食物配送II（中等）

#如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。
#「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。
#编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。
select Round(100*AVG(d.order_date = d.customer_pref_delivery_date),2) as immediate_percentage
from Delivery d
join (
    select  customer_id,MIN( order_date ) as first_order_date
    from Delivery
    group by customer_id
)f
on d.customer_id = f.customer_id
and d.order_date = f.first_order_date

27.只出现一次的最大数字（简单）

#找出最大的 单一数字 。如果不存在 单一数字 ，则返回 null 。
select Max(num) as num
from MyNumbers
where num not in( select num from MyNumbers group by num having count(num) > 1)

28.游戏玩法分析IV（中等）

# Write your MySQL query statement below
#编写解决方案，报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录后的第二天登录的玩家数量，并将其除以总玩家数。
#1.查玩家总数 
#select  count( distinct player_id) from Activity
#2.查询第二天登录的玩家数量
#2.1先查每个玩家的首次登录日期
#(select player_id,Min(event_date) as first_login_date
#from Activity
#group by player_id ) as F1
#2.2找出第二天登录的玩家数量：首次登录+1
#inner join Activity as A
#on F1.player_id = A.player_id
#and A.event_date = DATE_ADD(F1.first_login_date，INTERVAL 1 DAY)

 #整合这几步最终的sql为
 select
  Round(
     -- 分子: COUNT 出 JOIN 成功的玩家数
     Count(F1.player_id) /
     -- 分母: 子查询计算总玩家数
     (select  count( distinct player_id) from Activity)
     --保留两位数字
     ,2
 ) as fraction
 from 
 (#2.1先查每个玩家的首次登录日期
   (select player_id,Min(event_date) as first_login_date
      from Activity
     group by player_id ) 
 )as F1
#2.2找出第二天登录的玩家数量：首次登录+1
inner join Activity as A
on F1.player_id = A.player_id
 and A.event_date = DATE_ADD(F1.first_login_date,INTERVAL 1 DAY)

下面这样写也是一样的

-- players whose first day +1 also login
SELECT ROUND(
         IFNULL(
           (
             SELECT COUNT(DISTINCT a.player_id)
             FROM (
               SELECT player_id, MIN(event_date) AS first_login
               FROM Activity
               GROUP BY player_id
             ) f
             JOIN Activity a
               ON a.player_id = f.player_id
              AND a.event_date = DATE_ADD(f.first_login, INTERVAL 1 DAY)
           ) / (SELECT COUNT(DISTINCT player_id) FROM Activity)
         , 0)
       , 2) AS fraction;

29.每位经理的下属员工数量（简单）

#编写一个解决方案来返回需要听取汇报的所有经理的 ID、名称、直接向该经理汇报的员工人数，以及这些员工的平均年龄，其中该平均年龄需要四舍五入到最接近的整数。
#返回的结果集需要按照 employee_id 进行排序。
select e. employee_id,e.name, 
Count(r.reports_to)as reports_count, 
Round(AVG(r.age),0)as average_age 
from Employees e
inner join Employees r
on e.employee_id = r.reports_to
group by r.reports_to
order by employee_id

30.买下所有产品的客户（中等）

#编写解决方案，报告 Customer 表中购买了 Product 表中所有产品的客户的 id。
select distinct customer_id 
from Customer c
group by  customer_id
#按 customer_id 查时：Customer 表中出现的product_key = Product 表中出现的product_key 
having count(distinct c.product_key) = (select count(product_key)as cnt from Product)

31.员工的直属部门(简单)

#一个员工可以属于多个部门。当一个员工加入超过一个部门的时候，他需要决定哪个部门是他的直属部门。请注意，当员工只加入一个部门的时候，那这个部门将默认为他的直属部门，虽然表记录的值为'N'.
#请编写解决方案，查出员工所属的直属部门。
#若员工在多个部门，则取 primary_flag = 'Y' 的那条；
#若员工只在一个部门（即 count(*)=1），就取那一条（不管 flag 是什么）。
select  employee_id,department_id
from Employee e
where e.primary_flag = 'Y'
or employee_id in(
    select employee_id
    from Employee
    group by employee_id
    having count(*) = 1
)
order by employee_id,department_id

解释：

子查询找出“只有一个部门”的员工 employee_id；
外层选出两类行：primary_flag='Y' 的行，以及只有一个部门的那位员工的那一行。

写法二：窗口函数（MySQL 8+）

WITH t AS (
  SELECT
    employee_id,
    department_id,
    primary_flag,
    COUNT(*) OVER (PARTITION BY employee_id) AS cnt
  FROM Employee
)
SELECT employee_id, department_id
FROM t
WHERE primary_flag = 'Y' OR cnt = 1
ORDER BY employee_id, department_id;

这条 SQL 用了 窗口函数 + 公共表表达式（CTE），把题目的两条规则一次性“写进行里”，再在外层做一次非常简单的筛选。逐句看——

CTE t 在干什么？

WITH t AS (
  SELECT
    employee_id,
    department_id,
    primary_flag,
    COUNT(*) OVER (PARTITION BY employee_id) AS cnt
  FROM Employee
)

WITH t AS (...) 定义了一个临时结果集 t，可以把它当成一张临时表使用。
里面最关键的是这句窗口函数：
1
COUNT(*) OVER (PARTITION BY employee_id) AS cnt
含义：

对同一个 employee_id 的所有行分组（但不折叠行），统计该员工出现了多少行

。

也就是给每一行都“贴一个标签”——
1
cnt
= 这个员工一共属于多少个部门。和
1
GROUP BY
的区别：
- GROUP BY employee_id 会把同一个员工的多行聚成一行，行粒度改变了，后面想拿 department_id 会变得困难。
- 窗口函数不会合并行，只是在“每一行上”附带一个“分组统计值”，保留了明细粒度，后续可以自由筛哪一行。
外层查询如何筛选？

SELECT employee_id, department_id
FROM t
WHERE primary_flag = 'Y' OR cnt = 1
ORDER BY employee_id, department_id;

cnt = 1：该员工 只在一个部门，就取这唯一的一行（不管 primary_flag 记录的是 Y 还是 N）。
primary_flag = 'Y'：该员工 在多个部门 时，只取标了主部门的那一行。

因此，这个 WHERE 同时覆盖了两类情况，满足题意：

只有一个部门 → cnt = 1，取它。
多个部门 → 用 primary_flag = 'Y' 选出主部门。

ORDER BY 只是为了输出稳定，可有可无。

用示例走一遍（题目样例）

原表（简化）：

employee_id	department_id	primary_flag
1	1	N
1	2	Y
2	1	Y
2	3	N
3	1	Y
4	2	N
4	3	Y

CTE t 里多了 cnt：

employee_id	department_id	primary_flag	cnt
1	1	N	2
1	2	Y	2
2	1	Y	2
2	3	N	2
3	1	Y	1
4	2	N	2
4	3	Y	2

外层 WHERE primary_flag='Y' OR cnt=1 保留的行：

emp=1：两行里只有 Y 的那行保留 → (1, 2)
emp=2：保留 Y 的那行 → (2, 1)
emp=3：cnt=1，唯一一行保留 → (3, 1)
emp=4：保留 Y 的那行 → (4, 3)

结果：每个员工一行，就是他的主部门。

为什么不用 GROUP BY？

如果你 GROUP BY employee_id，就只剩一行，department_id 该取谁？
除非你再写一轮子查询/关联去“把那一行拉回来”。窗口函数保留了明细粒度，所以外层筛选非常干净。

边界 / 数据质量说明

题目保证：员工加入多个部门时会唯一选一个主部门（Y），所以不会出现多个 Y。
如果担心数据异常（比如既无 Y 又多部门），可以用更稳妥的写法：

WITH t AS (
  SELECT
    employee_id, department_id, primary_flag,
    ROW_NUMBER() OVER (
      PARTITION BY employee_id
      ORDER BY CASE WHEN primary_flag='Y' THEN 0 ELSE 1 END
    ) AS rn
  FROM Employee
)
SELECT employee_id, department_id
FROM t
WHERE rn = 1;

更“容错”。

一句话概括这条 SQL 的巧妙点：
用窗口函数把“每个员工的部门数量”写到每一行上，再用一条简单的 WHERE 同时处理两种规则，既简洁又高效。

32.判断三角形（简单）

#对每三个线段报告它们是否可以形成一个三角形。
#两边之和大于第三边
#两边之差的绝对值小于第三边
select
 t1.*,
 if(((t1.x+t1.y)>t1.z and (t1.y+t1.z)>t1.x and (t1.x+t1.z)>t1.y),'Yes','No')as triangle 
from Triangle t1

法二

select 
t1.*,
case when x+y+z>2*greatest(x,y,z) then 'Yes'
else 'No'
end as triangle
from triangle

GREATEST() 是 MySQL 的一个内置函数，用来返回一组数值（或字符串）中的最大值。

语法

1	GREATEST(expr1, expr2, expr3, ...)

expr1, expr2, expr3...：可以是多个参数（数字或字符串）。
返回结果是这些参数里 最大的那个。

示例

1 2	SELECT GREATEST(10, 20, 30); -- 结果 30 SELECT GREATEST(5, -1, 100, 42); -- 结果 100

如果是字符串：

1	SELECT GREATEST('apple', 'banana', 'pear'); -- 结果 pear（按字典序）

回到你的 SQL

SELECT t1.*,
       CASE 
         WHEN x+y+z > 2*GREATEST(x,y,z) THEN 'Yes'
         ELSE 'No'
       END AS triangle
FROM triangle;

含义：

GREATEST(x,y,z) 找到三条边里最长的那条边。
如果 x+y+z > 2*最长边，就说明三边能构成三角形 → 输出 'Yes'。
否则 → 输出 'No'。

这是利用“三角形两边之和大于第三边”的判定条件。

33.连续出现的数字（中等）

#找出所有至少连续出现三次的数字。
#返回的结果表中的数据可以按 任意顺序 排列。
select distinct l1.num as ConsecutiveNums
from Logs l1
join Logs l2 on l1.id = l2.id - 1
join logs l3 on l2.id = l3.id - 1
where l1.num = l2.num
      and l2.num = l3.num

解释：

l1.id, l2.id, l3.id 分别是连续的三行（id, id+1, id+2）。
如果这三行的 num 都相同，就说明 num 连续出现了至少三次。
DISTINCT 去重，避免重复结果。

#找出所有至少连续出现三次的数字。
#返回的结果表中的数据可以按 任意顺序 排列。
select distinct num as ConsecutiveNums
from (
    select num,
    lead(num,1)over(order by id)as next1,
    lead(num,2)over(order by id)as next2
    from Logs
)t
where num = next1 and num = next2

解释：

LEAD(num, k) 可以取到后面第 k 行的值。
检查当前行的 num 与后两行是否相等。
相等就说明 num 连续至少三次。

34.修复表中的名字（简单）

# UPPER(str) 将字符串中所有字符转为大写
# LOWER(str) 将字符串中所有字符转为小写
select user_id ,Concat(UPPER(left(name,1)),LOWER(RIGHT(name,LENGTH(name)-1)))as name
from Users
order by user_id
# 一、计算字段

# 其实本题主要考察的就是计算字段的使用。
# 二、知识点
# 2.1 CONCAT() 函数

# CONCAT 可以将多个字符串拼接在一起。
# 2.2 LEFT(str, length) 函数

# 从左开始截取字符串，length 是截取的长度。
# 2.3 UPPER(str) 与 LOWER(str)

# UPPER(str) 将字符串中所有字符转为大写

# LOWER(str) 将字符串中所有字符转为小写
# 2.4 SUBSTRING(str, begin, end)

# 截取字符串，end 不写默认为空。

# SUBSTRING(name, 2) 从第二个截取到末尾，注意并不是下标，就是第二个。

# CONCAT 用来拼接字符串 ● LEFT 从左边截取字符 ● RIGHT 从右边截取字符 ● UPPER 变为大写 ● LOWER 变为小写 ● LENGTH 获取字符串长度

35.患某种疾病的患者（简单）

#查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。
select *
from Patients
where conditions like 'DIAB1%' 
   or conditions like '% DIAB1%';

36.删除重复的电子邮箱（简单）

#编写解决方案 删除 所有重复的电子邮件，只保留一个具有最小 id 的唯一电子邮件。
#（对于 SQL 用户，请注意你应该编写一个 DELETE 语句而不是 SELECT 语句。）
#运行脚本后，显示的答案是 Person 表。驱动程序将首先编译并运行您的代码片段，然后再显示 Person 表。Person 表的最终顺序 无关紧要 。
DELETE p1 from Person p1,
Person p2
where p1.Email = p2.Email and p1.id > p2.id

37.指定日期的产品价格（中等）

#一开始，所有产品价格都为 10。
#编写一个解决方案，找出在 2019-08-16 所有产品的价格。
#如果在 2019-08-16没数据，找表中在 2019-08-16以内中最接近在 2019-08-16的数据，否则置默认值10
select
    p1.product_id,
    coalesce(p2.new_price,10)   as price
from(select distinct product_id from Products)p1
left join Products p2
on p1.product_id = p2.product_id
and p2.change_date <= '2019-08-16'
#保证p2是最接近2019-08-16的数据
and not exists(
    select 1 from Products p3
    where p3.product_id = p2.product_id
    and p3.change_date <= '2019-08-16'
    and p3.change_date > p2.change_date
)
order by p1.product_id

要点

NOT EXISTS 这段用于“每个 product_id 选出 <= 目标日的最后一条”。
同样用 COALESCE(...,10) 兜底默认价。

COALESCE的用法参考文档：

MySQL中空值处理COALESCE函数及COALESCE函数使用_Mysql_脚本之家

另一种解法（MySQL 8+，窗口函数）

WITH last_change AS (
  SELECT
    product_id,
    new_price,
    ROW_NUMBER() OVER (PARTITION BY product_id
                       ORDER BY change_date DESC) AS rn
  FROM Products
  WHERE change_date <= '2019-08-16'
)
SELECT
  p.product_id,
  COALESCE(lc.new_price, 10) AS price     -- 没有记录则用默认价 10
FROM (SELECT DISTINCT product_id FROM Products) AS p
LEFT JOIN last_change lc
  ON lc.product_id = p.product_id AND lc.rn = 1   -- 取每个产品“最近一次”的那条
ORDER BY p.product_id;

说明

ROW_NUMBER() … PARTITION BY product_id ORDER BY change_date DESC：对每个产品按时间倒序编号，rn=1 就是“该日期之前最近的一次变价”。
WHERE change_date <= '2019-08-16'：只看目标日之前（含当天）的记录。
COALESCE(..., 10)：如果某产品在目标日前没有任何记录，返回默认价 10。
外层用 Products 的去重 product_id 保证输出覆盖所有出现过的产品。

38.最后一个能进入巴士的人（中等）

写法一：自连接

#编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告 person_name 。
#题目测试用例确保顺位第一的人可以上巴士且不会超重。
select person_name 
from Queue q1
#找最接近1000的上一次数据，最后要小于1000
where (
    select sum(weight)
    from Queue
    where turn<=q1.turn
)<=1000
order by turn desc limit 1

写法二：窗口函数+where in

select person_name 
from Queue 
#取最后一位
where turn in(
    select max(turn)
    from (
      select turn,person_id,person_name,weight,sum(weight) over(order by turn) as total_weight
      from Queue
    ) as a
    where a.total_weight<=1000
)

39.按分类统计薪水（中等）

select 类别+union+sum(case when)

#查询每个工资类别的银行账户数量。
#结果表 必须 包含所有三个类别。 如果某个类别中没有帐户，则报告 0 。
select 'Low Salary' as category,
    sum(case when income<20000 then 1 else 0 end) as accounts_count
from accounts
union 
select 'High Salary' as category,
    sum(case when income>50000 then 1 else 0 end) as accounts_count
from accounts
union 
select 'Average Salary' as category,
    sum(case when income>=20000 and income<=50000 then 1 else 0 end) as accounts_count
from accounts

select目标字段+where过滤条件+union去重合并表

select 'Low Salary' as category,
    count(*) as accounts_count
from accounts
where income<20000
union 
select 'Average Salary' as category,
    count(*) as accounts_count
from accounts
where income<=50000 and income>=20000
union
select 'High Salary' as category,
    count(*) as accounts_count
from accounts
where income>50000

ifnull输出0值+union all临时表+left join

# Write your MySQL query statement below
select a.category,
    ifnull(count(account_id),0) as accounts_count
from (
    select 'Low Salary' as category
    union all select 'Average Salary'
    union all select 'High Salary'
) as a left join 
(select 
    account_id,
    case 
        when income<20000 then 'Low Salary'
        when income<=50000 then 'Average Salary'
        else 'High Salary'
    end as category
from accounts ) as b on a.category=b.category
group by a.category

40.上级经理已离职的公司员工（简单）

#查找这些员工的id，他们的薪水严格少于$30000 并且他们的上级经理已离职。当一个经理离开公司时，他们的信息需要从员工表中删除掉，但是表中的员工的manager_id  这一列还是设置的离职经理的id 。
#返回的结果按照employee_id 从小到大排序。
select employee_id
from Employees
where salary<30000 and manager_id not in(
    select distinct employee_id
    from Employees
)
order by employee_id asc

写法2

select a.employee_id
from employees a
left join employees b
on a.manager_id=b.employee_id
where b.employee_id is null and a.manager_id is not null and a.salary<30000
order by a.employee_id

41.换座位（中等）

#编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。

#按 id 升序 返回结果表。
select 
case
    when id%2 = 1 and id<(select max(id) from Seat)then id+1
    when id%2 = 0 then id-1
    else id
    end as id,
    student
from Seat
order by id

id % 2 = 1：说明是奇数编号 → 一般换到后面一个座位 (id+1)。
id < (SELECT MAX(id) FROM Seat)：保证不是最后一个人，否则不要动。
id % 2 = 0：说明是偶数编号 → 换到前一个座位 (id-1)。
ELSE id：处理最后一个奇数 id 的情况，保持不变。
最后 ORDER BY id，按照交换后的顺序输出结果。

42.电影评分（中等）

请你编写一个解决方案：

查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。

字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。

# Write your MySQL query statement below
#查找评论电影数量最多的用户名(desc)。如果出现平局，返回字典序较小的用户名。(asc)
#查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。
#字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。
#1.先查评论电影数量最多的用户名(desc)。
(select u.name as results 
from Users u
left join MovieRating mr 
on u.user_id  = mr.user_id
group by u.user_id 
order by count(*)desc,name asc
limit 1)
union all
#2.再查在 February 2020 平均评分最高 的电影名称
(select title as results
from Movies m
left join MovieRating mr on m.movie_id = mr.movie_id and year(mr.created_at) = 2020 and month(mr.created_at)=2
group by mr.movie_id
order by Avg(mr.rating)desc,title asc
limit 1)

43.餐馆营业额变化增长（中等）

窗口函数

语法：

1
2
3

[你要的操作] OVER ( PARTITION BY  <用于分组的列名>
                    ORDER BY <按序叠加的列名> 
                    ROWS|RANGE <窗口滑动的数据范围> )

<窗口滑动的数据范围> 用来限定 [你要的操作] 所运用的数据的范围，具体有如下这些：

当前 - current row
之前的 - preceding
之后的 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following

举例

取当前行和前五行：ROWS between 5 preceding and current row --共6行
取当前行和后五行：ROWS between current row and 5 following --共6行
取前五行和后五行：ROWS between 5 preceding and 5 following --共11行
取当前行和前六行：ROWS 6 preceding（等价于between...and current row） --共7行
这一天和前面6天：RANGE between interval 6 day preceding and current row --共7天
这一天和前面6天：RANGE interval 6 day preceding（等价于between...and current row） --共7天
字段值落在当前值-100到+200的区间：RANGE between 100 preceding and 200 following  --共301个数值
select distinct visited_on,
sum_amount as amount,  
Round(sum_amount /7,2) as average_amount 
from(
    select visited_on,Sum(amount)over(order by visited_on range interval 6 day preceding) as sum_amount
    from Customer
)t
where datediff(visited_on,(select min(visited_on)from Customer))>=6

这里分为三层逻辑：

子查询部分 (select visited_on, Sum(amount) over(...))
使用窗口函数 Sum(amount) over(...)，计算当前日期往前 6 天（含当日）共 7 天的消费总额。
外层包裹的查询 (from (...) t)
直接把 sum_amount 带出来，并且结合 visited_on。
最外层查询
- 用 distinct visited_on，保证每一天只返回一条结果；
- 计算 7 天平均值：Round(sum_amount /7, 2)；
- 用 where 筛选掉前 6 天（因为不足 7 天没法算完整窗口）。

(1) Sum(amount) over(order by visited_on range interval 6 day preceding)

这是一个

窗口函数

，窗口范围是：
- 从当前行的 visited_on 往前推 6 天；
- 一直到当前 visited_on 这一天；
- 相当于一个滚动 7 天窗口。
例如：
- 当前行日期是 2019-01-07，窗口范围是 2019-01-01 ~ 2019-01-07；
- 当前行日期是 2019-01-08，窗口范围是 2019-01-02 ~ 2019-01-08。

这一步就实现了「累计近 7 天的总消费额」。

(2) datediff(visited_on,(select min(visited_on) from Customer)) >= 6

datediff 计算「当前行日期 - 最早日期」的天数；
>= 6 意味着：至少是第 7 天以后，才能算出完整 7 天的窗口。

例如：

最早日期是 2019-01-01；
那么 2019-01-01 ~ 2019-01-06 都不足 7 天，不会输出；
从 2019-01-07 开始，才能得到第一个合法窗口。

(3) Round(sum_amount /7, 2)

把总额除以 7，得到平均消费额；
Round(..., 2) 保留两位小数。

(4) distinct visited_on

每个 visited_on 可能有多个顾客记录（因为表里每个顾客每天一条记录），会导致窗口计算出来的 sum_amount 在当天有重复；
加上 distinct 确保一个日期只保留一条结果。

MySQL中Interval关键字的使用，看这一篇就够啦_mysql interval-CSDN博客

自连接

SELECT
	a.visited_on,
	sum( b.amount ) AS amount,
	round(sum( b.amount ) / 7, 2 ) AS average_amount 
FROM
	( SELECT DISTINCT visited_on FROM customer ) a JOIN customer b 
 	ON datediff( a.visited_on, b.visited_on ) BETWEEN 0 AND 6 
WHERE
	a.visited_on >= (SELECT min( visited_on ) FROM customer ) + 6 
GROUP BY
	a.visited_on
ORDER BY
  a.visited_on

44.按日期分组销售产品（简单）

GROUP_CONCAT() 是MySQL 中的一个聚合函数，用于将分组后的多行数据合并成一个字符串。

#编写解决方案找出每个日期、销售的不同产品的数量及其名称。
#每个日期的销售产品名称应按词典序排列。
#返回按 sell_date 排序的结果表。
SELECT 
    sell_date,
    COUNT(DISTINCT(product)) AS num_sold, 
    GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM 
    Activities
GROUP BY 
    sell_date
ORDER BY 
    sell_date ASC

45.列出指定时间段内所有的下单产品（简单）

#要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。
select p.product_name,sum(o.unit)as unit
from Products p
left join Orders o on p.product_id = o.product_id
where o.order_date like '2020-02%'
group by p.product_id
having unit>=100

select p.product_name,o.unit
from products AS p
JOIN(
    select product_id,sum(unit) AS unit
    from orders
    where year(order_date) = 2020 and month(order_date) = 2
    group by product_id
    having sum(unit) >=100
) 
AS o 
ON p.product_id = o.product_id;

46.查找拥有有效邮箱的用户（简单）

#编写一个解决方案，以查找具有有效电子邮件的用户。
select user_id,name,mail
from Users
where regexp_like(mail,'^[a-zA-Z][a-zA-Z0-9._-]*@leetcode\\.com$','c')
select * from users where mail collate utf8_bin REGEXP '^[a-zA-Z][a-zA-Z0-9\_\.\-]*@leetcode\\.com$'

47.好友申请II：谁有最多的好友（中等）

找出拥有最多好友的用户及其好友数。
即统计每个id在request_id 和accepter_id中出现的次数和。
每个人都可能发送好友请求，接收好友请求。好友数是统计每个id在接收好友请求和发送好友请求时所有的用户数。

union all不去重合并两张表，group by分组+order by排序+limit限制+子查询外count(*)行数

#编写解决方案，找出拥有最多的好友的人和他拥有的好友数目。
select a.id as id, count(*) as num 
#统计统计每个id在request_id 和accepter_id中出现的次数和。
from (
    select requester_id id 
    from RequestAccepted
    union all
    select accepter_id ids 
    from RequestAccepted
) as a
group by id 
order by num desc
limit 1

子查询内先求好友数，然后union，最后再sum求和

select 
    ids as id,sum(nums) as num
from (
    select 
        requester_id as ids,
        count(*) as nums
    from RequestAccepted
    group by ids
    union all
    select 
        accepter_id as ids,
        count(*) as nums
    from RequestAccepted
    group by ids
) as a
group by id
order by num desc
limit 1

48.第二高的薪水（中等）

#查询并返回 Employee 表中第二高的 不同 薪水 。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。
select ifnull
(
  (select distinct salary
  from Employee 
  order by Salary Desc
  limit 1,1),null
)
as SecondHighestSalary

自连接做法

select max(E2.salary) as SecondHighestSalary 
from 
Employee as E1 left join Employee as E2
on E1.salary > E2.salary

将表自连接的时候设置了 E1.salary > E2.salary，在这种情况下，每行E1里面的工资都比E2的工资高，对应着的就是第一＞第二、第二＞第三、第三＞第四……，只要取E2里面的工资最大值，这个值肯定就是第二高的工资
类似去最大取最大

去最大取最大

1
2
3

select max(salary) as SecondHighestSalary 
from Employee
where salary < (select max(salary) from Employee);

子查询查出最大 where排除最大
max()重新提取最大 null值有max不用考虑

49.2016年的投资（中等）

#编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：
#他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
#他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。
#tiv_2016 四舍五入的 两位小数 。
select round(sum(tiv_2016), 2) tiv_2016
 from insurance
  where tiv_2015 in 
(
	select tiv_2015 from insurance group by tiv_2015 having count(*) > 1
) and concat(lat, lon) in
 (
	select 
		concat(lat, lon)
	from insurance group by lat, lon having count(*) = 1
);

50.部门工资前三高的所有员工（困难）

#公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

#编写解决方案，找出每个部门中 收入高的员工 。
select d.name as Department,e1.name as Employee,e1.salary as Salary
from Employee e1
left join Department d on 
e1.departmentId = d.id   
where 
(   #查询工资中 排名前三
    select count(distinct e2.salary)
    from Employee e2
    where e2.salary>e1.salary and e1.departmentId = e2.departmentId
)<3