group by 专题

1. 涉及 “最多/最大”，即比其他所有（all）都大（>=）：

常和 having count() 搭配，作为一个判断条件；还可搭配 max()，all() 。

力扣586：

select customer_number
from Orders 
group by customer_number 
having count(*)>=
    all(select count(*)
        from Orders
        group by customer_number)

select customer_number
from Orders 
group by customer_number 
having count(*)=
    (select max(cnt)
    from   
        (select count(*) as cnt 
        from Orders
        group by customer_number) as tmp)

力扣1076：

select project_id
from Project,Employee
where Project.employee_id = Employee.employee_id
group by project_id
having count(*) >= all
    (select count(*)
    from Project,Employee
    where Project.employee_id = Employee.employee_id
    group by project_id)

力扣1867：

select order_id
from
    (select order_id,
        max(avg_quantity) over() as max_avg_quantity,
        max_quantity
    from
        (select order_id,
            avg(quantity) as avg_quantity,
            max(quantity) as max_quantity
        from OrdersDetails
        group by order_id) as t1) as t2
where max_avg_quantity < max_quantity

2. 涉及多字段聚合：即 group by A, B...

力扣1149：

select distinct viewer_id as id
from Views
group by view_date,viewer_id
having count(distinct article_id )>=2
order by id

力扣1193：

select left(trans_date,7) as month,country,
    count(*) as trans_count,
    sum(if(state='approved',1,0)) as approved_count,
    sum(amount) as trans_total_amount,
    sum(if(state='approved',amount,0)) as approved_total_amount
from Transactions
group by month,country

力扣1308：

select gender,day,sum(sum_score_points) over(partition by gender order by day) as total
from
    (select gender,day,sum(score_points) as sum_score_points
    from Scores
    group by gender,day) as tmp
order by gender,day

3. 常规 having count() ：

力扣570：

关键点1：满足条件的 managerId

with tmp as
(select managerId 
from Employee 
group by managerId 
having count(*)>=5)

关键点2：该 managerId 的名字

select name
from Employee
where id=...

题解：

select name
from Employee
where id=(select managerId 
    from Employee 
    group by managerId 
    having count(*)>=5)

-END