mysql - 将单个字段与 SQL 中由其他字段分组的同一字段的平均值进行比较

Question

我正在尝试创建一个查询，以获取高于其性别平均身高的人的姓名。

我知道如何获得每个性别的平均身高：

SELECT avg(height), gender from client group by gender

但我不知道如何将每个人（基于他的性别）与这个子查询进行比较..

这是我能做的：

SELECT cname 
FROM (SELECT height, cname, gender
        FROM client AS PplHeight
        HAVING (height > (SELECT avg(height) from client group by gender))) AS AboveAvg

提前致谢。

标签： mysqlsql

Answer 1

要么加入：

select client.*
from client
join
(
  select gender, avg(height) as avg_height
  from client
  group by gender
) genders on client.gender = genders.gender and client.height > genders.avg_height;

或使用相关子查询：

select *
from client
where height >
(
  select avg(height)
  from client all_clients
  where all_clients.gender = client.gender
);