mysql - 如何在MySQL中使用MAX？

Question

假设下表/数据:

person_id  age  gender  name
1          25   Female  Jane
2          28   Male    John
3          29   Female  Jill
4          24   Male    Jack

如何查询年龄最大的男性和女性？

这不起作用:SELECT person_id, max(age), gender, name FROM person GROUP BY gender

返回时:

person_id  age  gender  name
2          28   Male    John
1          29   Female  Jane

期望的结果是:

person_id  age  gender  name
2          28   Male    John
3          29   Female  Jill

我的 SQL 有什么问题？

Answer 1

结合ORDER BY和LIMIT 1可以得到一行的所有数据。在你的情况下，使用它两次并与 UNION 结合:

  ( SELECT * 
    FROM person 
    WHERE gender = 'Male'
    ORDER BY age DESC
    LIMIT 1
  )
UNION ALL
  ( SELECT * 
    FROM person 
    WHERE gender = 'Female'
    ORDER BY age DESC
    LIMIT 1
  )

---

另一种方法是fing男性和女性的最大年龄(带子查询):

SELECT *
FROM person
WHERE ( gender = 'Male'
      AND age =  
          ( SELECT MAX(age) 
            FROM person 
            WHERE gender = 'Male'
          )
      )
   OR ( gender = 'Female'
      AND age =  
          ( SELECT MAX(age) 
            FROM person 
            WHERE gender = 'Female'
          )
      )

如果你有超过 2 种性别，或者如果你不想在查询中硬编码 Male 和 Female 常量，这可以重写为:

SELECT p.*
FROM person AS p
  JOIN
      ( SELECT gender
             , MAX(age) AS maxage 
        FROM person 
        GROUP BY gender
      ) AS pg
    ON  pg.gender = p.gender
    AND pg.maxage = p.age

---

上述查询有一个主要区别。第一个只会给你一个男性和一个女性结果(最多)。当有许多(男性)具有相同的最大年龄并且女性也类似时，第二个和第三个查询会给你不止一个。

(gender, age) 上的索引将有助于任一查询。