SQL学习笔记：Leetcode刷题 SQL入门

最新推荐文章于 2024-01-07 16:40:20 发布

Freyaah

最新推荐文章于 2024-01-07 16:40:20 发布

阅读量589

点赞数

分类专栏： SQL 文章标签： sql 学习 leetcode

本文链接：https://blog.csdn.net/hhvicky/article/details/129917218

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SQL学习笔记：Leetcode刷题

1873. 计算特殊奖金

写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头，那么他的奖金是他工资的100%，否则奖金为0。

难点：

返回的列带筛选条件

`if ( 条件，若条件正确则返回，若条件错误则返回`）

判断奇偶

employee_id%2=1
--or
mod(id,2)  --=1 是指id是奇数, =0 是指id是偶数

627. 变更性别

编写一个 SQL 查询来交换所有的 ‘f’ 和 ‘m’ （即，将所有 ‘f’ 变为 ‘m’ ，反之亦然），仅使用单个 update 语句，且不产生中间临时表。

难点：

update语句

update 表名
set 列名 = 修改后的值

case when

case 列名
	when 1 then 2 #将1换为2
	when 2 then 3
	else 3
end

196. 删除重复的电子邮箱

编写一个 SQL 删除语句来删除所有重复的电子邮件，只保留一个id最小的唯一电子邮件。

难点：

DELETE

将该表分为两份判断

DELETE p1.*
FROM Person as p1, Person as p2
WHERE p1.email = p2.email AND p1.id>p2.id

1667. 修复表中的名字

编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。

CONCAT

CONCAT(' A','B','C')

UPPER, LOWER

LEFT, SUBSTRING

LEFT('123456789',3) 返回左边3项
SUBSTRING('123456789',3) 从第3位开始返回

1484. 按日期分组销售产品

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。

COUNT 计数

DISTINCT 去重

GROUP CONCAT 多个拼接

连接group by的结果集时，可以指定自定义的分隔符，不指定的情况下，默认是 ‘,’

GROUP_CONCAT(DISTINCT product 
			 ORDER BY product ASC 
			 SEPARATOR ',')

解法

SELECT sell_date,
  COUNT(DISTINCT product) as num_sold,
  GROUP_CONCAT(DISTINCT product
                ORDER BY product ASC
                SEPARATOR ',') as products
FROM Activities
GROUP BY sell_date #需要将同一销售日期的并在一起
ORDER BY sell_date

1527. 患某种疾病的患者

写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。

近似查询

SELECT sell_date,
  COUNT(DISTINCT product) as num_sold,
  GROUP_CONCAT(DISTINCT product
                ORDER BY product ASC
                SEPARATOR ',') as products
FROM Activities
GROUP BY sell_date #需要将同一销售日期的并在一起
ORDER BY sell_date

1965. 丢失信息的雇员

写出一个查询语句，找到所有丢失信息的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：
雇员的姓名丢失了，或者
雇员的薪水信息丢失了，

思路：
将salary空白的join到Employees表

left join

A left join B on 关键key条件 WHERE 条件 #将B加到A中

union

1795. 每个产品在不同商店的价格

重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。

SELECT product_id, 'store1' as store, store1 as price FROM Products WHERE store1 is not NULL
UNION 
SELECT product_id, 'store2' as store, store2 as price FROM Products WHERE store2 is not NULL
UNION 
SELECT product_id, 'store3' as store, store3 as price FROM Products WHERE store3 is not NULL

608. 树节点

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：

若p_id为空，则为Root
若id不在p_id中，则为Leaf
否则则为Inner

case when的使用

SELECT id, 
  (case 
    when p_id is null then 'Root'
    when id not in (SELECT p_id FROM tree WHERE p_id is not null) then 'Leaf'
    else 'Inner'
  end) as Type
FROM tree

176. 第二高的薪水

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null 。

SELECT … LIMIT

select* from user limit 3 #取前三条数据
select * from user limit 1,3 #跳过第1个数，取3个数
select * from user limit 3 offset 1 #跳过1个数，取3个数

IFNULL

SELECT IFNULL(条件,a);  若条件为空，则输出a

解法

取max where <max()

SELECT MAX(salary) as SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee)

临时表（可解决筛不到NULL的问题）

