【力扣 | SQL题 | 每日4题】力扣2308，2324，2346，2372

4 mid，还是比较常规的。

1. 力扣2308：按性别排列表格

1.1 题目：

表: Genders

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| gender      | varchar |
+-------------+---------+
user_id 是该表的主键(具有唯一值的列)。
gender 的值是 'female', 'male','other' 之一。
该表中的每一行都包含用户的 ID 及其性别。
表格中 'female', 'male','other' 数量相等。
编写一个解决方案以重新排列 Genders 表，使行按顺序在 'female', 'other' 和 'male' 之间交替。同时每种性别按照 user_id 升序进行排序。
按 上述顺序 返回结果表。
返回结果格式如以下示例所示。

示例 1:

输入: 
Genders 表:
+---------+--------+
| user_id | gender |
+---------+--------+
| 4       | male   |
| 7       | female |
| 2       | other  |
| 5       | male   |
| 3       | female |
| 8       | male   |
| 6       | other  |
| 1       | other  |
| 9       | female |
+---------+--------+
输出: 
+---------+--------+
| user_id | gender |
+---------+--------+
| 3       | female |
| 1       | other  |
| 4       | male   |
| 7       | female |
| 2       | other  |
| 5       | male   |
| 9       | female |
| 6       | other  |
| 8       | male   |
+---------+--------+
解释: 
女性：ID 3、7、9。
其他性别：ID 1、2、6。
男性：ID 4、5、8。
我们在 'female', 'other','male' 之间交替排列表。
注意，每种性别都是按 user_id 升序排序的。

1.2 思路：

1. 给玩家开窗排名

2. 给性别优先级

1.3 题解：

with tep1 as (
    -- 以性别开窗，根据user_id排序给出排名
    select user_id,gender,rank() over (partition by gender order by user_id) rank2
    from Genders
), tep2 as (
    -- 然后赋给gender优先级（排名）
    select user_id,
    case when gender = 'female' then 3
    when gender = 'other' then 2
    else 1
    end as rank1
    ,rank2
    from tep1
)

select user_id, 
-- 记住要还原gender字段
case rank1 when 3 then 'female'
when 2 then 'other'
else 'male' end gender
from tep2
-- 先rank2升序排列，即让各窗口内排名第一的三个人先输出
-- 然后这三个人ran2排名一样，再让这三个人按照rank1的优先级再排序
order by rank2, rank1 desc

2. 力扣2324：产品销售分析4

2.1 题目：

表: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| user_id     | int   |
| quantity    | int   |
+-------------+-------+
sale_id 包含唯一值。
product_id 是 product 表的外键。
该表的每一行都显示了产品的 ID 和用户购买的数量。

表: Product

+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id  | int  |
| price       | int  |
+-------------+------+
product_id 包含唯一值。
该表的每一行都表示每种产品的价格。

编写解决方案，为每个用户获取其消费最多的产品 id。如果同一用户在两个或多个产品上花费了最多的钱，请获取所有花费了最多的钱的产品。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Sales 表:
+---------+------------+---------+----------+
| sale_id | product_id | user_id | quantity |
+---------+------------+---------+----------+
| 1       | 1          | 101     | 10       |
| 2       | 3          | 101     | 7        |
| 3       | 1          | 102     | 9        |
| 4       | 2          | 102     | 6        |
| 5       | 3          | 102     | 10       |
| 6       | 1          | 102     | 6        |
+---------+------------+---------+----------+
Product 表:
+------------+-------+
| product_id | price |
+------------+-------+
| 1          | 10    |
| 2          | 25    |
| 3          | 15    |
+------------+-------+
输出: 
+---------+------------+
| user_id | product_id |
+---------+------------+
| 101     | 3          |
| 102     | 1          |
| 102     | 2          |
| 102     | 3          |
+---------+------------+ 
解释: 
用户 101:
    - 在产品 1 上花费 10 * 10 = 100。
    - 在产品 3 上花费 7 * 15 = 105。
用户101在产品3上花的钱最多。
用户 102:
    - 在产品 1 上花费 (9 + 6)* 10 = 150
    - 在产品 2 上花费 6 * 25 = 150
    - 在产品 3 上花费 10 * 15 = 150。
用户 102 在产品 1、2、3 上花的钱最多。

2.2 思路：

看注释。

2.3 题解：

-- 以user_id和product_id分组，并计算组内的消费总和
with tep as (
    select user_id, sum(quantity*price) consume, s.product_id 
    from Sales s
    join Product p 
    on s.product_id = p.product_id
    group by user_id, s.product_id 
)

-- 判断该记录的consume值是否在该用户其他产品消费下是最多的。
select user_id, product_id 
from tep t1 
where consume >= all(
    select max(consume)
    from tep t2
    where t1.user_id = t2.user_id
)

3. 力扣2346：以百分比计算排名

3.1 题目：

表: Students

+---------------+------+
| Column Name   | Type |
+---------------+------+
| student_id    | int  |
| department_id | int  |
| mark          | int  |
+---------------+------+
student_id 包含唯一值。
该表的每一行都表示一个学生的 ID，该学生就读的院系 ID，以及他们的考试分数。

编写一个解决方案，以百分比的形式报告每个学生在其部门的排名，其中排名的百分比使用以下公式计算:

(student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1)。 percentage 应该 四舍五入到小数点后两位。 

