统计上下班时间的数据库筛选问题

肛肛 2012-09-23 11:49:32
各位大大我语言表达能力比较差希望大家不要介意- -

情况如下：
表的三列为 [id] [time] [device]分别表示工号，打卡时间，打卡设备号（1为上班，2为下班），其中打卡设备号用于区分上班和下班的打卡时间，目前我得到了持续一个月的所有员工的全部打卡记录，都记录在这个表中，现在我想通过sql的筛选计算功能得到类似下面列的新表：
[id] [InTime] [OutTime] [LT]分别表示工号，上班时间，下班时间，在岗时间（也就是下班减去上班），但由于情况特殊，员工的上下班并没有确切的时间段，24小时内都有人上下班，不知道有没有解决方案呢？
...全文
329 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
DBA_磊仔 2012-09-23
  • 打赏
  • 举报
 回复 


select a.id,a.time as Intime, b.Time as OutTime, 

    datediff(ss/*以秒记*/,a.time,b.time) as LT 

from tb a cross apply 

  (select top (1)* from tb b where a.ID = b.ID and b.time > a.time 

     and b.device = 2 order by b.time desc)p 

where a.device = 1
DBA_磊仔 2012-09-23
  • 打赏
  • 举报
 回复 


select a.id,a.time as Intime, b.Time as OutTime, datediff(ss/*以秒记*/,a.time,b.time) as LT from tb a cross apply (select top (1)* from tb b where a.ID = b.ID and b.time > a.time and b.device = 2 order by b.time desc)p where a.device = 1
肛肛 2012-09-23
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 的回复:]
引用 13 楼 的回复:
误删是因为我没有对工号进行分组，将工号加到partition by a.time a,工号 order by 这样
[/Quote]
这个问题我把原先的分组条件改为partition by a.time order by b.time desc就解决了，分析原因应该是由于在每次都取了距离上班打卡时间最近的下班打卡时间，因而不会出现一个上班时间和多个下班时间的情况，现在问题已经圆满解决，谢谢磊仔和DBA_Huangzj，前者提供了我思路（作为初学者看到他的with as不明白什么意思去查了才知道还有这么方便的功能，这也是这段语句可以最终完成的最大功劳），而DBA_Huangzj给出的时间统计也很有帮助
p.s k_enny_的语句我也会再仔细研究一下，争取能学到更多，谢谢大家，结贴~
onlykenny 2012-09-23
  • 打赏
  • 举报
 回复 


select a.id ,b.Intime,b.OutTime,

datediff(s,b.OutTime,b.Intime) as LT 

from #a as a cross apply(

select case when device=1 then max(time) end as Intime,

case when device=2 then min(time) end as  OutTime from #a where id =a.id group by device ) as b

额。上面的有点错误，补下这个看看
DBA_磊仔 2012-09-23
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 的回复:]
id time device
1 2012-09-23 08:00:00.000 1
2 2012-09-23 08:01:00.000 1
3 2012-09-23 08:02:00.000 1
1 2012-09-23 09:00:00.000 2
2 2012-09-23 09:10:00.000 2
3 2012-09-23 09:20:00.000 2
1 2012-09-……
[/Quote]误删是因为我没有对工号进行分组，将工号加到partition by a.time a,工号 order by 这样
onlykenny 2012-09-23
  • 打赏
  • 举报
 回复 


select #a.id ,b.Intime,b.OutTime,

datediff(s,b.OutTime,b.Intime) as LT 

from #a cross join(

select case when device=1 then max(time) end as Intime,

case when device=2 then min(time) end as  OutTime from #a group by device) as b
DBA_磊仔 2012-09-23
  • 打赏
  • 举报
 回复
我们这样举例，只是为了给你一个思路，不要急着套进去然后运行，报了错就把代码否决了，先看懂代码要实现的逻辑是什么
DBA_磊仔 2012-09-23
  • 打赏
  • 举报
 回复 
with CET AS (select a.id,a.time as Intime, b.Time as OutTime, 

    datediff(ss/*以秒记*/,a.time,b.time) as LT ,row_number()over(partition by a.time order by b.time) as Brn, row_number()over(partition by b.time order by a.time) as Ern



from tb a cross apply 

  (select top (1)* from tb b where a.ID = b.ID and b.time > a.time 

     and b.device = 2 order by b.time )b--错是因为这个别名 不小心打成P了，desc确实应该删掉，across apply的意思是对每一行进行计算 

where a.device = 1 )

select * from CET where Brn = 1 and Ern = 1
肛肛 2012-09-23
  • 打赏
  • 举报
 回复
