力扣高频 SQL 50 题（基础版）_sql经典例题利扣

1.可回收且低脂的产品

答案：select product_id from Products where low_fats='Y' and recyclable='Y'

较为简单入门。

2.寻找用户推荐人

答案：select name from customer where referee_id <> 2 or referee_id is null

这里面需要注意的是id!=2的情况中，null也需要算进去，<>这种一般的逻辑符号是会忽略null的，所以需要需要加一个or来补充。

3.大的国家

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

较为简单

4.文章浏览

答案：select distinct author_id as id from Views

where author_id=viewer_id

order by author_id

这里的逻辑是要找author_id=viewer_id的所有行，但是可以看到44 44 有两个，所以需要去重 就需要引入distinct这类语句。

5.无效的推文

答案：select·‌tweet_id from·‌Tweets

where·‌length(content)>15

这里需要注意的是 length（） 函数的使用，（）里面是属性 也是列，表示这列中的某个元素的字符长度超过15。

6.上级经理已离职的公司员工

在 SQL 中，employee_id 是这个表的主键。
这个表包含了员工，他们的薪水和上级经理的id。
有一些员工没有上级经理（其 manager_id 是空值）。

查找这些员工的id，他们的薪水严格少于$30000 并且他们的上级经理已离职。当一个经理离开公司时，他们的信息需要从员工表中删除掉，但是表中的员工的manager_id  这一列还是设置的离职经理的id 。

返回的结果按照employee_id 从小到大排序。

查询结果如下所示：

解答：

select employee_id from Employees

where salary <30000 and manager_id not in(select employee_id from Employees)

order by employee_id

这个题的思路是先选出工资低于30000的员工id，然后对比 被选出的表当中的employee_id与employee_id，如果相等，则说明这个人的领导没有辞职，如果不在这个表中 那就说明他的领导辞职了。

1. select employee_id from Employees: 这行代码是查询语句的开始，它指定了要从Employees表中选择employee_id字段。employee_id是员工的唯一标识符。

2. where salary < 30000: WHERE子句用于过滤结果，只选择那些salary字段值小于30000的记录。

3. and manager_id not in (select employee_id from Employees): 这个AND条件进一步过滤了上一步的结果。它指定manager_id字段的值不能是Employees表中任何记录的employee_id。换句话说，它排除了那些有直属上司的员工，只选择了那些没有直属上司的员工（即可能是部门领导或公司高层管理人员）。

4. order by employee_id: ORDER BY子句用于对结果进行排序。这里指定了按照employee_id字段的值进行升序排序。

综上所述，这条SQL语句的作用是：从Employees表中选择那些工资低于30000元且没有直属上司的员工的员工ID，并按员工ID进行排序。这通常用于找出公司中高层管理人员或独立工作的员工。

7.626换座位

思路：因为这个表是奇数的，所以可以先 判断 id%2=0，是否是偶数，是偶数可以直接id-1，那就相当于变换了，如果id是最后一个的话 那么id不变，if id=select count（*）from Seat then id

，else否则 id+1 end 囊括了所有情况。

select(

    case when id%2=0 then id-1

    when id=(select count(*) from Seat) then id

    else id+1 end

    ) as  id,student from Seat order by id

8.电影评分（着重再看看）

请你编写一个解决方案：

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

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

返回结果格式如下例所示。

答案：(select name as results from movierating join users using(user_id) group by user_id order by count(movie_id) desc,name limit 1 ) union all (select title as results from movierating join movies using(movie_id) where DATE_FORMAT(created_at,'%Y-%m')='2020-02' group by movie_id order by avg(rating) desc,title limit 1 )

有点难了 ，需要细化 拆开来看，

这条SQL查询语句使用了`UNION ALL`操作符来合并两个查询的结果集。每个查询都从`movierating`表中获取数据，并与另一个表（`users`或`movies`）进行连接，然后进行分组、排序和限制结果数量。下面是对这条语句的逐行解释：

1. `(select name as results from movierating join users using(user_id) group by user_id order by count(movie_id) desc,name limit 1 )`:
   - 这是一个子查询，它首先通过`JOIN`操作将`movierating`表和`users`表连接起来，使用`user_id`作为连接条件。
   - `GROUP BY user_id`表示按用户ID进行分组。
   - `ORDER BY count(movie_id) desc, name`表示首先按照每个用户的电影数量降序排序，如果电影数量相同，则按照用户的名字升序排序。
   - `LIMIT 1`表示只选择排序后的第一条记录。
   - `name as results`表示将`users`表中的`name`字段重命名为`results`。

2. `union all`:
   - `UNION ALL`操作符用于合并两个`SELECT`语句的结果集，它不会去除重复的行。

3. `(select title as results from movierating join movies using(movie_id) where DATE_FORMAT(created_at,'%Y-%m')='2020-02' group by movie_id order by avg(rating) desc,title limit 1 )`:
   - 这是第二个子查询，它通过`JOIN`操作将`movierating`表和`movies`表连接起来，使用`movie_id`作为连接条件。
   - `WHERE DATE_FORMAT(created_at,'%Y-%m')='2020-02'`是一个过滤条件，它只选择`created_at`字段在2020年2月创建的电影。
   - `GROUP BY movie_id`表示按电影ID进行分组。
   - `ORDER BY avg(rating) desc, title`表示首先按照电影的平均评分降序排序，如果平均评分相同，则按照电影标题升序排序。
   - `LIMIT 1`表示只选择排序后的第一条记录。
   - `title as results`表示将`movies`表中的`title`字段重命名为`results`。

