MySQL转换字符串到日期计算年龄和顺序BY年龄
问题描述:
我想写一个mysql查询,将字符串转换为日期,然后计算一个年龄,然后按年龄计算年龄和组。MySQL转换字符串到日期计算年龄和顺序BY年龄
我有字符串日期和计算年龄查询:
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`
但我不能指望年龄和组。任何人都可以帮我吗?
答
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
+0
完美工作,欢呼声。 – Danny
http://ma.tt/2003/12/calculate-age-in-mysql/ –