SELECT (
    SELECT DISTINCT salary 
    FROM Employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
)  as SecondHighestSalary

IFNULL

SELECT IFNULL(
  (SELECT DISTINCT salary 
  FROM Employee
  ORDER BY salary DESC
  LIMIT 1 OFFSET 1),NULL
) as SecondHighestSalary

175. 组合两个表

编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为空 null 。

left join

将两个表left join 即可

SELECT firstName, lastName, city, state
FROM Person left join Address on Person.personID=Address.personID

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

请你编写一个 SQL 查询，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。
思路：

选出不在Transactions表中的visit_id，
并且要group_by，以把重叠的数据分隔开

SELECT customer_id, count(visit_id) as count_no_trans
FROM Visits
WHERE visit_id not in (SELECT visit_id FROM Transactions) #选出没有光顾过的
GROUP BY customer_id #要使用group by，否则数据会重合成一条

1148. 文章浏览 I

编写一条 SQL 查询以找出所有浏览过自己文章的作者，结果按照 id 升序排列。

SELECT DISTINCT author_id as id
FROM Views
WHERE author_id = viewer_id ##浏览过既author_id = viewer_id
ORDER BY id asc #升序asc

197. 上升的温度

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。

datediff(a,b) 返回a，b相差的天数（a-b）

思路：

因为有比较，可以拆分成两个表处理
有两个筛选条件，a温度低于b温度，且a日期比b日期早一天

SELECT b.id
FROM Weather as a, Weather as b
WHERE a.Temperature<b.Temperature AND DATEDIFF(a.recordDate,b.recordDate)=-1

607. 销售员

编写一个SQL查询，报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。
思路：

筛选到RED公司的com_id
筛选Orders表中com_id不等于RED的sales_id

SELECT name FROM SalesPerson
WHERE sales_id not in (SELECT sales_id FROM Orders WHERE com_id = (SELECT DISTINCT com_id FROM Company WHERE name = 'RED'))

1141. 查询近30天活跃用户数

查询出截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。

ADDDATE 向某日期加一段时间

ADDDATE(date, INTERVAL value addunit)
--OR
ADDDATE(date, days)

思路：

得到30天内日期
COUNT(DISTINCT user_id)
解法：

SELECT activity_date as day, COUNT(DISTINCT user_id) as active_users
FROM Activity
WHERE activity_date <= '2019-07-27' AND activity_date > ADDDATE('2019-07-27',-30)
GROUP BY day

1693. 每天的领导和合伙人

写一条 SQL 语句，使得对于每一个 date_id 和 make_name，返回不同的 lead_id 以及不同的 partner_id 的数量
解法

SELECT date_id, make_name, count(DISTINCT lead_id) as unique_leads, count(DISTINCT partner_id) as unique_partners 
FROM DailySales 
GROUP BY date_id, make_name

1729. 求关注者的数量

写出 SQL 语句，对于每一个用户，返回该用户的关注者数量。

SELECT user_id, count(DISTINCT follower_id) as followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id

586. 订单最多的客户

编写一个SQL查询，为下了最多订单的客户查找 customer_number

ORDER BY count(*)

即按表内值count的顺序排列，表中可以没有表内值count这一列
解法：

SELECT customer_number  
FROM Orders
GROUP BY customer_number
ORDER BY count(*) DESC
LIMIT 1

511. 游戏玩法分析 I

写一条 SQL 查询语句获取每位玩家第一次登陆平台的日期
要得到每个player_id对应的最小日期

min+group by

SELECT DISTINCT player_id, min(event_date) as first_login
FROM Activity
GROUP BY player_id

窗口函数：min() over (partition by …)

窗口函数自带

min(), count()…等function
partition by <用于分组的列名> 来分组
order by <用于排序的列名>) 来排序

SELECT DISTINCT player_id, min(event_date) over (partition by player_id) as first_login
FROM Activity

1890. 2020年最后一次登录

编写一个 SQL 查询，该查询可以获取在 2020 年登录过的所有用户的本年度最后一次登录时间。结果集不包含 2020 年没有登录过的用户
和上一题511有点像

窗口函数 max() + WHERE 时间限制

SELECT DISTINCT user_id, max(time_stamp) over (partition by user_id) as last_stamp
FROM Logins
WHERE time_stamp >= '2020-01-01' AND time_stamp < '2021-01-01'

