sql - 在子查询中如何获取 max() 组的值？

Question

所以我想找到其他组中平均年龄最大的组的部门名称或部门 ID(dpmid)，这是我的查询:

select 
 MAX(avg_age) as 'Max average age' FROM (
    SELECT 
      AVG(userage) AS avg_age FROM user_data GROUP BY 
         (select dpmid from department_branch where 
            (select dpmbid from user_department_branch where 
               user_data.userid = user_department_branch.userid)=department_branch.dpmbid)
) AS query1

此代码仅显示平均年龄的最大值，当我尝试显示组名时，它会显示错误的组名。

那么，如何显示具有来自另一个表的子查询的最大组的名称？？？

Answer 1

你可以试试这个..

select MAX(avg_age) as max_avg, SUBSTRING_INDEX(MAX(avg_age_dep),'##',-1)  as max_age_dep from
(
    SELECT 
     AVG(userage) as avg_age, CONCAT( AVG(userage), CONCAT('##' ,department_name)) as avg_age_dep
     FROM user_data 
     inner join user_department_branch 
            on user_data.userid = user_department_branch.userid
     inner join department_branch 
            on department_branch.dpmbid = user_department_branch.dpmbid
     inner join department
            on department.dpmid = department_branch.dpmid
     group by department_branch.dpmid
) tab_avg_age_by_dep
;

我对 ipothesys 进行了一些更改，将部门名称放置在“部门”类比表中。因此，由于需要在 plus 中加入一个表，然后我更改了您的查询，最终如果部门名称被放置(但我不这么认为)在 branch_department 表中，您可以将字段及其处理添加到您的查询中

更新

如前所述，如果您想避免相同的平均情况，您可以通过以这种方式附加一个 rownum id 来进一步使平均值变得单一:

select MAX(avg_age) as max_avg, SUBSTRING_INDEX(MAX(avg_age_dep),'##',-1)  as max_age_dep from
(
    SELECT 
     AVG(userage) as avg_age, CONCAT( AVG(userage), CONCAT('##', CONCAT( @rownum:=@rownum+1, CONCAT('##' ,department_name)))) as avg_age_dep
     FROM user_data 
     inner join user_department_branch 
            on user_data.userid = user_department_branch.userid
     inner join department_branch 
            on department_branch.dpmbid = user_department_branch.dpmbid
     inner join department
            on department.dpmid = department_branch.dpmid
     ,(SELECT @rownum:=0) r 
     group by department_branch.dpmid
) tab_avg_age_by_dep
;