如何在MySQL中使用MAX?

本文介绍了如何在MySQL中使用MAX?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设以下表格/数据:

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.