如何查询代打卡嫌疑人清单

sun8330 2012-06-18 03:07:29
考勤系统原始打卡记录表结构如下:
WORKRECO (WKR_INCOD char(7) NOT NULL,--工号
WKR_DATTIM smalldatetime NOT NULL, --刷卡时间
WKR_CACS varchar(3) NOT NULL) --刷卡机器号

问题：如何查询两天以上(含两天)，刷卡时间与刷卡机器号相同的员工,显示结果最好有如下栏位:

工号１,工号２，起始日期，截止日期
...全文
522 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
sun8330 2012-06-19
  • 打赏
  • 举报
 回复
上面两位高手，我用的是sql2000,有些指令用不了，我还在测试，如果方便的话，能否改成sql2000的指令，方便我测试一下，谢谢!
开着拖拉机泡妞 2012-06-18
  • 打赏
  • 举报
 回复 


--> 测试数据：[WORKRECO]

if object_id('[WORKRECO]') is not null 

drop table [WORKRECO]

go

create table [WORKRECO](

[WKR_INCOD] varchar(5),

[WKR_DATTIM] datetime,

[WKR_CACS] varchar(3)

)

go

insert [WORKRECO]

select 'B0001','2012-06-01 07:58:00','001' union all

select 'B0002','2012-06-01 07:58:00','001' union all

select 'B0003','2012-06-01 07:50:00','001' union all

select 'B0001','2012-06-01 12:01:00','002' union all

select 'B0002','2012-06-01 12:01:00','002' union all

select 'B0003','2012-06-01 12:03:00','002' union all

select 'B0001','2012-06-01 13:29:00','002' union all

select 'B0002','2012-06-01 13:29:00','002' union all

select 'B0003','2012-06-01 13:28:00','001' union all

select 'B0001','2012-06-01 17:35:00','001' union all

select 'B0002','2012-06-01 17:35:00','001' union all

select 'B0003','2012-06-01 17:30:00','001' union all

select 'B0001','2012-06-02 07:58:00','001' union all

select 'B0002','2012-06-02 07:58:00','001' union all

select 'B0003','2012-06-02 07:50:00','001' union all

select 'B0001','2012-06-02 12:01:00','002' union all

select 'B0002','2012-06-02 12:01:00','002' union all

select 'B0003','2012-06-02 12:03:00','002' union all

select 'B0001','2012-06-02 13:29:00','002' union all

select 'B0002','2012-06-02 13:29:00','002' union all

select 'B0003','2012-06-02 13:28:00','001' union all

select 'B0001','2012-06-02 17:35:00','001' union all

select 'B0002','2012-06-02 17:35:00','001' union all

select 'B0003','2012-06-02 17:30:00','001' union all

select 'B0001','2012-06-03 07:58:00','001' union all

select 'B0002','2012-06-03 07:58:00','001' union all

select 'B0003','2012-06-03 07:50:00','001' union all

select 'B0001','2012-06-03 12:01:00','002' union all

select 'B0002','2012-06-03 12:01:00','002' union all

select 'B0003','2012-06-03 12:03:00','002' union all

select 'B0001','2012-06-03 13:29:00','002' union all

select 'B0002','2012-06-03 13:29:00','002' union all

select 'B0003','2012-06-03 13:28:00','001' union all

select 'B0001','2012-06-03 17:35:00','001' union all

select 'B0002','2012-06-03 17:35:00','001' union all

select 'B0003','2012-06-03 17:30:00','001'

go



--问题：如何查询两天以上(含两天)，刷卡时间与刷卡机器号相同的员工,显示结果最好有如下栏位

;with t

as(

select 

    px=ROW_NUMBER()over(partition by [WKR_INCOD] order by [WKR_DATTIM]),

    de=convert(varchar(10),dateadd(dd,-DENSE_RANK()over(partition by [WKR_INCOD] order by convert(varchar(10),[WKR_DATTIM],120)),[WKR_DATTIM]),120),

    [WKR_INCOD],

    [WKR_DATTIM],

    [WKR_CACS]

from 

    [WORKRECO]

)

select distinct  

    case when a.WKR_INCOD<b.WKR_INCOD then a.WKR_INCOD else b.WKR_INCOD end as 工号一,

    case when a.WKR_INCOD>b.WKR_INCOD then a.WKR_INCOD else b.WKR_INCOD end as 工号二, 

    convert(varchar(10),MIN(a.WKR_DATTIM),120) as 起始时间,

    convert(varchar(10),MAX(b.WKR_DATTIM),120) as 结束时间

from 

    t a,t b 

where 

    a.de=b.de 

    and a.px=b.px 

    and a.WKR_CACS=b.WKR_CACS 

    and a.WKR_INCOD<>b.WKR_INCOD 

    and a.WKR_DATTIM=b.WKR_DATTIM

