MySQL根据出生日期查询年龄，以及对年龄进行分组统计_mysql根据生日查询年龄大于18

1. 根据出生日期查询年龄

SELECT TIMESTAMPDIFF(YEAR, DATE(出生日期), CURDATE()) AS 别名 FROM 表名;

函数DATE()：提取日期或日期/时间表达式的日期部分；

函数CURDATE()：返回当前的日期；

函数TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)：计算两个日期的时间差，unit是计算时间差的单位，可以是SECOND秒、MINUTE分钟、HOUR小时、DAY天、WEEK星期、MONTH月、QUARTER季度、YEAR年。

2.对年龄进行分组统计

a、case 结构

SELECT
	ageGroup, COUNT(*) AS ageNum
FROM
(
	SELECT
		CASE
			WHEN TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) <= 18 THEN	'≤18岁'
			WHEN TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) >= 19 AND TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) <= 20 THEN	'19-20岁'
			WHEN TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) >= 21 AND TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) <= 22 THEN	'21-22岁'
			WHEN TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) >= 23 AND TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) <= 24 THEN	'23-24岁'
			WHEN TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) >= 25 THEN '≥25岁'
			ELSE '未知'
		END AS ageGroup
	FROM 表名
) AS a 
GROUP BY ageGroup;

b、if 结构

SELECT
	 ageGroup, COUNT(*) AS ageNum
FROM
(
	SELECT
		IF(TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) <= 18, '≤18岁',
			IF(TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) >= 19 AND TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) <= 20, '19-20岁',
				IF(TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) >= 21 AND TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) <= 22, '21-22岁',
					IF(TIMESTAMPDIFF(YEAR, DATE(字段), CURDATE()) > 23, '≥23岁', '未知')
				)
			)
		) AS ageGroup
	FROM 表名
) AS a
GROUP BY ageGroup;