Mysql身份证计算年龄，年龄筛选过滤

最新推荐文章于 2023-05-18 15:37:00 发布

<黑科技T_T

最新推荐文章于 2023-05-18 15:37:00 发布

阅读量233

点赞数

分类专栏： MySql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/luojiawen208/article/details/129442589

版权

MySql 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

该SQL查询用于统计各地区驾校学员的总人数、申请日期在特定时间内的男女比例，以及年龄在25岁以下、25到55岁、55岁以上的人数分布。

摘要由CSDN通过智能技术生成

(substring(now(),1,4)-substring(ts.idcard,7,4))-(substring(ts.idcard,11,4)-date_format(now(),'%m%d')>0) &lt;25  ) age25DownCount

select
        td.district,
        count(1) sumCount,
        (select count(1) from ods_dsznh_t_training_studentinfo ts
        INNER JOIN ods_dsznh_t_training_driverschool tdd on tdd.id = ts.driverschoolid where tdd.district = td.district AND ts.applydate &lt;= #{rrTimeEnd} ) sumCountRr,
        (select count(1) from ods_dsznh_t_training_studentinfo ts
        INNER JOIN ods_dsznh_t_training_driverschool tdd on tdd.id = ts.driverschoolid where tdd.district = td.district AND ts.sex = 1 AND ts.applydate &lt;=  #{query.endTime} ) sumMan,
        (select count(1) from ods_dsznh_t_training_studentinfo ts
        INNER JOIN ods_dsznh_t_training_driverschool tdd on tdd.id = ts.driverschoolid where tdd.district = td.district AND ts.sex = 2 AND ts.applydate &lt;=  #{query.endTime} ) sumWoman,
        (select count(1) from ods_dsznh_t_training_studentinfo ts
        INNER JOIN ods_dsznh_t_training_driverschool tdd on tdd.id = ts.driverschoolid where tdd.district = td.district AND ts.applydate &lt;=  #{query.endTime}
        AND (substring(now(),1,4)-substring(ts.idcard,7,4))-(substring(ts.idcard,11,4)-date_format(now(),'%m%d')>0) &lt;25  ) age25DownCount,
        (select count(1) from ods_dsznh_t_training_studentinfo ts
        INNER JOIN ods_dsznh_t_training_driverschool tdd on tdd.id = ts.driverschoolid where tdd.district = td.district AND ts.applydate &lt;=  #{query.endTime}
        AND (substring(now(),1,4)-substring(ts.idcard,7,4))-(substring(ts.idcard,11,4)-date_format(now(),'%m%d')>0) >25
        AND (substring(now(),1,4)-substring(ts.idcard,7,4))-(substring(ts.idcard,11,4)-date_format(now(),'%m%d')>0) &lt;55 ) age2555DownCount,
        (select count(1) from ods_dsznh_t_training_studentinfo ts
        INNER JOIN ods_dsznh_t_training_driverschool tdd on tdd.id = ts.driverschoolid where tdd.district = td.district AND ts.applydate &lt;=  #{query.endTime}
        AND (substring(now(),1,4)-substring(ts.idcard,7,4))-(substring(ts.idcard,11,4)-date_format(now(),'%m%d')>0)  >55  ) age55UpCount
        from ods_dsznh_t_training_studentinfo ts
        INNER JOIN ods_dsznh_t_training_driverschool td on td.id = ts.driverschoolid
        where ts.applydate &lt;=  #{query.endTime}
        <if test="@com.traffic.admin.business.util.CommonUtil@isNotNull(query.district)">
            AND td.district = #{query.district}
        </if>
        GROUP BY td.district