student_rank_in_the_department 由 mark 的降序决定，mark 最高的学生是  rank 1。如果两个学生得到相同的分数，他们也会得到相同的排名。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1:

输入: 
Students 表:
+------------+---------------+------+
| student_id | department_id | mark |
+------------+---------------+------+
| 2          | 2             | 650  |
| 8          | 2             | 650  |
| 7          | 1             | 920  |
| 1          | 1             | 610  |
| 3          | 1             | 530  |
+------------+---------------+------+
输出: 
+------------+---------------+------------+
| student_id | department_id | percentage |
+------------+---------------+------------+
| 7          | 1             | 0.0        |
| 1          | 1             | 50.0       |
| 3          | 1             | 100.0      |
| 2          | 2             | 0.0        |
| 8          | 2             | 0.0        |
+------------+---------------+------------+
解释: 
对于院系 1:
 - 学生 7:percentage = (1 - 1)* 100 / (3 - 1) = 0.0
 - 学生 1:percentage = (2 - 1)* 100 / (3 - 1) = 50.0
 - 学生 3:percentage = (3 - 1)* 100 / (3 - 1) = 100.0
对于院系 2:
 - 学生 2: percentage = (1 - 1) * 100 / (2 - 1) = 0.0
 - 学生 8: percentage = (1 - 1) * 100 / (2 - 1) = 0.0

3.2 思路：

开窗给出排名，然后需要注意该学生部门学生人数为1的时候，1-1=0，则相当于a/0=>null，所以外面包一个ifNull函数。

3.3 题解：

-- 题目说了要给每个部门内学生的排名，那就开呗
with tep1 as (
    select student_id,department_id , rank() over (partition by department_id order by mark desc) ranks
    from Students
)

select student_id , department_id,
-- 这里为什么会用到ifNull呢？因为当子查询查询该学生部门学生人数为1时，1-1=0
-- 则会返回null
round(ifNull((ranks-1)*100/((select count(*) from tep1 t2 where t1.department_id = t2.department_id)-1), 0), 2) percentage
from tep1 t1

4. 力扣2372：计算每个销售人员的影响力

4.1 题目：

表： Salesperson

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| salesperson_id | int     |
| name           | varchar |
+----------------+---------+
sales_person_id 包含唯一值。
这个表中的每一行都显示一个销售人员的 ID。

表：Customer

+----------------+------+
| Column Name    | Type |
+----------------+------+
| customer_id    | int  |
| salesperson_id | int  |
+----------------+------+
customer_id 包含唯一值。
salesperson_id 是一个来自于 Salesperson 表的外键
Customer 表中的每一行都显示了一个客户的 ID 和销售人员的 ID。

表：Sales

+-------------+------+
| Column Name | Type |
+-------------+------+
| sale_id     | int  |
| customer_id | int  |
| price       | int  |
+-------------+------+
sale_id 包含唯一值。
customer_id 是一个来自于 Customer 表的外键。
Sales 表中的每一行都显示了一个客户的 ID 以及他们在 sale_id 指代的交易中所支付的金额。

编写解决方案，报告每个销售人员的客户所支付的价格总和。如果销售人员没有任何客户，则总值应该为 0 。
以 任意顺序 返回结果表。
结果格式如下所示。

示例 1:

输入: 
Salesperson 表:
+----------------+-------+
| salesperson_id | name  |
+----------------+-------+
| 1              | Alice |
| 2              | Bob   |
| 3              | Jerry |
+----------------+-------+
Customer 表:
+-------------+----------------+
| customer_id | salesperson_id |
+-------------+----------------+
| 1           | 1              |
| 2           | 1              |
| 3           | 2              |
+-------------+----------------+
Sales 表:
+---------+-------------+-------+
| sale_id | customer_id | price |
+---------+-------------+-------+
| 1       | 2           | 892   |
| 2       | 1           | 354   |
| 3       | 3           | 988   |
| 4       | 3           | 856   |
+---------+-------------+-------+
输出: 
+----------------+-------+-------+
| salesperson_id | name  | total |
+----------------+-------+-------+
| 1              | Alice | 1246  |
| 2              | Bob   | 1844  |
| 3              | Jerry | 0     |
+----------------+-------+-------+
解释: 
Alice 是客户 1 和客户 2 的销售人员。
  - 客户 1 一次购买花费了 354。
  - 客户 2 一次购买花费了 892。
Alice 的总数是 354 + 892 = 1246。

Bob 是客户 3 的销售人员。
  - 客户 3 一次购买花费了 988，另一次购买花费了 856。
Bob 的总数是 988 + 856 = 1844。

Jerry 没有客户。
Jerry 的总数是 0。

4.2 思路：

多表连接，先内连接计算price总和，再左外连接保证每个销售人员都在。

4.3 题解：

-- 将后两张表连接，并salesperson_id分组计算price总和
with tep as (
    select salesperson_id , sum(price) total 
    from Customer t1
    join Sales t2
    on t1.customer_id = t2.customer_id 
    group by salesperson_id 
)
-- 要显示所有的销售人员，所以使用左外连接，如果total为null，即
-- 销售人员无任何客户，该销售人员在tep表无任何记录。
select t1.salesperson_id , name, ifNull(total, 0) total
from Salesperson t1
left join tep t2
on t1.salesperson_id = t2.salesperson_id 

原文地址：https://blog.csdn.net/2301_80912559/article/details/142989779

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！