力扣sql中等篇练习(二十八)

力扣sql中等篇练习(二十八)

1 每个城市最高气温的第一天

1.1 题目内容

1.1.1 基本题目信息

1.1.2 示例输入输出

1.2 示例sql语句

# Write your MySQL query statement below
SELECT w.city_id,MIN(w.day) day,w.degree
FROM Weather w
INNER JOIN
(
    SELECT city_id,MAX(degree) maxDegree
    FROM Weather
    GROUP BY city_id
)t
ON w.city_id=t.city_id AND w.degree=t.maxDegree
GROUP BY w.city_id
ORDER BY w.city_id asc

1.3 运行截图

2 产品销量分析IV

2.1 题目内容

2.1.1 基本题目信息1

2.1.2 基本题目信息2

2.1.3 示例输入输出

a 示例输入

b 示例输出

2.2 示例sql语句

# 同一个人可以买同一产品多次
WITH T as
(
    SELECT s.product_id,s.user_id,SUM(s.quantity*p.price) s_price
    FROM Sales s
    INNER JOIN Product p 
    ON s.product_id=p.product_id
    GROUP BY s.user_id,s.product_id
)
SELECT t1.user_id,t1.product_id
FROM T t1
INNER JOIN
(
    SELECT user_id,MAX(s_price) max_price
    FROM T
    GROUP BY user_id
)t2
ON t1.user_id=t2.user_id AND t1.s_price=t2.max_price

2.3 运行截图

3 以百分比计算排名

3.1 题目内容

3.1.1 基本题目信息

3.1.2 示例输入输出

3.2 示例sql语句

# 注意看院系2的两个学生排名 是相同是一个名词的
SELECT s.student_id,s.department_id,
ROUND(
    IFNULL(
        (rank() over(PARTITION BY s.department_id ORDER BY s.mark desc)-1)*100
        /
        (
        SELECT count(s1.student_id)-1
        FROM Students s1
        WHERE s1.department_id=s.department_id
        ),0
    ),2
) percentage 
FROM Students s

3.3 运行截图