MAX() + GROUP BY

SELECT DISTINCT user_id, max(time_stamp) as last_stamp
FROM Logins
WHERE time_stamp >= '2020-01-01' AND time_stamp < '2021-01-01'
GROUP BY user_id

1741. 查找每个员工花费的总时间

编写一个SQL查询以计算每位员工每天在办公室花费的总时间（以分钟为单位）。请注意，在一天之内，同一员工是可以多次进入和离开办公室的

group by能加两个筛选条件

SELECT DISTINCT event_day as day, emp_id, sum(out_time-in_time) as total_time 
FROM Employees
GROUP BY emp_id, event_day

1393. 股票的资本损益

编写一个SQL查询来报告每支股票的资本损益。

case when 判断条件+窗口函数

SELECT DISTINCT stock_name, 
                sum(CASE
                      WHEN operation ='Buy' then -price
                      else price
                      end ) over (partition by stock_name) as capital_gain_loss 
FROM Stocks

case when + group by

SELECT DISTINCT stock_name, 
                sum(CASE
                      WHEN operation ='Buy' then -price
                      else price
                      end )  as capital_gain_loss 
FROM Stocks
GROUP BY stock_name

1407. 排名靠前的旅行者

写一段 SQL , 报告每个用户的旅行距离。

两层ORDER
还要处理null问题

SELECT name, IFNULL(sum(distance),0) as travelled_distance 
FROM Users left join Rides on Users.id = Rides.user_id  
GROUP BY user_id
ORDER BY travelled_distance DESC, name ASC

1158. 市场分析 I

写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。

外连接时where和on的区别

外连接时要注意where和on的区别，

on是在连接构造临时表时执行的，不管on中条件是否成立都会返回主表（也就是left join左边的表）的内容，
where是在临时表形成后执行筛选作用的，不满足条件的整行都会被过滤掉。
如果这里用的是 where year(order_date)=‘2019’ 那么得到的结果将会把不满足条件的user_id为3，4的行给删掉。用on的话会保留user_id为3，4的行。

year(日期) 返回日期中的年份

解法：

先筛选了再left join

SELECT user_id as buyer_id, join_date, IFNULL(COUNT(item_id),0) as orders_in_2019 
FROM Users left join 
      (SELECT * FROM Orders WHERE order_date <'2020-01-01' AND order_date >= '2019-01-01') AS C
       on Users.user_id = C. buyer_id
GROUP BY user_id

两个on条件

left join时不用where，用两个on条件，确保左列表中的值能被保存到

SELECT user_id as buyer_id, join_date, IFNULL(COUNT(item_id),0) as orders_in_2019 
FROM Users left join Orders on Users.user_id = Orders.buyer_id AND year(order_date) ='2019'
GROUP BY user_id

182. 查找重复的电子邮箱

编写一个 SQL 查询来报告所有重复的电子邮件。请注意，可以保证电子邮件字段不为 NULL。

having函数

类似where，对结果做筛选

SELECT email as Email
FROM Person
group by Email
having count(*) >1

1050. 合作过至少三次的演员和导演

写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)
按演员和导演group by，再count

SELECT actor_id, director_id
FROM ActorDirector 
group by actor_id, director_id
having count(*)>=3

1587. 银行账户概要 II

写一个 SQL, 报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和.

SELECT name, sum(amount) as balance
FROM Users left join Transactions on Users.account = Transactions.account
GROUP BY name
having balance > 10000

1084. 销售分析III

编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品
因为两张表中都有product_id列，为避免ambiguous，需要指定是从哪一张表取的

SELECT Product.product_id as product_id, product_name
FROM Product left join Sales on Product.product_id = Sales.product_id 
GROUP BY product_id
having min(sale_date) >= '2019-01-01' AND max(sale_date) <= '2019-03-31'

注意：不能直接在left join时用where 筛选

SELECT Product.product_id as product_id, product_name
FROM Product left join Sales on Product.product_id = Sales.product_id WHERE sale_date >= '2019-01-01' AND sale_date <= '2019-03-31'   #这样不能确保全部都在这个区间，只要有一个在这个区间就能被选到
GROUP BY product_id
#having min(sale_date) > '2019-01-01' AND max(sale_date) <= '2019-03-31'