Mysql按身份证号或生日统计各个年龄段的人数

Mysql统计各个年龄段的人数

select 语句中 if 的用法

IF( expr1 , expr2 , expr3 )
expr1 的值为 TRUE，则返回值为 expr2
expr1 的值为FALSE，则返回值为 expr3

例子是截取身份证号字段，截取日期字段也是一样的
获取当前日期年份并减去截取的身份证号的年份判断是否大于10，大于返回10，否则在进行 IF 判断是否大于10小于20，以此类推向后嵌套判断。
IF ( YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) <= 10, 10, IF…)

SELECT
	f AS 'code',
	f AS 'name',
	count( 1 ) AS 'value'
FROM
(
	SELECT
		IF(YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) <= 10, 10, 
			IF ( YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) > 10 AND YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) <= 20, 20, 
				IF ( YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) > 20 AND YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) <= 30, 30, 
					IF ( YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) > 30 AND YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) <= 40, 40, 
						IF ( YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) > 40 AND YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) <= 50, 50, 
							IF ( YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) > 50 AND YEAR ( NOW( ) ) - SUBSTR( t.ZJHM FROM 7 FOR 4 ) <= 60, 60, 70 ) 
						) 
					) 
				) 
			) 
		) AS f 
	FROM
		sys_user t 
) t 
GROUP BY
f

查询结果