#查询总数,男女的分别人数
SELECTcount( * ),sum( CASE WHEN gender = 1 THEN 1 ELSE 0 END ) AS nan,
sum( CASE WHEN gender = 2 THEN 1 ELSE 0 END ) AS nv 	
FROMt_user

写死版本的更新

#可以正确运行的sql,这样的话是写死版本
UPDATE t_user 
SET gender = ( select sex from  (SELECT CASE WHEN gender = 1 THEN '男' ELSE '女' END as sex FROM t_user s where s.id=1) as k)  

最终解决方案,根据函数来更新

#函数传入身份证号,来计算性别进行数据的更新
UPDATE t_user 
SET gender =myf3(t_user.gender)

对应的参考函数

CREATE DEFINER=`root`@`localhost` FUNCTION `myf3`(deptName VARCHAR(20)) RETURNS varchar(20) CHARSET utf8
BEGINDECLARE sal varchar(20) ;if deptName=1 then set sal='男';else set sal='女';end if;RETURN sal;
END

select * from t_user#查询总数,男女的分别人数
SELECTcount( * ),sum( CASE WHEN gender = 1 THEN 1 ELSE 0 END ) AS nan,
sum( CASE WHEN gender = 2 THEN 1 ELSE 0 END ) AS nv 	
FROMt_user#可以正确运行的sql
UPDATE t_user 
SET gender = ( select sex from  (SELECT CASE WHEN gender = 1 THEN '男' ELSE '女' END as sex FROM t_user s where s.id=t_user.id) as k)  #函数传入身份证号,来计算性别进行数据的更新
UPDATE t_user 
SET gender =myf3(t_user.gender)UPDATE t_user 
SET gender =  (SELECT CASE WHEN gender = 1 THEN '男' ELSE '女' END as sex FROM t_user as s where s.id=1)  #可以正确运行的sql,这样的话是写死版本
UPDATE t_user 
SET gender = ( select sex from  (SELECT CASE WHEN gender = 1 THEN '男' ELSE '女' END as sex FROM t_user s where s.id=1) as k)  UPDATE t_user 
SET gender = (SELECT 'n' from dual)