group by 

    a.WKR_INCOD,b.WKR_INCOD,a.de

having

    DATEDIFF(DD,MIN(a.WKR_DATTIM),MAX(b.WKR_DATTIM))>=2

/*

工号一	工号二	起始时间	结束时间

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

B0001	B0002	2012-06-01	2012-06-03

*/
孤独加百列 2012-06-18
  • 打赏
  • 举报
 回复
写死我了，希望你看得懂。。。


IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'WORKRECO')

BEGIN

	DROP TABLE WORKRECO

END

GO

CREATE TABLE WORKRECO 

(

	WKR_INCOD char(7) NOT NULL,--工号

	WKR_DATTIM smalldatetime NOT NULL, --刷卡时间

	WKR_CACS varchar(3) NOT NULL

) --刷卡机器号

GO

INSERT INTO WORKRECO

SELECT 'B0001','2012-06-01 07:58:00','001' UNION

SELECT 'B0002','2012-06-01 07:58:00','001' UNION

SELECT 'B0003','2012-06-01 07:50:00','001' UNION

SELECT 'B0001','2012-06-01 12:01:00','002' UNION

SELECT 'B0002','2012-06-01 12:01:00','002' UNION

SELECT 'B0003','2012-06-01 12:03:00','002' UNION

SELECT 'B0001','2012-06-01 13:29:00','002' UNION

SELECT 'B0002','2012-06-01 13:29:00','002' UNION

SELECT 'B0003','2012-06-01 13:28:00','001' UNION

SELECT 'B0001','2012-06-01 17:35:00','001' UNION

SELECT 'B0002','2012-06-01 17:35:00','001' UNION

SELECT 'B0003','2012-06-01 17:30:00','001' UNION



SELECT 'B0001','2012-06-02 07:58:00','001' UNION

SELECT 'B0002','2012-06-02 07:58:00','001' UNION

SELECT 'B0003','2012-06-02 07:50:00','001' UNION

SELECT 'B0001','2012-06-02 12:01:00','002' UNION

SELECT 'B0002','2012-06-02 12:01:00','002' UNION

SELECT 'B0003','2012-06-02 12:03:00','002' UNION

SELECT 'B0001','2012-06-02 13:29:00','002' UNION

SELECT 'B0002','2012-06-02 13:29:00','002' UNION

SELECT 'B0003','2012-06-02 13:28:00','001' UNION

SELECT 'B0001','2012-06-02 17:35:00','001' UNION

SELECT 'B0002','2012-06-02 17:35:00','001' UNION

SELECT 'B0003','2012-06-02 17:30:00','001' UNION





SELECT 'B0001','2012-06-03 07:58:00','001' UNION

SELECT 'B0002','2012-06-03 07:58:00','001' UNION

SELECT 'B0003','2012-06-03 07:50:00','001' UNION

SELECT 'B0001','2012-06-03 12:01:00','002' UNION

SELECT 'B0002','2012-06-03 12:01:00','002' UNION

SELECT 'B0003','2012-06-03 12:03:00','002' UNION

SELECT 'B0001','2012-06-03 13:29:00','002' UNION

SELECT 'B0002','2012-06-03 13:29:00','002' UNION

SELECT 'B0003','2012-06-03 13:28:00','001' UNION

SELECT 'B0001','2012-06-03 17:35:00','001' UNION

SELECT 'B0002','2012-06-03 17:35:00','001' UNION

SELECT 'B0003','2012-06-03 17:30:00','001'

GO



WITH t AS

(SELECT WKR_INCOD,CONVERT(VARCHAR(10),WKR_DATTIM,23) AS date,COUNT(1) AS num

FROM WORKRECO AS A

WHERE EXISTS (SELECT 1 FROM WORKRECO AS B WHERE A.WKR_INCOD <> B.WKR_INCOD AND A.WKR_DATTIM = B.WKR_DATTIM AND A.WKR_CACS = B.WKR_CACS)

GROUP BY WKR_INCOD,CONVERT(VARCHAR(10),WKR_DATTIM,23)),

m AS

(

SELECT WKR_INCOD,CONVERT(VARCHAR(10),WKR_DATTIM,23) AS Date,COUNT(1) AS num

FROM WORKRECO AS A

GROUP BY WKR_INCOD,CONVERT(VARCHAR(10),WKR_DATTIM,23)),

n AS

(

SELECT A.WKR_INCOD AS AWKR_INCOD,B.WKR_INCOD AS BWKR_INCOD,A.date

FROM t AS A,m AS B

WHERE A.WKR_INCOD <> B.WKR_INCOD AND A.num = B.num AND A.date = B.Date AND B.WKR_INCOD IN (SELECT WKR_INCOD FROM t WHERE num = B.num AND t.date = B.Date)

),

