sql复杂业务处理

1.美团面试

employees表 包含employee_id 员工id, department_id 部门号, salary 工资

-- 思路用排序函数求最高工资跟最低工资的第一，在基础上用条件筛选出大于1的人，接着用group by分组聚合
WITH max_min AS (
SELECT  *,
        dense_RANK() over(PARTITION by department_id ORDER BY salary) AS min_salary,
        dense_RANK() over(PARTITION by department_id ORDER BY salary desc) AS max_salary
FROM employees)
SELECT  department_id,
        avg(salary)
FROM max_min 
WHERE min_salary!=1 AND max_salary!=1 
GROUP BY department_id

2.阿里

输出总订单金额前三的买家，对应的订单总金额和订单量
orders 表 字段 order_id 表示订单的唯一标识符 buyer_id：用来标识购买订单的买家
amount：记录了每个订单的金额

WITH orders_over AS(
SELECT  buyer_id AS 买家id,  
        count(order_id) as 总订单量,
        sum(amount) as 总金额,
        DENSE_RANK() over(ORDER BY sum(amount) DESC) AS rk
from orders
GROUP BY buyer_id)
SELECT 买家id,总订单量,总金额 
FROM orders_over 
WHERE rk<=3;

3.学生成绩分析（综合）

查询需求：
students ：student_id，name，class_id
scores：score_id，student_id，subject，score，exam_date
1.计算每个学生总分，并给出班级内总分排名
思路：成绩表中按学生id分组聚合，得出总分，由于要按班级分组求排名，所以要与学生表连表，之后用学生表里的classid分组，排序开窗，再按求出的学生成绩order by即可，由于可能有重分的，所以用dense_rank同分同名，不跳名

select students.name,
       students.class_id,
       sum(scores.score) as 总分,
       dense_rank() over (partition by class_id  order by sum(scores.score)) as 排名
from scores
join students
on scores.student_id = students.student_id
group by students.student_id;

2.找出每个班级总分第1名的学生

with rk as (
    select students.name,
       students.class_id,
       sum(scores.score) as 总分,
       dense_rank() over (partition by class_id  order by sum(scores.score)) as 排名
from scores
join students
on scores.student_id = students.student_id
group by students.student_id)
select * from rk where 排名 = 1;

4.学生相同成绩

求成绩表中成绩相同的学生信息，例如30分跟30分的同学信息跟50分跟50分同学信息

with count_sc as(
    select *,
    count(*) over(partition by score) as count_s
    from score
)
select * from count where sount_s >= 2;

还有一种用到子查询，我子查询中的sql语句按成绩分组count()聚合，然后用HAVING筛选count>=2，最后select查出相同的分数
主查询筛选条件用WHERE score in (子查询)即可 ，会查出所有成绩相同的学生

SELECT *
FROM score
WHERE score IN (
    SELECT score
    FROM score
    GROUP BY score
    HAVING COUNT(*) >= 2
);