Mysql中计算年龄,对精度高的可以使用TIMESTAMPDIFF(YEAR,birthday_column,now_date)
不高的可以使用substr(birthady,1,4)截取年份字符串直接相减,效率更高
SELECT
NAME,
COUNT(NAME) AS VALUE
FROM
(
SELECT
CASE
WHEN age < 20 THEN '19岁及以下'
WHEN age <= 29 THEN '20-29岁'
WHEN age <= 39 THEN '30-39岁'
WHEN age <= 49 THEN '40-49岁'
ELSE '50岁及以上'
END AS NAME
FROM
(
select
TIMESTAMPDIFF(YEAR,ry.`出生日期`,CURDATE())AS age
from
test_ry ry
WHERE ry.`出生日期` is not NULL
)t
)t GROUP BY NAME
有更好的方法,欢迎留言