x AS

(

SELECT AWKR_INCOD,BWKR_INCOD,MIN(date) AS BEGINDATE,MAX(date) AS ENDDATE,COUNT(1) AS num

FROM n

GROUP BY AWKR_INCOD,BWKR_INCOD

HAVING COUNT(1) > 1 AND DATEDIFF(DAY,MIN(date),MAX(date)) + 1 = COUNT(1)

)



SELECT DISTINCT CASE WHEN AWKR_INCOD < BWKR_INCOD THEN AWKR_INCOD

       ELSE BWKR_INCOD END AS AWKR_INCOD,

       CASE WHEN AWKR_INCOD < BWKR_INCOD THEN BWKR_INCOD

       ELSE AWKR_INCOD END AS BWKR_INCOD,BEGINDATE,ENDDATE

FROM x



AWKR_INCOD	BWKR_INCOD	BEGINDATE	ENDDATE

B0001  	B0002  	2012-06-01	2012-06-03

应该中间步骤可以合并的，酌情处理下吧。。。
sun8330 2012-06-18
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 的回复:]
假如有三个人是一样的你又怎么处理？？？
工号１,工号２，起始日期，截止日期
那么三哥一样的时候还有一个工号怎么办？？？？
[/Quote]

你这个问题问得很有道理，我也想过，如果能用一个栏位的字符串也很好，这样就没有这个问题了，如果能输出这样的结果，也算ok,同样会给分，谢谢!
开着拖拉机泡妞 2012-06-18
  • 打赏
  • 举报
 回复
假如有三个人是一样的你又怎么处理？？？
工号１,工号２，起始日期，截止日期
那么三哥一样的时候还有一个工号怎么办？？？？
sun8330 2012-06-18
  • 打赏
  • 举报
 回复
考勤系统原始打卡记录表WORKRECO结构如下:
表名:WORKRECO
栏位说明:(WKR_INCOD char(7) NOT NULL,--工号
WKR_DATTIM smalldatetime NOT NULL, --刷卡时间
WKR_CACS varchar(3) NOT NULL) --刷卡机器号

问题：如何查询连续两天以上(含两天)，刷卡时间与刷卡机器号相同的员工,显示结果最好有如下栏位:

工号１,工号２，起始日期，截止日期

测试数据:
B0001,2012-06-01 07:58:00,001
B0002,2012-06-01 07:58:00,001
B0003,2012-06-01 07:50:00,001
B0001,2012-06-01 12:01:00,002
B0002,2012-06-01 12:01:00,002
B0003,2012-06-01 12:03:00,002
B0001,2012-06-01 13:29:00,002
B0002,2012-06-01 13:29:00,002
B0003,2012-06-01 13:28:00,001
B0001,2012-06-01 17:35:00,001
B0002,2012-06-01 17:35:00,001
B0003,2012-06-01 17:30:00,001

B0001,2012-06-02 07:58:00,001
B0002,2012-06-02 07:58:00,001
B0003,2012-06-02 07:50:00,001
B0001,2012-06-02 12:01:00,002
B0002,2012-06-02 12:01:00,002
B0003,2012-06-02 12:03:00,002
B0001,2012-06-02 13:29:00,002
B0002,2012-06-02 13:29:00,002
B0003,2012-06-02 13:28:00,001
B0001,2012-06-02 17:35:00,001
B0002,2012-06-02 17:35:00,001
B0003,2012-06-02 17:30:00,001


B0001,2012-06-03 07:58:00,001
B0002,2012-06-03 07:58:00,001
B0003,2012-06-03 07:50:00,001
B0001,2012-06-03 12:01:00,002
B0002,2012-06-03 12:01:00,002
B0003,2012-06-03 12:03:00,002
B0001,2012-06-03 13:29:00,002
B0002,2012-06-03 13:29:00,002
B0003,2012-06-03 13:28:00,001
B0001,2012-06-03 17:35:00,001
B0002,2012-06-03 17:35:00,001
B0003,2012-06-03 17:30:00,001


记住，一定要连续，如果一天有一次不相同就不要抓

最后的查询结果应该是:
B0001 B0002 2012-06-01 2012-06-03
表示B0001与B0002在6/1号与6/3号三天的打卡一样

sun8330 2012-06-18
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]
SQL code


