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

加入:

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
);

select *
from client as c inner join
(
    SELECT avg(height) as avgh, gender
    from client
    group by gender
) as t
on c.gender = t.gender and c.height > t.avgh

您可以在性别和身高更大的情况下内部联接子查询。

SELECT c1.cname
       FROM client c1
            INNER JOIN (SELECT c2.gender,
                               avg(c2.height) height
                               FROM client c2
                               GROUP BY c2.gender) x
                       ON x.gender = c1.gender
                          AND x.height > c1.height;

SELECT PplHeight.cname, PplHeight.gender, PplHeight.height
FROM 
  client AS PplHeight,
  (SELECT gender, avg(height) as avg_height from client group by gender) avg_h
WHERE PplHeight.gender = avg_h.gender
  AND PplHeight.height > avg_h.avg_height