一、有趣的电影
（1）题目
表：cinema
±---------------±---------+
| Column Name | Type |
±---------------±---------+
| id | int |
| movie | varchar |
| description | varchar |
| rating | float |
±---------------±---------+
id 是该表的主键(具有唯一值的列)。
每行包含有关电影名称、类型和评级的信息。
评级为 [0,10] 范围内的小数点后 2 位浮点数。
编写解决方案，找出所有影片描述为 非 boring (不无聊) 的并且 id 为奇数 的影片。
返回结果按 rating 降序排列。
结果格式如下示例。
示例 1：
输入：
±--------±----------±-------------±----------+
| id | movie | description | rating |
±--------±----------±-------------±----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
±--------±----------±-------------±----------+
输出：
±--------±----------±-------------±----------+
| id | movie | description | rating |
±--------±----------±-------------±----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
±--------±----------±-------------±----------+
解释：
我们有三部电影，它们的 id 是奇数:1、3 和 5。id = 3 的电影是 boring 的，所以我们不把它包括在答案中。
（2）题解
select * from cinema where mod(id,2)=1 and description not like 'boring' order by rating desc
（3）注意点
1）mod(id,2)
①MOD函数用于返回N除以M后的余数
②求余数可以用%替代
③一般用于判断奇偶
MOD(id,2) = 1 id是奇数
MOD(id,2) = 0 id是偶数
2）not like
①表示不包含，本题中直接使用不等于也可以实现，看到别人总结一把评论为一句话，所以使用包含
3）order by desc
①表示降序
二、平均售价
（1）题目
表：Prices
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| product_id | int |
| start_date | date |
| end_date | date |
| price | int |
±--------------±--------+
(product_id，start_date，end_date) 是 prices 表的主键（具有唯一值的列的组合）。
prices 表的每一行表示的是某个产品在一段时期内的价格。
每个产品的对应时间段是不会重叠的，这也意味着同一个产品的价格时段不会出现交叉。
表：UnitsSold
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| product_id | int |
| purchase_date | date |
| units | int |
±--------------±--------+
该表可能包含重复数据。
该表的每一行表示的是每种产品的出售日期，单位和产品 id。
编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。
返回结果表 无顺序要求 。
结果格式如下例所示。
示例 1：
输入：
Prices table:
±-----------±-----------±-----------±-------+
| product_id | start_date | end_date | price |
±-----------±-----------±-----------±-------+
| 1 | 2019-02-17 | 2019-02-28 | 5 |
| 1 | 2019-03-01 | 2019-03-22 | 20 |
| 2 | 2019-02-01 | 2019-02-20 | 15 |
| 2 | 2019-02-21 | 2019-03-31 | 30 |
±-----------±-----------±-----------±-------+
UnitsSold table:
±-----------±--------------±------+
| product_id | purchase_date | units |
±-----------±--------------±------+
| 1 | 2019-02-25 | 100 |
| 1 | 2019-03-01 | 15 |
| 2 | 2019-02-10 | 200 |
| 2 | 2019-03-22 | 30 |
±-----------±--------------±------+
输出：
±-----------±--------------+
| product_id | average_price |
±-----------±--------------+
| 1 | 6.96 |
| 2 | 16.96 |
±-----------±--------------+
解释：
平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96
（2）题解
select p.product_id, ifnull(round(sum(price*units)/sum(units),2),0) as average_price
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 product_id;
（3）注意点
1）ifnull 用于排除分母非空的情况
2）sum 计算总数
3）round 四舍五入保留两位
4）left join 左链接对两个表进行查询
5）between and 筛选日期范围
三、项目员工
（1）题目
项目表 Project：
±------------±--------+
| Column Name | Type |
±------------±--------+
| project_id | int |
| employee_id | int |
±------------±--------+
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
员工表 Employee：
±-----------------±--------+
| Column Name | Type |
±-----------------±--------+
| employee_id | int |
| name | varchar |
| experience_years | int |
±-----------------±--------+
主键是 employee_id。
请写一个 SQL 语句，查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。
查询结果的格式如下：
Project 表：
±------------±------------+
| project_id | employee_id |
±------------±------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
±------------±------------+
Employee 表：
±------------±-------±-----------------+
| employee_id | name | experience_years |
±------------±-------±-----------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
±------------±-------±-----------------+
Result 表：
±------------±--------------+
| project_id | average_years |
±------------±--------------+
| 1 | 2.00 |
| 2 | 2.50 |
±------------±--------------+
第一个项目中，员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00；第二个项目中，员工的平均工作年限是 (3 + 2) / 2 = 2.50
（2）题解
select project_id , ifnull(round(sum(e.experience_years)/count(p.employee_id),2),0) as average_years
from Project p left join Employee e
on p.employee_id = e.employee_id
group by p.project_id
（3）注意点
group by 分组后，sum() / count() 计算平均值。用round()函数取两位小数,ifnull 保证不为空。
四、各赛事的用户注册率
（1）题目
用户表： Users
±------------±--------+
| Column Name | Type |
±------------±--------+
| user_id | int |
| user_name | varchar |
±------------±--------+
user_id 是该表的主键(具有唯一值的列)。
该表中的每行包括用户 ID 和用户名。
注册表： Register
±------------±--------+
| Column Name | Type |
±------------±--------+
| contest_id | int |
| user_id | int |
±------------±--------+
(contest_id, user_id) 是该表的主键(具有唯一值的列的组合)。
该表中的每行包含用户的 ID 和他们注册的赛事。
编写解决方案统计出各赛事的用户注册百分率，保留两位小数。
返回的结果表按 percentage 的 降序 排序，若相同则按 contest_id 的 升序 排序。
返回结果如下示例所示。
示例 1：
输入：
Users 表：
±--------±----------+
| user_id | user_name |
±--------±----------+
| 6 | Alice |
| 2 | Bob |
| 7 | Alex |
±--------±----------+
Register 表：
±-----------±--------+
| contest_id | user_id |
±-----------±--------+
| 215 | 6 |
| 209 | 2 |
| 208 | 2 |
| 210 | 6 |
| 208 | 6 |
| 209 | 7 |
| 209 | 6 |
| 215 | 7 |
| 208 | 7 |
| 210 | 2 |
| 207 | 2 |
| 210 | 7 |
±-----------±--------+
输出：
±-----------±-----------+
| contest_id | percentage |
±-----------±-----------+
| 208 | 100.0 |
| 209 | 100.0 |
| 210 | 100.0 |
| 215 | 66.67 |
| 207 | 33.33 |
±-----------±-----------+
解释：
所有用户都注册了 208、209 和 210 赛事，因此这些赛事的注册率为 100% ，我们按 contest_id 的降序排序加入结果表中。
Alice 和 Alex 注册了 215 赛事，注册率为 ((2/3) * 100) = 66.67%
Bob 注册了 207 赛事，注册率为 ((1/3) * 100) = 33.33%
（2）题解
select contest_id,round(100*count(user_id)/(select count(*) from Users),2) as percentage
from Register r
group by r.contest_id
order by 2 desc, 1 asc;
（3）注意点
主要注意嵌套，然后理清关系
Regist表中每个赛事注册用户的数量count(user_id)
User表总数量select count(*) from Users
保留两位小数round
五、查询结果的质量和占比
（1）题目
Queries 表：
±------------±--------+
| Column Name | Type |
±------------±--------+
| query_name | varchar |
| result | varchar |
| position | int |
| rating | int |
±------------±--------+
此表可能有重复的行。
此表包含了一些从数据库中收集的查询信息。
“位置”（position）列的值为 1 到 500 。
“评分”（rating）列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询。
将查询结果的质量 quality 定义为：
各查询结果的评分与其位置之间比率的平均值。
将劣质查询百分比 poor_query_percentage 为：
评分小于 3 的查询结果占全部查询结果的百分比。
编写解决方案，找出每次的 query_name 、 quality 和 poor_query_percentage。
quality 和 poor_query_percentage 都应 四舍五入到小数点后两位 。
以 任意顺序 返回结果表。
结果格式如下所示：
示例 1：
输入：
Queries table:
±-----------±------------------±---------±-------+
| query_name | result | position | rating |
±-----------±------------------±---------±-------+
| Dog | Golden Retriever | 1 | 5 |
| Dog | German Shepherd | 2 | 5 |
| Dog | Mule | 200 | 1 |
| Cat | Shirazi | 5 | 2 |
| Cat | Siamese | 3 | 3 |
| Cat | Sphynx | 7 | 4 |
±-----------±------------------±---------±-------+
输出：
±-----------±--------±----------------------+
| query_name | quality | poor_query_percentage |
±-----------±--------±----------------------+
| Dog | 2.50 | 33.33 |
| Cat | 0.66 | 33.33 |
±-----------±--------±----------------------+
解释：
Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33
Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33
（2）题解
select query_name, round(avg(rating/position),2) as quality,round(SUM(IF(rating < 3, 1, 0)) * 100 / COUNT(*),2) as poor_query_percentage
from Queries
group by query_name
（3）注意点
1）SUM()：返回某列的和。
2）AVG()：返回某列的平均值。
3）COUNT() ：返回某列的行数。
扫一扫
4356
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