select
*
from
tb t
where
exists(select 1 from tb where workeco<>t.workeco and WKR_CACS=t.WKR_CACS and datediff(dd,WKR_DATTIM,tWKR_DATTIM)>=2) and convert(varchar(10),WKR_DATTIM……
[/Quote]
逻辑明显不对，是要查询连续两天以上的打卡完全一样的员工，不是相差两天
--小F-- 2012-06-18
  • 打赏
  • 举报
 回复 
select

 * 

from

 tb t 

where

 exists(select 1 from tb where workeco<>t.workeco and WKR_CACS=t.WKR_CACS  and convert(varchar(10),WKR_DATTIM,108)=convert(varchar(10),t.WKR_DATTIM,108)

and

 WKR_CACS in(select WKR_CACS from tb group by WKR_CACS having count(1)>1)
--小F-- 2012-06-18
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]
SQL code

select
*
from
tb t
where
exists(select 1 from tb where workeco<>t.workeco and WKR_CACS=t.WKR_CACS and datediff(dd,WKR_DATTIM,tWKR_DATTIM)>=2) and convert(varchar(10),WKR_DATTIM,1……
[/Quote]

我错误的理解了两天以上。
开着拖拉机泡妞 2012-06-18
  • 打赏
  • 举报
 回复
给测试数据
昵称被占用了 2012-06-18
  • 打赏
  • 举报
 回复 
SELECT A.WORKRECO , B.WORKRECO,MIN (A.WKR_DATTIM),MAX(A.WKR_DATTIM)

FROM 考勤系统原始打卡记录表 A,考勤系统原始打卡记录表 B

WHERE A.WKR_DATTIM = B.WKR_DATTIM

AND A.WKR_CACS  = B.WKR_CACS 

AND A.WORKRECO < B.WORKRECO

GROUP BY A.WORKRECO , B.WORKRECO

HAVING COUNT(1) > 1
--小F-- 2012-06-18
  • 打赏
  • 举报
 回复 


select

 * 

from

 tb t 

where

 exists(select 1 from tb where workeco<>t.workeco and WKR_CACS=t.WKR_CACS and datediff(dd,WKR_DATTIM,tWKR_DATTIM)>=2) and convert(varchar(10),WKR_DATTIM,108)=convert(varchar(10),t.WKR_DATTIM,108)
第 23 篇：如何抓到“正确”的包
抓包实战：如何精准捕获有效数据包 网络排障中，抓包分析的关键不在于技术本身，而在于能否捕获到真正有用的数据包。本文提出抓包五步法： 明确目标 - 将模糊问题转化为可验证的具体假设（如DNS延迟、TCP握手问题等） 绘制路径 - 理清流量经过的完整路径（客户端→LB→服务端→DB） 选择位置 - 根据假设确定最佳抓包点（客户端/服务端/中间节点） 找准接口 - 区分物理网卡/虚拟接口/回环设备，避免抓错路径 精准过滤 - 采用从宽到窄策略，逐步缩小范围验证假设 有效抓包的核心在于：在正确的位置、正确的时间、针
【2025最新高维多目标优化】基于城市场景下无人机三维路径规划的导航变量的多目标粒子群优化算法NMOPSO研究（Matlab代码实现）
内容概要：本文围绕城市场景下的无人机三维路径规划问题，提出了一种新颖的多目标粒子群优化算法（NMOPSO），旨在解决高维复杂环境中路径规划面临的多重挑战。研究聚焦于导航变量的多目标优化，构建了一个包含路径长度、飞行安全性、能量消耗等多个相互冲突目标的优化模型，并采用改进的粒子群算法进行高效搜索，最终获得一组Pareto最优解集，为实际决策提供多样化路径选择方案。该方法结合Matlab代码实现，详细阐述了算法的设计机制、数学建模流程及仿真验证过程，充分展示了其在密集城市建筑环境中有效规避障碍物、满足飞行动力学约束并实现多目标权衡的能力，具有较强的工程应用价值。; 适合人群：具备一定优化算法理论基础和Matlab编程能力，从事无人机路径规划、智能交通系统、自动化控制、人工智能应用等方向研究的研究生、科研人员及工程技术人员。; 使用场景及目标：①应用于城市环境中的无人机物流配送、巡检监控、应急救援等实际任务的三维路径规划；②为高维、非线性、多约束的多目标优化问题提供有效的算法设计思路与改进参考；③通过Matlab仿真平台复现算法，进一步开展性能测试、参数调优与算法对比研究，推动相关领域的技术创新。; 阅读建议：此资源强调算法原理与工程实践的深度融合，建议读者重点研读目标函数的构建方式、约束条件的处理策略以及NMOPSO算法的核心改进机制，并务必动手运行和调试所提供的Matlab代码，以深入理解算法在三维空间路径优化中的具体实现细节与实际表现。
易语言源码超级列表框载入图片(仿Windows浏览框的显缩略图)
易语言源码超级列表框载入图片（仿Windows浏览框的显缩略图）
疑难问题

22,296

社区成员

121,728

社区内容

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

加载中

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

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

+ 用AI写文章