mysql-根据身份证号更新性别、出生日期,根据出生日期查询年龄...
根据身份证号查询更新性别:
update uufs_ltx_info set xb= (case SUBSTRING(sfzh,17,1)&1 when 1 then '男' else '女' end) ;
update 表名 set xb = (case SUBSTRING(sfzh,17,1)&1 when 1 then '男' else '女' end) ;
注意:xb(性别),sfzh(身份证号)根据表字段自行修改
根据身份证号查询更新出生日期:
select sfzh as 身份证号, cast(substring(sfzh,7,8) as date) as 出生日期, DATE_FORMAT(cast(substring(sfzh,7,8) as date), '%m-%d') as 生日 from table1;
UPDATE table1 set csrq=cast(substring(sfzh,7,8) as date) where LENGTH(sfzh) = 18;
根据出生日期查询年龄:
SELECT TIMESTAMPDIFF(YEAR,DATE(csrq),CURDATE()) AS age FROM uufr_ltx_info;
-- 求各党支部中 各类政治面貌人员的年龄总和
select dzbmc,zzmmmc,count(*) as rs,sum(TIMESTAMPDIFF(YEAR,DATE(csrq),CURDATE())) AS age from uufr_ltx_info where sfls = '否' GROUP BY dzbmc,zzmmmc;
-- 求各党支部中的各类政治面貌人员的平均年龄
select dzbmc,zzmmmc,count(*) as rs,sum(TIMESTAMPDIFF(YEAR,DATE(csrq),CURDATE()))/count(*) AS age from uufr_ltx_info where sfls = '否' GROUP BY dzbmc,zzmmmc;