想爆头的数据统计

long_0662 2014-03-09 12:51:27
上班打卡时间 下班打卡时间
早班 7:00~8:00 15:55~16:30
中班 15:00~16:00 23:55~0:30
晚班 23:00~24:00 7:55~8:30
1、用上班卡匹配下班卡
2、上班取第一次打卡，下班取最后一次打卡
月数据量十分大(30W)用While统计，N久出不来。有什么好的方法？希望各位大神不吝赐教!
...全文
173 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
long_0662 2014-03-10
  • 打赏
  • 举报
 回复
感谢大家的支持！ 上班下班那些中文是我While语句更新进去的打卡有卡标识
LongRui888 2014-03-09
  • 打赏
  • 举报
 回复
没看明白，你要的结果是什么呢
极品老土豆 2014-03-09
  • 打赏
  • 举报
 回复 

--增加建议，在最后一行
--修改文本缩进
--sql server 2012
--如果表存在，就删除它
if object_id('AtRecord','U') is not null drop table AtRecord
create table AtRecord ( nameid char(4),workdate date,worktime time(0),flag varchar(6))
go
--插入数值
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','07:00:55','早班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','07:50:36','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','15:10:06','中班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','15:53:04','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','15:56:46','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','16:16:57','早班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','23:58:17','晚班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-27','00:17:38','中班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-27','07:58:21','晚班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-27','15:19:56','中班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-27','15:51:31','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-27','23:40:19','晚班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-28','00:20:42','中班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-28','07:55:44','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-28','08:11:43','晚班下') 

 --解决方案如下
 ;with workOn as 
 (
  select nameid,workdate,min(worktime) as  worktime,flag 
  from AtRecord  
  where substring(flag,3,1)='上' 
  group by nameid,workdate,flag

 ),
  workOff as 
  (
    select nameid,workdate,min(worktime) as  worktime,flag 
	from AtRecord   
	where substring(flag,3,1)='下' 
	group by nameid,workdate,flag
  )
  select * ,datediff(hour,cast(a.workdate as varchar)+' '+cast(a.worktime as varchar),cast(b.workdate as varchar)+' '+cast(b.worktime as varchar))  as workhours
  from workOn as a 
        inner join 
	WorkOff as b on a.nameid = b.nameid 
	         and datediff(hour,a.workdate,b.workdate)<=24 
			 and left(a.flag,2)=left(b.flag,2)
--结果展示
/*
nameid workdate   worktime         flag   nameid workdate   worktime         flag   workhours
------ ---------- ---------------- ------ ------ ---------- ---------------- ------ -----------
0002   2014-02-26 07:00:55         早班上    0002   2014-02-26 16:16:57         早班下    9
0002   2014-02-26 23:58:17         晚班上    0002   2014-02-27 07:58:21         晚班下    8
0002   2014-02-26 15:10:06         中班上    0002   2014-02-27 00:17:38         中班下    9
0003   2014-02-27 23:40:19         晚班上    0003   2014-02-28 08:11:43         晚班下    9
0003   2014-02-27 15:19:56         中班上    0003   2014-02-28 00:20:42         中班下    9

(5 行受影响)
*/
--月工作时长
 ;with workOn as 
 (
  select nameid,workdate,min(worktime) as  worktime,flag 
  from AtRecord  
  where substring(flag,3,1)='上' 
  group by nameid,workdate,flag

 ),
  workOff as 
  (
    select nameid,workdate,min(worktime) as  worktime,flag 
	from AtRecord   
	where substring(flag,3,1)='下' 
	group by nameid,workdate,flag
  )
  select a.nameid ,sum(datediff(hour,cast(a.workdate as varchar)+' '+cast(a.worktime as varchar),cast(b.workdate as varchar)+' '+cast(b.worktime as varchar)))  as workhours
   from workOn as a 
        inner join 
	WorkOff as b on a.nameid = b.nameid 
	         and datediff(hour,a.workdate,b.workdate)<=24 
			 and left(a.flag,2)=left(b.flag,2)
  group by a.nameid
  
  --结果展示
  /*
  nameid workhours
------ -----------
0002   26
0003   18

(2 行受影响)

  */
  --写在最后，1.5W人，一人一天之多上2班至少打4次卡，按每人一天打10次卡来算，一天15万数据，一月*30=450w数据，
  --我的建议是where添加一个条件len(flag)=3 筛选出来的数据就是4*15000*30=180万数据，就好筛选多了。
  --至于索引嘛，我感觉没什么好加的，你可以自己摸索
