2308,2314,2324,2329,2339,2346

---

---

2308. Arrange Table by Gender

# Write your MySQL query statement below
select user_id,gender
from(
select* ,
#对相同性别按照id升序进行排名
row_number()over(partition by gender order by user_id) same_rk,
case
    when gender ='female' then 1
    when gender = 'male' then 3
    else  2
    end diff_rk
from Genders)new_table
order by same_rk,diff_rk
1
2
3
4
5
6
7
8
9
10
11
12
13

2314. The First Day of the Maximum Recorded Degree in Each City

with new_table as(
select city_id,day,degree,
    rank()over(partition by city_id order by degree desc, day asc) rk
from Weather)

select city_id,day,degree
from new_table
where rk = 1
order by city_id 
1
2
3
4
5
6
7
8
9

---

2324. Product Sales Analysis IV

# Write your MySQL query statement below
with new_tale as(
    select user_id ,S.product_id ,
    RANK()OVER(PARTITION BY user_id order by  sum(quantity*price) desc)rk
    from Sales S left join Product P 
    on S.product_id = P.product_id
    group by S.user_id ,S.product_id
)
SELECT user_id ,product_id
from new_tale
where rk = 1
1
2
3
4
5
6
7
8
9
10
11

2329. 产品销售分析Ⅴ

# Write your MySQL query statement below
# Write your MySQL query statement below
with new_tale as(
    select user_id ,S.product_id ,sum(quantity*price) spending
    from Sales S left join Product P 
    on S.product_id = P.product_id
    group by user_id
)
SELECT user_id ,spending
from new_tale
order by spending desc ,1
1
2
3
4
5
6
7
8
9
10
11

2339. All the Matches of the League

# Write your MySQL query statement below
select t1.team_name home_team ,t2.team_name away_team
from Teams t1,Teams t2
where t1.team_name <>t2.team_name
1
2
3
4

2346. Compute the Rank as a Percentage

# Write your MySQL query statement below
select student_id,department_id,
       ifnull(round((((rank()
over(partition by department_id order by mark desc))-1)*100)
       /(count(*)over(partition by department_id)-1
       ),2),0) percentage
from students
1
2
3
4
5
6
7

---

总结

注意order by a,b
表示先用a排序，在a排序的基础上再用b规则进行一次排序