MySQL将字符串转换为日期计算年龄并按年龄排序

Question

我正在尝试编写一个 mysql 查询，它将一个字符串转换为一个日期，然后计算一个年龄，然后计算年龄并按年龄分组。

我有日期字符串并计算年龄查询:

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(STR_TO_DATE( birthday,  '%m/%d/%Y' )) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(STR_TO_DATE( birthday,  '%m/%d/%Y' ), 5)) as age 
  FROM `persons`

但我不能按年龄统计和分组。谁能帮帮我？

Answer 1

SELECT  EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),STR_TO_DATE(birthday, '%m/%d/%Y'))))) + 0 age,
        COUNT(*) TotalCount
FROM    tableNAme
GROUP   BY EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),STR_TO_DATE(birthday, '%m/%d/%Y'))))) + 0
ORDER   BY TotalCount DESC

• SQLFiddle Demo