sql server如何设置范围为男或女 sql设置性别

2024-06-29 13:28| 来源: 网络整理| 查看: 265

大家晚上好，我是阿涛。

sql server如何设置范围为男或女 sql设置性别_sql replace函数用法

今天的主题是介绍两道SQL题，有些方法函数挺常用的，第二道是腾讯面试题。

【1】在录入人员身份信息的时候，小王把人的性别编码弄错了，导致数据库中表a1的人员性别都是反的，这个时候如何快速把性别修改过来？

select * from test.a1;

数据是这样的：

sql server如何设置范围为男或女 sql设置性别_子查询_02

答案：

update test.a1 set ssex = replace('男女',ssex,'');# orupdate test.a1 set ssex = case ssex when '男' then '女'else '男' end;# orupdate test.a1 set ssex = IF(ssex= '男','女','男');

结果：

sql server如何设置范围为男或女 sql设置性别_字符串_03

补充知识：

这里用到的是replace的更新替换，

replace的用法有：

[1]查询替换

select *,replace(location,'荷兰','河南') AS rep from aa; # 把荷兰替换成河南

[2]更新替换

update test.a1 set ssex=replace('男女',ssex,'');# 对目标字符串'男女'替换，# (相当于做差男女-男=女 or 男女-男=女)# 把ssex替换成' '，那就完成了对男女的替换# 备注：set sql_safe_updates=0; -- 上面这个解决错误1175 无主键条件下执行update和delete命令

【2】如何计算最近员工的连续考勤天数和历史最大连续考勤天数？

我先创建下员工考勤表，就做5天的表把，fdate是时间，id是员工号，fis_sign_in=0表示未打卡，1表示正常打卡。

create table t_user_attendance (fdate date,id int(10),fis_sign_in int(2));insert into t_user_attendance values('20200826',1,1);insert into t_user_attendance values('20200825',1,1);insert into t_user_attendance values('20200824',1,1);insert into t_user_attendance values('20200823',1,1);insert into t_user_attendance values('20200822',1,0);insert into t_user_attendance values('20200826',2,0);insert into t_user_attendance values('20200825',2,1);insert into t_user_attendance values('20200824',2,1);insert into t_user_attendance values('20200823',2,1);insert into t_user_attendance values('20200822',2,1);insert into t_user_attendance values('20200826',3,1);insert into t_user_attendance values('20200825',3,1);insert into t_user_attendance values('20200824',3,1);insert into t_user_attendance values('20200823',3,0);insert into t_user_attendance values('20200822',3,1);select * from t_user_attendance order by fdate;

数据是这样的：

sql server如何设置范围为男或女 sql设置性别_mysql_04

答案：

问题1思路，比如要计算到8.26这天的员工连续考勤天数，只需要把最近一次没有考勤的那一天找到就可以了(连续签到天数consecutive_days连续签到天数consecutive_days)；

create table t_user_consecutive_days as select id,datediff('20200826',fdate_max) as consecutive_days from (select id,max(fdate) as fdate_max from t_user_attendance where fis_sign_in=0 group by id ) t;# 加一个子查询，找到之前没有打卡的那天就可 # datediff是mysql的函数，但在oracle不可用哦select * from t_user_consecutive_days ;

结果：

sql server如何设置范围为男或女 sql设置性别_mysql_05

得到员工1连续打卡4天，员工2连续打卡0天(因为8.26那天未打卡)，员工3连续打卡3天。

*****问题2思路，比如要计算到员工历史最大连续考勤天数，这个就比较复杂了，先把员工的打卡记录绘制成一个连续的字符串’01111100111111011...'，然后呢以0为分隔符号，这样我们是不是就得到了一串又一串的‘111..'，这样我们再计算每一串的’11..'的长度是不是就得到了员工的连续打卡天数，最后再做个max函数就得到了员工历史的最大连续考勤天数对吧。

我们来拆分下：

1.第一步，把员工的打卡情况01这个fis_sign_in绘制成一个字符串，这里可以用到group_concat()函数或者wm_concat()函数，这是把多行变成一行。group_concat用于mysql，wm_concat用于oracle。

select id,group_concat(fis_sign_in) as fsign_record from t_user_attendance group by id;# 把每个用户的fis_sign_in汇总到一个字符串

结果：

sql server如何设置范围为男或女 sql设置性别_mysql_06

2.第二步，按照0分隔符，把一行变成多行，这里用lateral view explode+split函数，但是explode只能在hive中做，我这里就展示不了，看代码把：

select id,fsign_record,cut_fsign_record from (select id,group_concat(fis_sign_in) as fsign_record from t_user_attendance group by id) t1 lateral view explode(split_str(fsign_record,'0')) t as cut_fsign_record ;-- 这里中间有个子查询，得到就是一串的打卡记录fsign_record，-- 然后对这fsign_record 进行'0'拆分

3.第三步，就对上面得到的每一串'11..','1111..'做max求最大值就行。

总的代码如下：

create table t_user_max_days as select id,max(length(cut_fsign_record)) as fmax_days from(select id,fsign_record,cut_fsign_record from (select id,group_concat(fis_sign_in) as fsign_record from t_user_attendance group by id) t1 lateral view explode(split_str(fsign_record,‘0’)) t as cut_fsign_record) t2where cut_sign_record'' group by id;-- 是！=的意思

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章