这条查询语句的目的是：
- 从第一个子查询中选择一个用户，该用户在`movierating`表中拥有最多的电影评分，且如果评分数量相同，则选择名字在字典序上排在最前的用户。
- 从第二个子查询中选择一个在2020年2月创建的电影，该电影在`movierating`表中拥有最高的平均评分，且如果平均评分相同，则选择标题在字典序上排在最前的电影。
- 最后，使用`UNION ALL`将这两个结果合并为一个结果集。

自学SQL习题SQL Lesson 0+1+2: 

在学习SQL语法之前有必要解释一下什么是关系型数据库（Relational databases)？数据库由若干张表(Table)组成，这里说的数据Table很像Excel里的表; 正如Excel里的表格，Table也是由 行(rows)和列(columns)组成

一个Table存储一个类别的数据，每一行是一条数据，每一列是这种数据的一个属性； Table就像一个二维的表格，列(columns)是有限固定的，行(rows)是无限不固定的

举个例子, 比如我们数据库里有一张表(Table). 是用来存储世界上所有的狗，每一行(rows)是一条狗的信息。那么你会在这个狗Table里存储 哪些狗的属性列(columns)呢？比如 编号，名字，体重，身高，品种，年龄，毛发颜色？。让我们来看一下这张表，暂且就叫表 Dog吧！

Table（表）: Dog

上面就是一个Dog表的简单展示，像这样一个狗数据库里，除了这张狗（Dog）表，还可能存在其他的表，比如有一张表是存狗品种信息， 另外有一张表是存狗主人信息的 ...

我们学习SQL, 其实最后目的就是在类似这张Dog表数据基础上, 可以通过SQL来回答诸如以下的一类问题： "现在全世界名字叫 'Mike'的狗有几条?", 或者 "有多少条狗的毛发是 红色的?", 等等。 通过SQL我们可以方便的去获取，统计这些信息，在接下来的更多章节中你会有更深的体会.

1. 【简单查询】找到所有电影的名称title
   1. SELECT Title FROM movies;
2. 【简单查询】找到所有电影的导演
   1. SELECT distinct Director FROM movies;
3. 【简单查询】找到所有电影的名称和导演
   1. SELECT Title, Director FROM movies;
4. 【简单查询】找到所有电影的名称和上映年份
   1. SELECT Title, Year FROM movies;
5. 【简单查询】找到所有电影的所有信息
   1. SELECT * FROM movies;
6. 【简单查询】找到所有电影的名称,Id和播放时长
   1. SELECT Title, Id, Length_minutes FROM movies;

1. 【简单条件】找到id为6的电影
   1. SELECT * FROM movies WHERE Id = 6;
2. 【简单条件】找到在2000-2010年间year上映的电影
   1. SELECT * FROM movies WHERE Year BETWEEN 2000 AND 2010;
3. 【简单条件】找到不是在2000-2010年间year上映的电影
   1. SELECT * FROM movies WHERE Year NOT BETWEEN 2000 AND 2010;
4. 【简单条件】找到头5部电影
   1. SELECT * FROM movies ORDER BY Id ASC LIMIT 5;
5. 【简单条件】找到2010（含）年之后的电影里片长小于两个小时的片子
   1. SELECT * FROM movies WHERE Year >= 2010 AND Length_minutes < 120

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

• 现在有一个工厂网站由几台机器运行，每台机器上运行着 相同数量的进程 。编写解决方案，计算每台机器各自完成一个进程任务的平均耗时。

  完成一个进程任务的时间指进程的'end' 时间戳 减去 'start' 时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。

  结果表必须包含machine_id（机器ID） 和对应的 average time（平均耗时） 别名 processing_time，且四舍五入保留3位小数。

  以 任意顺序 返回表。

• 这里需要知道的知识：

  • round（...,3）对...的内容取 3为小数
  • sum（...）...是需要求和的内容
  • if（.1.. , .2.. , ..3..）1中是判定条件，2中是如果是ture则输出的内容，3中是如果是fasle则输出的内容
  • count（...）...可以是某种属性某种列，也可以是*或者1
  • 

• select machine_id ,
   
  round(
   
  sum(if(activity_type ='end',timestamp,-timestamp))/count(distinct process_id )
   
  ,3) as processing_time 
   
  from Activity
  group by machine_id 
  

  10.上升的温度

• 编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。

  返回结果 无顺序要求 。

• 

