sql replace函数用法_两道精彩的SQL练习题

大家晚上好，我是阿涛。

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

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

select * from test.a1;

数据是这样的：

答案：

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= '男','女','男');

结果：

补充知识：

这里用到的是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;

数据是这样的：

答案：

问题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 ;

结果：

得到员工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汇总到一个字符串

结果：

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;-- <>是！=的意思

往期推荐阅读 ▽白话MCMC爬虫之scrapy框架极速可视化BI——TableauSQL存储过程SQL窗口函数MYSQL 49问

End

作者：涛网站：http://atshare.top/

半壶水全栈工程师，好读书，甚喜之