如何在MySQL中使用MAX? [英] How to use MAX in MySQL?

问题描述

假设以下表格/数据:

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

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

How do I query for the oldest Male and Female?

这不起作用: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怎么了?

推荐答案

您可以获取结合了ORDER BY和LIMIT 1的行的所有数据.在您的情况下，请使用两次并与UNION组合:

You can get all the data of a row combining ORDER BY and LIMIT 1. In your case, using this twice and combining with 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
  )

---

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

---

Another way is to fing the maximum age of males and females (with subqueries):

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常量进行硬编码，则可以将其重写为:

If you have more than 2 genders or if you prefer not to hardcode Male and Female constants in the query, this can be rewritten as:

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

---

以上查询有一个主要区别.第一项只会给您一个男性结果，一个女性结果(最多).当有很多(男性)具有相同的最大年龄，而女性的年龄相似时，第二和第三查询将为您提供多个查询.

---

The above queries have a main difference. The 1st will give you only one male and only one female result (at most). The 2nd and 3rd query will give you more than one when there are many (males) with same maximum age and similarly for females.

(gender, age)上的索引将帮助任一查询.

An index on (gender, age) will help either query.

这篇关于如何在MySQL中使用MAX?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！