极品老土豆 2014-03-09
  • 打赏
  • 举报
 回复 

--修改文本缩进
--sql server 2012
--如果表存在，就删除它
if object_id('AtRecord','U') is not null drop table AtRecord
create table AtRecord ( nameid char(4),workdate date,worktime time(0),flag varchar(6))
go
--插入数值
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','07:00:55','早班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','07:50:36','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','15:10:06','中班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','15:53:04','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','15:56:46','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','16:16:57','早班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','23:58:17','晚班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-27','00:17:38','中班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-27','07:58:21','晚班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-27','15:19:56','中班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-27','15:51:31','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-27','23:40:19','晚班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-28','00:20:42','中班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-28','07:55:44','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-28','08:11:43','晚班下') 

 --解决方案如下
 ;with workOn as 
 (
  select nameid,workdate,min(worktime) as  worktime,flag 
  from AtRecord  
  where substring(flag,3,1)='上' 
  group by nameid,workdate,flag

 ),
  workOff as 
  (
    select nameid,workdate,min(worktime) as  worktime,flag 
	from AtRecord   
	where substring(flag,3,1)='下' 
	group by nameid,workdate,flag
  )
  select * ,datediff(hour,cast(a.workdate as varchar)+' '+cast(a.worktime as varchar),cast(b.workdate as varchar)+' '+cast(b.worktime as varchar))  as workhours
  from workOn as a 
        inner join 
	WorkOff as b on a.nameid = b.nameid 
	         and datediff(hour,a.workdate,b.workdate)<=24 
			 and left(a.flag,2)=left(b.flag,2)
--结果展示
/*
nameid workdate   worktime         flag   nameid workdate   worktime         flag   workhours
------ ---------- ---------------- ------ ------ ---------- ---------------- ------ -----------
0002   2014-02-26 07:00:55         早班上    0002   2014-02-26 16:16:57         早班下    9
0002   2014-02-26 23:58:17         晚班上    0002   2014-02-27 07:58:21         晚班下    8
0002   2014-02-26 15:10:06         中班上    0002   2014-02-27 00:17:38         中班下    9
0003   2014-02-27 23:40:19         晚班上    0003   2014-02-28 08:11:43         晚班下    9
0003   2014-02-27 15:19:56         中班上    0003   2014-02-28 00:20:42         中班下    9

(5 行受影响)
*/
--月工作时长
 ;with workOn as 
 (
  select nameid,workdate,min(worktime) as  worktime,flag 
  from AtRecord  
  where substring(flag,3,1)='上' 
  group by nameid,workdate,flag

 ),
  workOff as 
  (
    select nameid,workdate,min(worktime) as  worktime,flag 
	from AtRecord   
	where substring(flag,3,1)='下' 
	group by nameid,workdate,flag
  )
  select a.nameid ,sum(datediff(hour,cast(a.workdate as varchar)+' '+cast(a.worktime as varchar),cast(b.workdate as varchar)+' '+cast(b.worktime as varchar)))  as workhours
   from workOn as a 
        inner join 
	WorkOff as b on a.nameid = b.nameid 
	         and datediff(hour,a.workdate,b.workdate)<=24 
			 and left(a.flag,2)=left(b.flag,2)
  group by a.nameid
  --结果展示
  /*
  nameid workhours
------ -----------
0002   26
0003   18

(2 行受影响)

  */
极品老土豆 2014-03-09
  • 打赏
  • 举报
 回复 

