mysqlDM数据库中利用函数更新身份证字段,单表计算单表更新

本文链接：https://blog.csdn.net/weixin_61503139/article/details/133420447

#查询总数,男女的分别人数
SELECT
	count( * ),
	sum( CASE WHEN gender = 1 THEN 1 ELSE 0 END ) AS nan,
sum( CASE WHEN gender = 2 THEN 1 ELSE 0 END ) AS nv 	
FROM
	t_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
BEGIN
	DECLARE sal varchar(20) ;
	
	if deptName=1 then 
	set sal='男';
	else 
	set sal='女';
	end if;
	
	
	RETURN sal;
END

select * from t_user

#查询总数,男女的分别人数
SELECT
	count( * ),
	sum( CASE WHEN gender = 1 THEN 1 ELSE 0 END ) AS nan,
sum( CASE WHEN gender = 2 THEN 1 ELSE 0 END ) AS nv 	
FROM
	t_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)