MySQL场景——连接并分组后根据聚合函数值查询

查询当前产量表中总产量最大的班组名称及该班组的总产量数值

1、窗口函数法：

select Team_name 班组名,ROUND(zcl,1) 产量总和
    from
        (
            select
                Team_id,
                sum(Produce) zcl,
                rank() over (order by sum(Produce) desc ) rn     --不需要partition，直接根据分组查询结果进行排名
            from
                cl_table
            group by Team_id
        ) t1 JOIN team_table ON t1.Team_id=team_table.Team_id
WHERE rn=1;

2、普通子查询法： 

select Team_name,round(sum(Produce),1) zcl
    from
        cl_table join team_table tt on tt.Team_id = cl_table.Team_id
    group by cl_table.Team_id having sum(Produce)=(
        select max(zcl)
            from (
                select Team_id,sum(Produce) zcl
                from cl_table group by Team_id
            ) t1
    );