--sql server 2012
--如果表存在，就删除它
if object_id('AtRecord','U') is not null drop table AtRecord
create table AtRecord ( nameid char(4),workdate date,worktime time(0),flag varchar(50))
go
--插入数值
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','07:00:55','早班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','07:50:36','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','15:10:06','中班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','15:53:04','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','15:56:46','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','16:16:57','早班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-26','23:58:17','晚班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-27','00:17:38','中班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0002','2014-02-27','07:58:21','晚班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-27','15:19:56','中班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-27','15:51:31','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-27','23:40:19','晚班上')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-28','00:20:42','中班下')
insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-28','07:55:44','-')
 insert into AtRecord(nameid,workdate,worktime,flag)
 values('0003','2014-02-28','08:11:43','晚班下') 

 --解决方案如下
 ;with workOn as 
 (
  select nameid,workdate,min(worktime) as  worktime,flag from AtRecord   where substring(flag,3,1)='上' group by nameid,workdate,flag

 ),
  workOff as 
  (
    select nameid,workdate,min(worktime) as  worktime,flag from AtRecord   where substring(flag,3,1)='下' group by nameid,workdate,flag
  )
  select * ,datediff(hour,cast(a.workdate as varchar)+' '+cast(a.worktime as varchar),cast(b.workdate as varchar)+' '+cast(b.worktime as varchar))  as workhours
  from workOn as a inner join WorkOff as b on a.nameid = b.nameid and datediff(hour,a.workdate,b.workdate)<=24 and left(a.flag,2)=left(b.flag,2)
--结果展示
/*
nameid workdate   worktime         flag                                               nameid workdate   worktime         flag                                               workhours
------ ---------- ---------------- -------------------------------------------------- ------ ---------- ---------------- -------------------------------------------------- -----------
0002   2014-02-26 07:00:55.0000000 早班上                                                0002   2014-02-26 16:16:57.0000000 早班下                                                9
0002   2014-02-26 23:58:17.0000000 晚班上                                                0002   2014-02-27 07:58:21.0000000 晚班下                                                8
0002   2014-02-26 15:10:06.0000000 中班上                                                0002   2014-02-27 00:17:38.0000000 中班下                                                9
0003   2014-02-27 23:40:19.0000000 晚班上                                                0003   2014-02-28 08:11:43.0000000 晚班下                                                9
0003   2014-02-27 15:19:56.0000000 中班上                                                0003   2014-02-28 00:20:42.0000000 中班下                                                9

(5 行受影响)
*/
--月工作时长
 ;with workOn as 
 (
  select nameid,workdate,min(worktime) as  worktime,flag from AtRecord   where substring(flag,3,1)='上' group by nameid,workdate,flag

 ),
  workOff as 
  (
    select nameid,workdate,min(worktime) as  worktime,flag from AtRecord   where substring(flag,3,1)='下' group by nameid,workdate,flag
  )
  select a.nameid ,sum(datediff(hour,cast(a.workdate as varchar)+' '+cast(a.worktime as varchar),cast(b.workdate as varchar)+' '+cast(b.worktime as varchar)))  as workhours
  from workOn as a inner join WorkOff as b on a.nameid = b.nameid and datediff(hour,a.workdate,b.workdate)<=24 and left(a.flag,2)=left(b.flag,2)
  group by a.nameid
  --结果展示
  /*
  nameid workhours
------ -----------
0002   26
0003   18

(2 行受影响)

  */
Q315054403 2014-03-09
  • 打赏
  • 举报
 回复
考勤应用，偶厂1.5W人，计算一次几分钟，考勤规则繁琐得超过一般预想
---涛声依旧--- 2014-03-09
  • 打赏
  • 举报
 回复
主要有时间段表、班次表、人员排班表、排班明细表、日考勤统计表、月考勤统计表
---涛声依旧--- 2014-03-09
  • 打赏
  • 举报
 回复
用存储过程来实现了 建议楼主可以参考中控的“考勤管理系统标准版”的考勤统计的存储过程了，楼主自己下载吧，免费的
long_0662 2014-03-09
  • 打赏
  • 举报
 回复