补充下磊仔的cross apply语句后面的内容中，desc应该删掉 添加之后就成了时间从大到小排序导致出错
肛肛 2012-09-23
  • 打赏
  • 举报
 回复
id time device
1 2012-09-23 08:00:00.000 1
2 2012-09-23 08:01:00.000 1
3 2012-09-23 08:02:00.000 1
1 2012-09-23 09:00:00.000 2
2 2012-09-23 09:10:00.000 2
3 2012-09-23 09:20:00.000 2
1 2012-09-23 09:30:00.000 1
1 2012-09-23 09:40:00.000 2
2 2012-09-23 09:50:00.000 1
2 2012-09-23 09:55:00.000 2
1 2012-09-23 08:01:00.000 1
1 2012-09-23 08:02:00.000 1
为了简单我自己写了一段数据作为参考
根据上面磊仔8楼的cet语句运行结果如下
id intime outtime LT Brn Ern
2 2012-09-23 09:50:00.000 2012-09-23 09:55:00.000 300 1 1
1 2012-09-23 09:30:00.000 2012-09-23 09:40:00.000 600 1 1
3 2012-09-23 08:02:00.000 2012-09-23 09:20:00.000 4680 2 1
2 2012-09-23 08:01:00.000 2012-09-23 09:10:00.000 4140 2 1
1 2012-09-23 08:00:00.000 2012-09-23 09:00:00.000 3600 1 1
1 2012-09-23 08:01:00.000 2012-09-23 09:00:00.000 3540 1 2
1 2012-09-23 08:02:00.000 2012-09-23 09:00:00.000 3480 1 3
我觉得他的思路已经可以解决我的问题了，但是很奇怪的是id为3和2的两次上下班的brn值不是1，导致最终被删除掉了，原因不明我也正在自己找
發糞塗牆 2012-09-23
  • 打赏
  • 举报
 回复
cross apply你可以去查联机丛书
發糞塗牆 2012-09-23
  • 打赏
  • 举报
 回复
我觉得你最好给出两个人的数据来看看，因为我的代码是自己造数据，不同的数据有不同的写法，针对你的数据来写才有意义
發糞塗牆 2012-09-23
  • 打赏
  • 举报
 回复
我觉得你最好给出两个人的数据来看看，因为我的代码是自己造数据，不同的数据有不同的写法，针对你的数据来写才有意义
肛肛 2012-09-23
  • 打赏
  • 举报
 回复
楼上这个运行了还是报错
无法绑定由多个部分组成的标识符 "b.time"
这个搞不定啊- -
另外cross apply这个作用我没有弄明白，求说明呀~
DBA_磊仔 2012-09-23
  • 打赏
  • 举报
 回复
如果重复打卡，会出现一个开始时间有多个结束时间，或者一个结束时间 有多个开始时间，这样的只取第一个


with CET AS (select a.id,a.time as Intime, b.Time as OutTime, 

    datediff(ss/*以秒记*/,a.time,b.time) as LT ,row_number()over(partition by a.time order by b.time) as Brn, row_number()over(partition by b.time order by a.time) as Ern



from tb a cross apply 

  (select top (1)* from tb b where a.ID = b.ID and b.time > a.time 

     and b.device = 2 order by b.time desc)p 

where a.device = 1 )

select * from CET where Brn = 1 and Ern = 1
肛肛 2012-09-23
  • 打赏
  • 举报
 回复
谢谢楼上，我运行之后出现了负数时间，并且只出现了最近的上下班记录，同一个员工之前的记录全部丢失了，虽然语句我没有完全看懂，但是猜想是InTime=MAX这类语句所造成的，不过您这个时间统计非常好，学习了
DBA_磊仔 2012-09-23
  • 打赏
  • 举报
 回复 


select a.id,a.time as Intime, b.Time as OutTime, 

    datediff(ss/*以秒记*/,a.time,b.time) as LT 

from tb a cross apply 

  (select top (1)* from tb b where a.ID = b.ID and b.time > a.time 

     and b.device = 2 order by b.time desc)b 

where a.device = 1
發糞塗牆 2012-09-23
  • 打赏
  • 举报
 回复 
