mysql 考勤表异常 【待修改】
2024-10-20 13:52:49
有考勤刷卡记录表,表名为attendance ,有如下字段:
姓名 卡号 刷卡时间 刷卡类型
name
id
time
type
张三 59775623 2010-04-01 07:23:37
null
张三 59775623 2010-04-01 07:50:21
null
张三 59775623 2010-04-01 18:20:22
null
张三 59775623 2010-04-01 18:50:53
null
李四 59775624 2010-04-01 07:00:06
null
李四 59775624 2010-04-01 18:00:12
null
李四 59775624 2010-04-02 08:20:32
null
李四 59775624 2010-04-02 17:00:22
null
李四 59775624 2010-04-01 18:00:08
null
.....................................................................................................
以下还有很多,每位员工每天都有,...............
现在需要更新刷卡的数据,需要对表attendance执行一个
update
动作,根据刷卡时间,需满足如下功能
1.如果刷卡时间是8:00以前,则type的值
update
之后就为“上班”;
2.如果刷卡时间是17:30以后,则type的值
update
之后就为“下班”;
3.如果刷卡时间为8:00~~12:00之间,则type的值
update
之后就为“迟到”;
4.如果刷卡时间为13:00~~17:30之间,则type的值
update
之后就为“早退”;
5.如果同一个人同一天在12:00以前有多次刷卡,则刷卡时间最早的那一笔记录其type值为“上班”,其余12:00以前的刷卡记录其type值
update
之后,变为“上班重复刷卡;
6. 如果同一个人同一天在13:00以后有多次刷卡,则刷卡时间最迟的那一笔记录其type值为“下班”,其余13:00以后的刷卡记录其type值
update
之后,变为“下班重复刷卡;
7.其余每天的任何时间段,
update
后,type值变为“乱刷卡”
小弟最头痛的是其中的5、6两种情况,可以使用
where
+
group
by
+ haviing
count
(*)>1将其查出来,
update
就不知道如何处理了,小弟思考了好几天,也只能做到这一步,实在做不下去了,跑来求助各位达人;
问题补充:
1.请各位达人务必注意那个时间的格式,SQL里面转换时间格式可以使用
convert
(
char
(10),
time
,120),输出为YYYYMMDD;
convert
(
char
(8),
time
,112),输出格式为YYYYMMDD;
convert
(
char
(10),
time
,108),输出为HH-MM-SS
use test
go
if object_id(
'test.dbo.attendance'
)
is
not
null
drop
table
attendance
-- 创建数据表
create
table
attendance
(
name
char
(5),
id
int
,
time
datetime,
type
char
(20)
)
go
--插入测试数据
insert
into
attendance
select
'张三'
,59775623,
'2010-04-01 07:23:37'
,
null
union
all
select
'张三'
,59775623,
'2010-04-01 07:50:21'
,
null
union
all
select
'张三'
,59775623,
'2010-04-01 18:20:22'
,
null
union
all
select
'张三'
,59775623,
'2010-04-01 18:50:53'
,
null
union
all
select
'李四'
,59775624,
'2010-04-01 07:00:06'
,
null
union
all
select
'李四'
,59775624,
'2010-04-01 18:00:12'
,
null
union
all
select
'李四'
,59775624,
'2010-04-02 08:20:32'
,
null
union
all
select
'李四'
,59775624,
'2010-04-02 17:00:22'
,
null
union
all
select
'李四'
,59775624,
'2010-04-02 18:18:08'
,
null
union
all
select
'王五'
,59775625,
'2010-04-01 08:02:06'
,
null
union
all
select
'王五'
,59775625,
'2010-04-01 18:00:12'
,
null
union
all
select
'王五'
,59775625,
'2010-04-02 07:20:32'
,
null
union
all
select
'王五'
,59775625,
'2010-04-02 12:35:22'
,
null
union
all
select
'王五'
,59775625,
'2010-04-02 18:18:08'
,
null
go
*/
-->更新数据
update
attendance
set
type=t2.type
from
attendance t1
inner
join
(
select
name
,id,
time
=_time,type=
case
when
time
<=
'08:00'
and
idd=1
then
'上班'
when
time
>
'08:00'
and
time
<=
'12:00'
and
idd=1
then
'迟到'
when
time
<
'12:00'
and
idd<>1
then
'上班重复刷卡'
when
time
>=
'13:00'
and
time
<=
'17:30'
and
idd=1
then
'早退'
when
time
>
'17:30'
and
idd=1
then
'下班'
when
time
>
'13:00'
and
idd<>1
then
'下班重复刷卡'
when
time
>=
'12:00'
and
time
<=
'13:00'
then
'乱刷卡'
end
from
(
select
name
,id,_time=
time
,
time
=
convert
(
varchar
(5),
time
,8),type,
idd=row_number()over(partition
by
convert
(
varchar
(10),
time
,120),
name
order
by
time
)
from
attendance
where
convert
(
varchar
(5),
time
,8)<=
'12:00'
union
all
select
name
,id,_time=
time
,
time
=
convert
(
varchar
(5),
time
,8),type,
idd=row_number()over(partition
by
convert
(
varchar
(10),
time
,120),
name
order
by
time
)
from
attendance
where
convert
(
varchar
(5),
time
,8)>=
'13:00'
union
all
select
name
,id,_time=
time
,
time
=
convert
(
varchar
(5),
time
,8),type,idd=0
from
attendance
where
convert
(
varchar
(5),
time
,8)>=
'12:00'
and
convert
(
varchar
(5),
time
,8)<=
'13:00'
)t
) t2
on
t1.id=t2.id
and
t1.
time
=t2.
time
-->显示更新后数据
select
*
from
attendance
/*测试结果
name
id
time
type
--------------------------------------------------------------
张三 59775623 2010-04-01 07:23:37.000 上班
张三 59775623 2010-04-01 07:50:21.000 上班重复刷卡
张三 59775623 2010-04-01 18:20:22.000 下班
张三 59775623 2010-04-01 18:50:53.000 下班重复刷卡
李四 59775624 2010-04-01 07:00:06.000 上班
李四 59775624 2010-04-01 18:00:12.000 下班
李四 59775624 2010-04-02 08:20:32.000 迟到
李四 59775624 2010-04-02 17:00:22.000 早退
李四 59775624 2010-04-02 18:18:08.000 下班重复刷卡
王五 59775625 2010-04-01 08:02:06.000 迟到
王五 59775625 2010-04-01 18:00:12.000 下班
王五 59775625 2010-04-02 07:20:32.000 上班
王五 59775625 2010-04-02 12:35:22.000 乱刷卡
王五 59775625 2010-04-02 18:18:08.000 下班
(14 行受影响)
*/
最新文章
- Qt 程序访问 sqlite 权限错误
- java集合类的学习总结一
- OEM status|start|stop
- Andriod中WebView加载登录界面获取Cookie信息并同步保存,使第二次不用登录也可查看个人信息。
- 转:C#: static关键字的作用
- OpenVPN多处理之-netns容器与iptables CLUSTER
- 编写原生的Node.js模块
- Excel 2010高级应用-气泡图(八)
- DS1-13
- day8数据类型补充,集合,深浅拷贝
- getchar getche getch
- 【 Gym - 101138F 】GukiZ Height (数学)
- 如何清空iframe中的内容?
- 内部排序->;插入排序->;希尔排序
- yii NAV x下拉
- ejb servlet demo
- linux上,mysql使用聚合函数group by 时报错:SELECT list is not in GROUP BY clause and contains nonaggre的问题
- http跳转https方法:百度云如何让http自动跳转到https【免费SSL证书使用FAQ】
- sails ORM组件 Waterline v0.10 英文文档
- BZOJ4811 Ynoi2017由乃的OJ(树链剖分+线段树)
热门文章
- 玩转Nuget服务器搭建(一)
- 怎样设计REST中间件---中间件JSON对数据库数据的组织
- SpannableString 转换局部字体大小,但在EditText测量之前设置内容,测量高度为,字体变小之前的高度
- hdu1021(C++)
- DatagramPacket,DatagramSocket
- [Debug] Diagnose a Slow Page Using Developer Tools
- 关于ng-router嵌套使用和总结
- linux命令之------vmstat使用
- 标准库Allocator的简易实现(二)
- UISearchBar 点击取消回到原来位置时会跳动的解决方法