insert into AtRecord(nameid,workdate,worktime,flag) values('0002','2014-02-26','07:00:55','早班上') insert into AtRecord(nameid,workdate,worktime,flag) values('0002','2014-02-26','07:50:36','-') insert into AtRecord(nameid,workdate,worktime,flag) values('0002','2014-02-26','15:10:06','中班上') insert into AtRecord(nameid,workdate,worktime,flag) values('0002','2014-02-26','15:53:04','-') insert into AtRecord(nameid,workdate,worktime,flag) values('0002','2014-02-26','15:56:46','-') insert into AtRecord(nameid,workdate,worktime,flag) values('0002','2014-02-26','16:16:57','早班下') insert into AtRecord(nameid,workdate,worktime,flag) values('0002','2014-02-26','23:58:17','晚班上') insert into AtRecord(nameid,workdate,worktime,flag) values('0002','2014-02-27','00:17:38','中班下') insert into AtRecord(nameid,workdate,worktime,flag) values('0002','2014-02-27','07:58:21','晚班下') insert into AtRecord(nameid,workdate,worktime,flag) values('0003','2014-02-27','15:19:56','中班上') insert into AtRecord(nameid,workdate,worktime,flag) values('0003','2014-02-27','15:51:31','-') insert into AtRecord(nameid,workdate,worktime,flag) values('0003','2014-02-27','23:40:19','晚班上') insert into AtRecord(nameid,workdate,worktime,flag) values('0003','2014-02-28','00:20:42','中班下') insert into AtRecord(nameid,workdate,worktime,flag) values('0003','2014-02-28','07:55:44','-') insert into AtRecord(nameid,workdate,worktime,flag) values('0003','2014-02-28','08:11:43','晚班下')
LongRui888 2014-03-09
  • 打赏
  • 举报
 回复
引用 5 楼 long_0662 的回复:
flag列运算出来
这个数据，能不能生成那种insert语句呢，这个图没办法取数据
long_0662 2014-03-09
  • 打赏
  • 举报
 回复

flag列运算出来
LongRui888 2014-03-09
  • 打赏
  • 举报
 回复
引用 3 楼 long_0662 的回复:
统计员工出勤天数 表结构(工号，打卡日期及时间，打卡有效标识)
数据稍微详细一些，然后给出你最后要的统计结果
long_0662 2014-03-09
  • 打赏
  • 举报
 回复
统计员工出勤天数 表结构(工号，打卡日期及时间，打卡有效标识)
唐诗三百首 2014-03-09
  • 打赏
  • 举报
 回复
请问存打卡记录的表结构是什么?
CSGORankMeWeb：Rank Me CSGO统计跟踪插件的Web界面
CSGORankMeWeb RankMe CSGO统计跟踪插件的Web界面。 目的 此Web界面允许用户从运行RankMe插件发现反恐精英全球攻势服务器查看统计或遣的版本发现。 产品特点 按各种统计数据排序：击杀，时间，爆头，得分或任何其他跟踪的统计数据。 从任何运行RankMe的服务器上查看您的个人统计信息。 检查您想要在所寻找的服务器上播放的任何玩家的统计信息。 限制条件 到目前为止，主要的限制是仅支持mySQL而不支持SQLite。 我可能会开始开发一个单独的项目，以将SQLite转换为mySQL，以便人们可以根据需要走这条路。 我可能还会在当前项目中实现对SQLite的支持，敬请
魔兽怀旧服一个服务器最多人数,《魔兽世界》用数据告诉你，怀旧服到底有多少人排队...
原标题：《魔兽世界》用数据告诉你，怀旧服到底有多少人排队作者：NGA-akcjgogo最近这服务器排队排的简直让人受不了啊 挂着等排队的时候闲着无聊做了在线人数计算；STEP:1首先参考[范服人数统计结果 简单计算怀旧服人数上限]这帖的数据，感谢统计数据的大佬 [@闪光爆头王]这位大佬统计了3-4个小时我想还是比较准的吧 从28日晚开始统计的服务器人数，最后得出的结论是范克瑞斯联盟人数在8200左...
Rust游戏数据查询、Rust服务器清档时间表
Rust盒子“玩家查询”，除了支持玩家账号信息查询，封禁记录查询和好友信息查询外，新增了游戏数据统计查询功能等...
python每天20分钟_python20分钟入门
原子类型# 内建函数名不可以做变量名如sum，ordabs(-1) # 1d = dict() # d = {}空字典的构造l = list() # l = []s = set(list)t = tuple(list) # 不可以修改数据算法想爆头，没有理论def change(x,y,l):# 默认是传值,无论是可变还是不可变类型变量x = 0y = 0# l = [] # 赋值是指将一个内存...
python数据分析处理：PUBG Finish Placement Prediction
PUBG Finish Placement Prediction (Kernels Only) 比赛网址：https://www.kaggle.com/c/pubg-finish-placement-prediction/data 问题背景： 在PUBG游戏中，每场比赛最多有100名玩家，玩家可以根据在自己被淘汰时还有多少玩家活着从而获得比赛排名。 在游戏中，玩家可以拿起不同的武器攻击敌人，也可以...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章