• 需要知道的知识：

  • DATEDIFF()语句，DATEDIFF(datepart, date1, date2) datepart: 指定你想要比较的时间单位，例如天（day）、小时（hour）、分钟（minute）等。date1: 第一个日期值。date2: 第二个日期值。

  • SELECT DATEDIFF(day, '2024-01-01', '2024-05-24') AS DaysDifference;

    编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 
     
    返回结果 无顺序要求 
     
    select a.id from Weather a, Weather b
     
    where a.Temperature > b.Temperature and datediff (a.recordDate,b.recordDate) = 1
    

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

  • 有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。

    返回以 任何顺序 排序的结果表。

  • 

  • 需要知道的知识：

  • 左连接 from 左表 left join 右表 on 条件

  • SELECT customer_id, count(customer_id) count_no_trans
    FROM Visits v
    LEFT JOIN transactions t 
    ON v.visit_id = t.visit_id
    WHERE transaction_id IS NULL
    GROUP BY customer_id;
    

    • count（*）

    • count（具体内容）

  • 总结来说，COUNT(*)用于获取总行数，而COUNT(具体内容)用于获取非NULL值的数量。在编写SQL查询时，应根据实际需求选择合适的计数方式。

  • 12.员工奖金

  • 编写解决方案，报告每个奖金 少于 1000 的员工的姓名和奖金数额。

    以 任意顺序 返回结果表。

  • 

# Write your MySQL query statement below
select name ,bonus 
from Employee a left join Bonus b
on a.empId=b.empId
where bonus < 1000 or bonus is null

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

查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

select a.student_id ,a.student_name ,a.subject_name ,count(b.subject_name) as attended_exams 
from 
(select student_id ,student_name ,subject_name from Students cross join Subjects) as a
left join Examinations b on a.student_id=b.student_id and a.subject_name=b.subject_name 
group by a.student_id,a.subject_name 
order by student_id

14.有趣的电影

编写解决方案，找出所有影片描述为 非 boring (不无聊) 的并且 id 为奇数 的影片。

返回结果按 rating 降序排列。

select *
from cinema
where id%2=1 and description != 'boring' 
order by rating desc

需要知道 %2可以作为判断是否是奇偶数的标准。

15.平均售价

编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。

select p.product_id, ifnull(round(sum(price * units) / sum(units), 2), 0) as average_price
from Prices p 
left join UnitsSold u on u.product_id = p.product_id
and u.purchase_date between p.start_date and p.end_date
group by p.product_id

在SQL中，IFNULL是一个函数，它接受两个参数：第一个参数是可能为NULL的表达式，第二个参数是当第一个参数为NULL时的替代值。如果第一个参数不是NULL，IFNULL函数返回第一个参数的值；如果第一个参数是NULL，则返回第二个参数的值。

这个题需要考虑空值的情况，也就是第一个表有可能是空的。

比如product_id=3 ，也有price但是销售量为0，那么此时它的平均售价需要为0才行。

16.项目员工

请写一个 SQL 语句，查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。

select project_id  ,round(avg(e.experience_years ),2)average_years 
from Project p left join Employee e
on p.employee_id =e.employee_id 
group by p.project_id  

这里就一个avg round 语句需要注意，左连接两个表，通过group by一筛选就完事 比较简单

17.各赛事的用户注册率

编写解决方案统计出各赛事的用户注册百分率，保留两位小数。

返回的结果表按 percentage 的 降序 排序，若相同则按 contest_id 的 升序 排序。

SELECT b.contest_id, ROUND(COUNT(b.user_id) * 100.0 / t.total, 2) AS percentage 
FROM Register b
CROSS JOIN (SELECT COUNT(user_id) AS total FROM Users) AS t
GROUP BY b.contest_id
ORDER BY percentage DESC, b.contest_id;

思路：统计所有的users有多少可以作为一个表 和后面的表连接在一起，然后计数相除。

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

编写一个解决方案，找出至少有五个直接下属的经理。

以 任意顺序 返回结果表。

 select name from Employee where id in
 (
    select managerId from Employee group by managerId having count(1) >= 5
 )

思路 找出所在managerId中出现次数超过5的id，然后在原表中输出这些id所对应的name就行了。太绝了。 

where xxxx in （一个表 select所写的条件表，这个表中 managerId出现的次数需要大于等于5）

group by XX按照XX分组，并且having count（1）大于等于5 就是说要求XX出现的次数大于5

19.确认率

用户的 确认率 是 'confirmed' 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。

编写一个SQL查询来查找每个用户的 确认率 。

select s.user_id,round(avg(if(c.action = 'confirmed',1,0)),2) confirmation_rate 
from Signups s
left join
Confirmations c
on s.user_id=c.user_id
group by s.user_id;

avg if 函数联合使用 太妙了。

20.产品销售分析

编写解决方案，以获取 Sales 表中所有 sale_id 对应的 product_name 以及该产品的所有 year 和 price 。

返回结果表 无顺序要求 。

结果格式示例如下。

SELECT activity_date AS day , COUNT(DISTINCT user_id) AS active_users
FROM activity
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY activity_date

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

展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。

你可以以 任意 顺序返回结果表。

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

22.每位教师所教授的科目种类的数量

查询每位老师在大学里教授的科目种类的数量。

以 任意顺序 返回结果表。

select DISTINCT teacher_id, count(DISTINCT subject_id) as cnt
from Teacher
group by  teacher_id