WITH test (id,TIME,device)

 AS 

 (

 SELECT 1,'2012-09-23 12:10:01',1

 UNION ALL 

 SELECT 1,'2012-09-24 12:10:01',2 --模拟跨天

 UNION ALL 

 SELECT 2,'2012-09-23 12:10:01',1

 UNION ALL 

 SELECT 2,'2012-09-23 14:10:01',2 --模拟当天

 )

 --SELECT * FROM test 

 

 SELECT id,InTime=MAX(CASE WHEN device=1 THEN TIME END ),OutTime=MAX(CASE WHEN device=2 THEN TIME END ),

 CASE WHEN DATEPART(DAY,MAX(CASE WHEN device=1 THEN TIME END ))=DATEPART(DAY,MAX(CASE WHEN device=2 THEN TIME END )) 

 THEN CONVERT(VARCHAR(10),DATEDIFF (hh,MAX(CASE WHEN device=1 THEN TIME END ),MAX(CASE WHEN device=2 THEN TIME END)))+'小时'

 + CONVERT(VARCHAR(10),DATEDIFF (mi,DATEPART(mi,MAX(CASE WHEN device=1 THEN TIME END )),DATEPART(mi,MAX(CASE WHEN device=2 THEN TIME END))))

 +'分'+CONVERT(VARCHAR(10),DATEDIFF (ss,DATEPART(ss,MAX(CASE WHEN device=1 THEN TIME END )),DATEPART(ss,MAX(CASE WHEN device=2 THEN TIME END))))+'秒' 

 

 ELSE  CONVERT(VARCHAR(10),DATEDIFF (hh,MAX(CASE WHEN device=1 THEN TIME END ),MAX(CASE WHEN device=2 THEN TIME END)))+'小时'

 + CONVERT(VARCHAR(10),DATEDIFF (mi,DATEPART(mi,MAX(CASE WHEN device=1 THEN TIME END )),DATEPART(mi,MAX(CASE WHEN device=2 THEN TIME END))))

 +'分'+CONVERT(VARCHAR(10),DATEDIFF (ss,DATEPART(ss,MAX(CASE WHEN device=1 THEN TIME END )),DATEPART(ss,MAX(CASE WHEN device=2 THEN TIME END))))+'秒'

 END 

 FROM test 

 GROUP BY id

 

 /*

 ----------- ------------------- ------------------- --------------------------------------

 1           2012-09-23 12:10:01 2012-09-24 12:10:01 24小时0分0秒

 2           2012-09-23 12:10:01 2012-09-23 14:10:01 2小时0分0秒

 警告: 聚合或其他 SET 操作消除了 Null 值。

 

 (2 行受影响)

 */
肛肛 2012-09-23
  • 打赏
  • 举报
 回复
另外补充一下问题，就是发现原表中存在重复打卡的记录，原因应该是员工连续进行了两次打卡，导致有时间间隔很短的同ID，同设备号记录，这样一来按上面那位的思路就会出现重复统计的数据（例如两次进门打卡导致最终表中出现相同下班时间不同上班时间的记录） 求大家帮忙~
肛肛 2012-09-23
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 的回复:]
SQL code

select a.id,a.time as Intime, b.Time as OutTime,
datediff(ss/*以秒记*/,a.time,b.time) as LT
from tb a cross apply
(select top (1)* from tb b where a.ID = b.ID and b.time > a.time ……
[/Quote]
谢谢大大，我跑了一下提示说无法绑定多个部分组成的标示符b.time
这语句的意思我大概明白就是先根据device=1查找出上班时间与id，然后反查下班时间的，只是
from tb a cross apply 这句我看不懂，不直到是什么意思，应该怎么改呢？
【SQL】从一张考勤表tb_cwa中找出员工每天的上班、下班打卡具体时间
从一张考勤表tb_cwa中找出员工每天的上班、下班打卡具体时间 考勤表相关字段如下下：ID、NAME、NO、TIME ID NAME NO TIME 1 zhangsan 1001 2020-01-01 08:34:40 2 lisi 1002 2020-01-01 08:39:29 3 wa...
查询数据库中的重复数据记录
SELECT a.*, dlookup("r1","表2","id=" & a.id & ...有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
数据库的简单运用
在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。其实，目前关系型数据库的具体产品有：Oracle和MySQL。其中Oracle在...
职工考勤管理信息系统数据库课设_基于数据库的员工考勤系统设计与实现
聂毓谣摘要：随着企业的规模不断扩大，企业人事管理的日趋复杂和企业人员的增多，而目前许企业管理仍停留在人工管理阶段，重复工作较多、工作量大、效率低、不易统计、成本高和易出错等弊端，已经无法适应现代企业的...
利用临床数据库就能发SCI？看完这篇！直呼做临床数据分析超简单
隔壁科室赵主任又双叒叕利用公开临床数据库发了一篇SCI，这已经是他利用公开临床数据库发的第三篇SCI了。为啥他发SCI就那么容易？用了他推荐的方法后，今年九月，我利用临床数据库写的论文也接收啦，终于符合职称...
应用实例

27,580

社区成员

68,556

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章