mybatis根据数据库身份证号分年龄段查询

sql

<!-- 分年龄段查询 -->
    <select id="getAgeSection" parameterType="Integer" resultType="com.bh.emergency.system.bean.bigScreen.AgeSection">
        select
        count(case when <![CDATA[ ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) < 17 ]]> and to_sex=0 THEN to_id END) seventeenMan,
        count(case when <![CDATA[ ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) < 17 ]]> and to_sex=1 THEN to_id END) seventeenWoman,
        count(case when ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) >= 17 and <![CDATA[ ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) <= 30 ]]> and to_sex=0 THEN to_id END) eighteenToThirtyFourMan,
        count(case when ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) >= 17 and <![CDATA[ ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) <= 30 ]]> and to_sex=1 THEN to_id END) eighteenToThirtyFourWoman,
        count(case when ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) >= 31 and <![CDATA[ ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) <=60 ]]> and to_sex=0 THEN to_id END) thirtyFiveTosixtyMan,
        count(case when ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) >= 31 and <![CDATA[ ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) <=60 ]]> and to_sex=1 THEN to_id END) thirtyFiveTosixtyWoman,
        count(case when ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) > 60 and to_sex=0 THEN to_id END )sixtyMan,
        count(case when ((substring(now(),1,4)-substring(to_card_num,7,4))-(substring(to_card_num,11,4)-date_format(now(),'%m%d')>0)) > 60 and to_sex=1 THEN to_id END )sixtyWoman
        from t_owner
        <where>
            <if test=" areaId != null">
                area_id = #{areaId}
            </if>
        </where>
    </select>

对应的实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class AgeSection {
    /**
     * 小于17岁男总数
     */
    private Integer seventeenMan;
    /**
     * 小于17岁女总数
     */
    private Integer seventeenWoman;
    /**
     * 18到34岁男总数
     */
    private Integer eighteenToThirtyFourMan;
    /**
     * 18到34岁女总数
     */
    private Integer eighteenToThirtyFourWoman;
    /**
     * 大于34小于等于60岁男总数
     */
    private Integer thirtyFiveTosixtyMan;
    /**
     * 大于34小于等于60岁女总数
     */
    private Integer thirtyFiveTosixtyWoman;
    /**
     * 大于60岁男总数
     */
    private Integer sixtyMan;
    /**
     * 大于60岁女总数
     */
    private Integer sixtyWoman;
}