mysql 通过嵌套case语句和if语句来计数性别和性别区间人数

CREATE TABLE `test`.`exportresult2018`  (

`E2(C|100)` text NULL,//性别

`I2(N|30)` text NULL,//年龄

)

需要完成的表格为

一开始我写的就是没包含case 和 if语句，单纯是条件，所以一直报错。

然后我疯狂搜索，找到了这两种语句解决了我的问题。主要是自我记录一下，以后大概率还要阶段性用到。

SELECT COUNT(*) AS '例次',
COUNT(CASE `E2(C|100)` WHEN '1' THEN '男' END) AS '男',
COUNT(CASE `E2(C|100)` WHEN '2' THEN '女' END) AS '女',
COUNT(IF(`I2(N|30)`>=60 AND `I2(N|30)`<=64,TRUE,NULL)) AS '60-64',
COUNT(IF(`I2(N|30)`>=65 AND `I2(N|30)`<=69,TRUE,NULL)) AS '65-69',
COUNT(IF(`I2(N|30)`>=70 AND `I2(N|30)`<=74,TRUE,NULL)) AS '70-74',
COUNT(IF(`I2(N|30)`>=75 AND `I2(N|30)`<=79,TRUE,NULL)) AS '75-79',
COUNT(IF(`I2(N|30)`>=80 AND `I2(N|30)`<=84,TRUE,NULL)) AS '80-84',
COUNT(IF(`I2(N|30)`>=85 AND `I2(N|30)`<=89,TRUE,NULL)) AS '85-89',
COUNT(IF(`I2(N|30)`>=90 AND `I2(N|30)`<=94,TRUE,NULL)) AS '90-94',
COUNT(IF(`I2(N|30)`>=95,TRUE,NULL)) AS '大于90'
FROM `exportresult2018`

跑出来的结果就是