1164.指定日期的产品价格
产品数据表:
Products+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | new_price | int | | change_date | date | +---------------+---------+ (product_id, change_date) 是此表的主键（具有唯一值的列组合）。 这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。编写一个解决方案，找出在
2019-08-16时全部产品的价格，假设所有产品在修改前的价格都是10。以 任意顺序 返回结果表。
结果格式如下例所示。
示例 1:
输入： Products 表: +------------+-----------+-------------+ | product_id | new_price | change_date | +------------+-----------+-------------+ | 1 | 20 | 2019-08-14 | | 2 | 50 | 2019-08-14 | | 1 | 30 | 2019-08-15 | | 1 | 35 | 2019-08-16 | | 2 | 65 | 2019-08-17 | | 3 | 20 | 2019-08-18 | +------------+-----------+-------------+ 输出： +------------+-------+ | product_id | price | +------------+-------+ | 2 | 50 | | 1 | 35 | | 3 | 10 | +------------+-------+
思路：首先找到修改日期小于’2019-08-16‘并且最接近这个日期的修改项，得到产品id和最接近的日期就可以取出这个产品最后的价格。然后利用左连接的特点，可以先将所有不重复的产品名称查找出来再去左连接，那些超出时间的就会显示null，再利用ifnull将他们改为10就能解除本题
select name.product_id,ifnull(new_price,10) as price from
(select distinct product_id from products) as name left join
(select product_id,new_price from
products where (product_id,change_date) in
(select product_id,max(change_date) from products
where change_date <= '2019-08-16'
group by product_id)) as rk on name.product_id = rk.product_id1174.即时食物配送Ⅱ
配送表:
Delivery+-----------------------------+---------+ | Column Name | Type | +-----------------------------+---------+ | delivery_id | int | | customer_id | int | | order_date | date | | customer_pref_delivery_date | date | +-----------------------------+---------+ delivery_id 是该表中具有唯一值的列。 该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。
「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。
编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。
结果示例如下所示：
示例 1：
输入： Delivery 表： +-------------+-------------+------------+-----------------------------+ | delivery_id | customer_id | order_date | customer_pref_delivery_date | +-------------+-------------+------------+-----------------------------+ | 1 | 1 | 2019-08-01 | 2019-08-02 | | 2 | 2 | 2019-08-02 | 2019-08-02 | | 3 | 1 | 2019-08-11 | 2019-08-12 | | 4 | 3 | 2019-08-24 | 2019-08-24 | | 5 | 3 | 2019-08-21 | 2019-08-22 | | 6 | 2 | 2019-08-11 | 2019-08-13 | | 7 | 4 | 2019-08-09 | 2019-08-09 | +-------------+-------------+------------+-----------------------------+ 输出： +----------------------+ | immediate_percentage | +----------------------+ | 50.00 | +----------------------+ 解释： 1 号顾客的 1 号订单是首次订单，并且是计划订单。 2 号顾客的 2 号订单是首次订单，并且是即时订单。 3 号顾客的 5 号订单是首次订单，并且是计划订单。 4 号顾客的 7 号订单是首次订单，并且是即时订单。 因此，一半顾客的首次订单是即时的。
思路：和上一题差不多，先把首次日期求出来，也就是每个用户下单时间最小的那一个，再去里面查找。
select round(avg(if(order_date=customer_pref_delivery_date,1,0))*100,2) as immediate_percentage
from delivery
where (customer_id,order_date) in
(select customer_id,min(order_date) as order_date
from delivery
group by customer_id
)1179.重新格式化部门表
表
Department：+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | revenue | int | | month | varchar | +---------------+---------+ 在 SQL 中，(id, month) 是表的联合主键。 这个表格有关于每个部门每月收入的信息。 月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。重新格式化表格，使得 每个月 都有一个部门 id 列和一个收入列。
以 任意顺序 返回结果表。
结果格式如以下示例所示。
示例 1：
输入： Department table: +------+---------+-------+ | id | revenue | month | +------+---------+-------+ | 1 | 8000 | Jan | | 2 | 9000 | Jan | | 3 | 10000 | Feb | | 1 | 7000 | Feb | | 1 | 6000 | Mar | +------+---------+-------+ 输出： +------+-------------+-------------+-------------+-----+-------------+ | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue | +------+-------------+-------------+-------------+-----+-------------+ | 1 | 8000 | 7000 | 6000 | ... | null | | 2 | 9000 | null | null | ... | null | | 3 | null | 10000 | null | ... | null | +------+-------------+-------------+-------------+-----+-------------+ 解释：四月到十二月的收入为空。 请注意，结果表共有 13 列（1 列用于部门 ID，其余 12 列用于各个月份）。
简单题，就是比较麻烦
select id,
sum(case month when 'Jan' then revenue end) as Jan_Revenue,
sum(case month when 'Feb' then revenue end) as Feb_Revenue,
sum(case month when 'Mar' then revenue end) as Mar_Revenue,
sum(case month when 'Apr' then revenue end) as Apr_Revenue,
sum(case month when 'May' then revenue end) as May_Revenue,
sum(case month when 'Jun' then revenue end) as Jun_Revenue,
sum(case month when 'Jul' then revenue end) as Jul_Revenue,
sum(case month when 'Aug' then revenue end) as Aug_Revenue,
sum(case month when 'Sep' then revenue end) as Sep_Revenue,
sum(case month when 'Oct' then revenue end) as Oct_Revenue,
sum(case month when 'Nov' then revenue end) as Nov_Revenue,
sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id1193.每月交易 I
表：
Transactions+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | country | varchar | | state | enum | | amount | int | | trans_date | date | +---------------+---------+ id 是这个表的主键。 该表包含有关传入事务的信息。 state 列类型为 ["approved", "declined"] 之一。编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入： Transactionstable: +------+---------+----------+--------+------------+ | id | country | state | amount | trans_date | +------+---------+----------+--------+------------+ | 121 | US | approved | 1000 | 2018-12-18 | | 122 | US | declined | 2000 | 2018-12-19 | | 123 | US | approved | 2000 | 2019-01-01 | | 124 | DE | approved | 2000 | 2019-01-07 | +------+---------+----------+--------+------------+ 输出： +----------+---------+-------------+----------------+--------------------+-----------------------+ | month | country | trans_count | approved_count | trans_total_amount | approved_total_amount | +----------+---------+-------------+----------------+--------------------+-----------------------+ | 2018-12 | US | 2 | 1 | 3000 | 1000 | | 2019-01 | US | 1 | 1 | 2000 | 2000 | | 2019-01 | DE | 1 | 1 | 2000 | 2000 | +----------+---------+-------------+----------------+--------------------+-----------------------+
简单题，这题只是要素过多，慢慢写即可
select distinct date_format(trans_date,'%Y-%m') as month,country,count(*) as trans_count,
sum(if(state = 'approved',1,0)) as approved_count,sum(amount) as trans_total_amount,
sum(if(state = 'approved',amount,0)) as approved_total_amount
from transactions group by country,month1204.最后一个能进入巴士的人
表:
Queue+-------------+---------+ | Column Name | Type | +-------------+---------+ | person_id | int | | person_name | varchar | | weight | int | | turn | int | +-------------+---------+ person_id 是这个表具有唯一值的列。 该表展示了所有候车乘客的信息。 表中 person_id 和 turn 列将包含从 1 到 n 的所有数字，其中 n 是表中的行数。 turn 决定了候车乘客上巴士的顺序，其中 turn=1 表示第一个上巴士，turn=n 表示最后一个上巴士。 weight 表示候车乘客的体重，以千克为单位。有一队乘客在等着上巴士。然而，巴士有
1000千克 的重量限制，所以其中一部分乘客可能无法上巴士。编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告
person_name。题目测试用例确保顺位第一的人可以上巴士且不会超重。返回结果格式如下所示。
示例 1：
输入： Queue 表 +-----------+-------------+--------+------+ | person_id | person_name | weight | turn | +-----------+-------------+--------+------+ | 5 | Alice | 250 | 1 | | 4 | Bob | 175 | 5 | | 3 | Alex | 350 | 2 | | 6 | John Cena | 400 | 3 | | 1 | Winston | 500 | 6 | | 2 | Marie | 200 | 4 | +-----------+-------------+--------+------+ 输出： +-------------+ | person_name | +-------------+ | John Cena | +-------------+ 解释： 为了简化，Queue 表按 turn 列由小到大排序。 +------+----+-----------+--------+--------------+ | Turn | ID | Name | Weight | Total Weight | +------+----+-----------+--------+--------------+ | 1 | 5 | Alice | 250 | 250 | | 2 | 3 | Alex | 350 | 600 | | 3 | 6 | John Cena | 400 | 1000 | (最后一个上巴士) | 4 | 2 | Marie | 200 | 1200 | (无法上巴士) | 5 | 4 | Bob | 175 | ___ | | 6 | 1 | Winston | 500 | ___ | +------+----+-----------+--------+--------------+
select person_name
from (
select *,sum(weight) over(order by turn) as sumWeight
from Queue
) as t
where sumWeight <= 1000
order by sumWeight desc
limit 11211.查询结果的质量和占比
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
select query_name,round(avg(rating/position),2) as quality,
round(sum(if(rating>=3,0,1))/count(rating)*100,2) as poor_query_percentage
from queries
group by query_name
having query_name is not null1251.平均售价
表：
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
select product_id,ifnull(round(sum(sum1)/sum(units),2),0) as average_price
from
(select prices.product_id as product_id,price*units as sum1,units
from prices
left join unitssold on prices.product_id = unitssold.product_id
and unitssold.purchase_date between start_date and end_date)
as a group by product_id1280.学生们参加各科测试的次数
学生表:
Students+---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | student_name | varchar | +---------------+---------+ 在 SQL 中，主键为 student_id（学生ID）。 该表内的每一行都记录有学校一名学生的信息。科目表:
Subjects+--------------+---------+ | Column Name | Type | +--------------+---------+ | subject_name | varchar | +--------------+---------+ 在 SQL 中，主键为 subject_name（科目名称）。 每一行记录学校的一门科目名称。考试表:
Examinations+--------------+---------+ | Column Name | Type | +--------------+---------+ | student_id | int | | subject_name | varchar | +--------------+---------+ 这个表可能包含重复数据（换句话说，在 SQL 中，这个表没有主键）。 学生表里的一个学生修读科目表里的每一门科目。 这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。查询出每个学生参加每一门科目测试的次数，结果按
student_id和subject_name排序。查询结构格式如下所示。
示例 1：
输入： Students table: +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Alice | | 2 | Bob | | 13 | John | | 6 | Alex | +------------+--------------+ Subjects table: +--------------+ | subject_name | +--------------+ | Math | | Physics | | Programming | +--------------+ Examinations table: +------------+--------------+ | student_id | subject_name | +------------+--------------+ | 1 | Math | | 1 | Physics | | 1 | Programming | | 2 | Programming | | 1 | Physics | | 1 | Math | | 13 | Math | | 13 | Programming | | 13 | Physics | | 2 | Math | | 1 | Math | +------------+--------------+ 输出： +------------+--------------+--------------+----------------+ | student_id | student_name | subject_name | attended_exams | +------------+--------------+--------------+----------------+ | 1 | Alice | Math | 3 | | 1 | Alice | Physics | 2 | | 1 | Alice | Programming | 1 | | 2 | Bob | Math | 1 | | 2 | Bob | Physics | 0 | | 2 | Bob | Programming | 1 | | 6 | Alex | Math | 0 | | 6 | Alex | Physics | 0 | | 6 | Alex | Programming | 0 | | 13 | John | Math | 1 | | 13 | John | Physics | 1 | | 13 | John | Programming | 1 | +------------+--------------+--------------+----------------+ 解释： 结果表需包含所有学生和所有科目（即便测试次数为0）： Alice 参加了 3 次数学测试, 2 次物理测试，以及 1 次编程测试； Bob 参加了 1 次数学测试, 1 次编程测试，没有参加物理测试； Alex 啥测试都没参加； John 参加了数学、物理、编程测试各 1 次。
select students.student_id,students.student_name,subjects.subject_name,ifnull(exam.attended_exams,0) as attended_exams from students
cross join subjects
left join
(
select student_id,subject_name,count(*) as attended_exams from Examinations group by student_id,subject_name
) as exam on students.student_id = exam.student_id and subjects.subject_name = exam.subject_name
order by students.student_id,subjects.subject_name
扫一扫
93
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
