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…)
SELECTf AS 'code',f AS 'name',count( 1 ) AS 'value'FROM(SELECTIF(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 FROMsys_user t ) t GROUP BYf