高频SQL50题 第四天 | 1251. 平均售价、620. 有趣的电影、1075. 项目员工 I、1633. 各赛事的用户注册率

知识点导览：日期大小比较；ifnull(字段，默认值)函数；取余操作；字符串比较like；逆序desc

1251. 平均售价

题目链接：https://leetcode.cn/problems/average-selling-price/description/?envType=study-plan-v2&envId=sql-free-50
状态：已完成

考点：

• 判断日期是否位于某个区间内，可以使用$>=$和$<=$计算
• $ifnull(字段，默认值)$可以将字段中的null替换为默认值

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

620. 有趣的电影

题目链接：https://leetcode.cn/problems/not-boring-movies/?envType=study-plan-v2&envId=sql-free-50
状态：已完成

考点：

• 取余操作 %
• 字符串比较操作 like，not like
• 逆序排序 order by XXX desc

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

1075. 项目员工 I

题目链接：https://leetcode.cn/problems/project-employees-i/?envType=study-plan-v2&envId=sql-free-50
状态：已完成

考点：无，均为重复考点

select project_id, round(sum(experience_years) / count(*), 2) as average_years
from Project join Employee
on Project.employee_id = Employee.employee_id
group by project_id

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

题目链接：https://leetcode.cn/problems/percentage-of-users-attended-a-contest/description/?envType=study-plan-v2&envId=sql-free-50
状态：需二刷，想了好久怎么通过连接解决，最后还是没想到

考点：子查询获取用户总数，作为新的一列num，通过连接操作加入Register表中

select contest_id, round(count(*) / num * 100, 2) as percentage
from Register join (select count(*) as num from Users) as Table1
group by contest_id
order by percentage desc, contest_id