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

2012-09-23 11:49:32 456 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

数据仓库综述本文描述了大数据，数据建仓的一些基本概念，以及每个部分的一些常用工具阅读详情

各位大大我语言表达能力比较差希望大家不要介意- -

情况如下：
表的三列为 [id] [time] [device]分别表示工号，打卡时间，打卡设备号（1为上班，2为下班），其中打卡设备号用于区分上班和下班的打卡时间，目前我得到了持续一个月的所有员工的全部打卡记录，都记录在这个表中，现在我想通过sql的筛选计算功能得到类似下面列的新表：
[id] [InTime] [OutTime] [LT]分别表示工号，上班时间，下班时间，在岗时间（也就是下班减去上班），但由于情况特殊，员工的上下班并没有确切的时间段，24小时内都有人上下班，不知道有没有解决方案呢？

...全文

数据仓库概述数据仓库概述数据仓库（Data Warehouse），简称DW或DWH，是一个面向主题的（Subject Oriented）、集成的（Integrate）、相对稳定的（Non-Volatile）、反应历史变化（Time Variant）的数据集合，用于支持管理、运营决策。面对主题的：数据仓库里的数据面向主题作为大分类，例如用户分析主题、流量分析主题等等。这种主题往往也是我们处理数据的目的。集成的：数据仓库的数据不只是通常数据库表里写下的业务数据，也记录下了用户行为相关的埋点数据等，例如用户访问的日志阅读详情

肛肛

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

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 这句我看不懂，不直到是什么意思，应该怎么改呢？

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

相关推荐