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

1 最大数量高于平均水平的订单

1.1 题目内容

1.1.1 基本题目信息

在这里插入图片描述

1.1.2 示例输入输出

a 示例输入

在这里插入图片描述

b 示例输出

在这里插入图片描述

1.2 示例sql语句

# Write your MySQL query statement below
WITH t1 as
(
    SELECT order_id,avg(quantity) Aquantity
    FROM OrdersDetails
    GROUP BY order_id
    ORDER BY Aquantity desc
    limit 1
)
SELECT order_id
FROM OrdersDetails
GROUP BY order_id
HAVING max(quantity)>
(
    SELECT Aquantity
    FROM t1
)

1.3 运行截图

在这里插入图片描述

2 将工资相同的雇员分组

2.1 题目内容

2.1.1 基本题目信息

在这里插入图片描述

2.1.2 示例输入输出

在这里插入图片描述

2.2 示例sql语句

# 使用dense_rank() 是密集排序 没有dense时会跳过
# 窗口函数也可以没有partion by分组这个条件
SELECT e1.employee_id,e1.name,e1.salary,dense_rank() over(ORDER BY e1.salary asc) team_id 
FROM Employees e1
INNER JOIN
(
    SELECT e.salary
    FROM Employees e
    GROUP BY e.salary
    HAVING count(*)>=2
)e2
ON e1.salary=e2.salary
ORDER BY team_id asc,employee_id asc

2.3 运行截图

在这里插入图片描述

3 按分类统计薪水

3.1 题目内容

3.1.1 基本题目信息

在这里插入图片描述

3.1.2 示例输入输出

在这里插入图片描述

3.2 示例sql语句

# 需要使用临时表,就使用with 表别名 as (查询到的表)的方式
WITH t1 AS
(
    SELECT 1 id,'Low Salary' means
    UNION 
    SELECT 2 id,'Average Salary' means
    UNION 
    SELECT 3 id,'High Salary' means
)
SELECT t1.means category,IFNULL(t3.num,0) accounts_count
FROM t1
LEFT JOIN
(
    SELECT t2.id,count(account_id) num
    FROM
    (
        SELECT account_id,
                    case when income<20000 then 1
                            when income between 20000 and 50000 then 2
                            else 3
                end id
        FROM Accounts
    )t2
    GROUP BY t2.id
)t3
ON t1.id=t3.id