MySQL将字符串转换为日期以计算年龄并按年龄排序 [英] MySQL Convert String To Date Calculate Age And Order BY Age

问题描述

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

I'm trying to write a mysql query that will convert a string to a date and then calculate an age and then count the ages and group by age.

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

I have the string to date and calculate age query:

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`

但是我无法按年龄计数和分组.有人可以帮我吗?

But I can't count and group by age. Could anyone help me?

推荐答案

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演示
• SQLFiddle Demo

这篇关于MySQL将字符串转换为日期以计算年龄并按年龄排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！