mysql里的max怎么用,如何在MySQL中使用MAX？

Assume the following table/data:

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?

This is not working: SELECT person_id, max(age), gender, name FROM person GROUP BY gender

as it returns:

person_id age gender name

2 28 Male John

1 29 Female Jane

The desired result is:

person_id age gender name

2 28 Male John

3 29 Female Jill

What's wrong with my SQL?

解决方案

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'

)

)

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.

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