sql基础day02

声明：题目均来自LeetCode官网
链接：https://leetcode.cn

1、1193. 每月交易 I

题目

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

以 任意顺序 返回结果表。

题解：

select substring(trans_date, 1, 7)                                 month,
       country,
       count(1)                                                 as trans_count,
       sum(case when state = 'approved' then 1 else 0 end)      as approved_count,
       sum(amount)                                              as trans_total_amount,
       sum(case when state = 'approved' then amount else 0 end) as "approved_total_amount"
from transactions
group by substring(trans_date, 1, 7), country

2、1633. 各赛事的用户注册率

写一条 SQL 语句，查询各赛事的用户注册百分率，保留两位小数。

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

题解：

select contest_id,
       round(count(u.user_id) / (select count(user_id) from users) * 100, 2) as percentage
from register
         left join users u on register.user_id = u.user_id
group by contest_id
order by percentage desc, contest_id

3 、1173. 即时食物配送 I

如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。

写一条 SQL 查询语句获取即时订单所占的百分比， 保留两位小数。

题解：

select round((select count(1)
        from delivery
        where order_date = customer_pref_delivery_date) / count(1) * 100,2)  as immediate_percentage
from delivery

select
    round(avg(order_date=customer_pref_delivery_date)*100,2) immediate_percentage 
from
    Delivery;

4、1211. 查询结果的质量和占比

将查询结果的质量 quality 定义为：

各查询结果的评分与其位置之间比率的平均值。

将劣质查询百分比 poor_query_percentage 为：

评分小于 3 的查询结果占全部查询结果的百分比。

编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。

质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/queries-quality-and-percentage

题解：

select query_name,
       round(sum(rating / queries.position) / count(*), 2)  as quality,
       round(sum(if(rating < 3, 1, 0)) / count(*) * 100, 2) as poor_query_percentage
from queries
group by query_name;

5、分数排名

编写 SQL 查询对分数进行排序。排名按以下规则计算:

分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/rank-scores

解题思路

要获取当前分数的排名，就需要知道比当前分数大的分数总共有几个（注意要去重）

select s1.score,
         (
                 select count(distinct s2.score)
                 from scores s2
                 where s1.score <= s2.score
                 group by s1.score
             )as 'Rank'
from scores s1
order by s1.score desc

select score, dense_rank() over (order by  score desc) 'Rank' FROM scores;

6 184. 部门工资最高的员工

编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。

解题思路

可以先找到各个部门的最高工资，然后将这条查询作为子查询，在查表中有哪些数据的部门和工资在这里范围内

题解

select d.name Department, e.name Employee, salary
from employee e,
     department d
where e.departmentId = d.id
  and (e.salary, e.departmentId) in (select max(salary), departmentId from employee group by departmentId)