MYSQL 按照身份证筛选年龄和性别

最新推荐文章于 2023-07-24 16:52:54 发布

凄凉山谷的风 OL

最新推荐文章于 2023-07-24 16:52:54 发布

阅读量854

点赞数

文章标签： sql 数据库

嘎嘎淘

本文链接：https://blog.csdn.net/zhang804633234/article/details/126381789

版权

#年龄小于18岁的

Select *from (select  id,
substr(CURRENT_DATE,1,4)
-
substr(id_num,7,4)
-
(case SIGN(substr(id_num,11,4)-date_format(CURRENT_DATE,'MMdd')) when -1 then 0 else 1 end) 
AS AGE
,
case mod(substr(id_num,17,1),2) 
when  0 then '女' else '男' end  
AS SEX
from   web_member_real order by AGE asc)t where t.AGE<18



#筛选
select  id,
substr(CURRENT_DATE,1,4)
-
substr(id_num,7,4)
-
(case SIGN(substr(id_num,11,4)-date_format(CURRENT_DATE,'MMdd')) when -1 then 0 else 1 end) 
AS AGE
,
case mod(substr(id_num,17,1),2) 
when  0 then '女' else '男' end  
AS SEX
from   web_member_real order by AGE asc