如何查询代打卡嫌疑人清单
考勤系统原始打卡记录表结构如下:
WORKRECO (WKR_INCOD char(7) NOT NULL,--工号
WKR_DATTIM smalldatetime NOT NULL, --刷卡时间
WKR_CACS varchar(3) NOT NULL) --刷卡机器号
问题：如何查询两天以上(含两天)，刷卡时间与刷卡机器号相同的员工,显示结果最好有如下栏位:
工号１,工号２，起始日期，截止日期
[解决办法]
写死我了，希望你看得懂。。。
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'WORKRECO')BEGIN DROP TABLE WORKRECOENDGOCREATE TABLE WORKRECO ( WKR_INCOD char(7) NOT NULL,--工号 WKR_DATTIM smalldatetime NOT NULL, --刷卡时间 WKR_CACS varchar(3) NOT NULL) --刷卡机器号GOINSERT INTO WORKRECOSELECT 'B0001','2012-06-01 07:58:00','001' UNIONSELECT 'B0002','2012-06-01 07:58:00','001' UNIONSELECT 'B0003','2012-06-01 07:50:00','001' UNIONSELECT 'B0001','2012-06-01 12:01:00','002' UNIONSELECT 'B0002','2012-06-01 12:01:00','002' UNIONSELECT 'B0003','2012-06-01 12:03:00','002' UNIONSELECT 'B0001','2012-06-01 13:29:00','002' UNIONSELECT 'B0002','2012-06-01 13:29:00','002' UNIONSELECT 'B0003','2012-06-01 13:28:00','001' UNIONSELECT 'B0001','2012-06-01 17:35:00','001' UNIONSELECT 'B0002','2012-06-01 17:35:00','001' UNIONSELECT 'B0003','2012-06-01 17:30:00','001' UNIONSELECT 'B0001','2012-06-02 07:58:00','001' UNIONSELECT 'B0002','2012-06-02 07:58:00','001' UNIONSELECT 'B0003','2012-06-02 07:50:00','001' UNIONSELECT 'B0001','2012-06-02 12:01:00','002' UNIONSELECT 'B0002','2012-06-02 12:01:00','002' UNIONSELECT 'B0003','2012-06-02 12:03:00','002' UNIONSELECT 'B0001','2012-06-02 13:29:00','002' UNIONSELECT 'B0002','2012-06-02 13:29:00','002' UNIONSELECT 'B0003','2012-06-02 13:28:00','001' UNIONSELECT 'B0001','2012-06-02 17:35:00','001' UNIONSELECT 'B0002','2012-06-02 17:35:00','001' UNIONSELECT 'B0003','2012-06-02 17:30:00','001' UNIONSELECT 'B0001','2012-06-03 07:58:00','001' UNIONSELECT 'B0002','2012-06-03 07:58:00','001' UNIONSELECT 'B0003','2012-06-03 07:50:00','001' UNIONSELECT 'B0001','2012-06-03 12:01:00','002' UNIONSELECT 'B0002','2012-06-03 12:01:00','002' UNIONSELECT 'B0003','2012-06-03 12:03:00','002' UNIONSELECT 'B0001','2012-06-03 13:29:00','002' UNIONSELECT 'B0002','2012-06-03 13:29:00','002' UNIONSELECT 'B0003','2012-06-03 13:28:00','001' UNIONSELECT 'B0001','2012-06-03 17:35:00','001' UNIONSELECT 'B0002','2012-06-03 17:35:00','001' UNIONSELECT 'B0003','2012-06-03 17:30:00','001'GOWITH t AS(SELECT WKR_INCOD,CONVERT(VARCHAR(10),WKR_DATTIM,23) AS date,COUNT(1) AS numFROM WORKRECO AS AWHERE 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 numFROM WORKRECO AS AGROUP 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.dateFROM t AS A,m AS BWHERE 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 numFROM nGROUP BY AWKR_INCOD,BWKR_INCODHAVING 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,ENDDATEFROM xAWKR_INCOD BWKR_INCOD BEGINDATE ENDDATEB0001 B0002 2012-06-01 2012-06-03
[解决办法]
--> 测试数据：[WORKRECO]if object_id('[WORKRECO]') is not null drop table [WORKRECO]gocreate table [WORKRECO]([WKR_INCOD] varchar(5),[WKR_DATTIM] datetime,[WKR_CACS] varchar(3))goinsert [WORKRECO]select 'B0001','2012-06-01 07:58:00','001' union allselect 'B0002','2012-06-01 07:58:00','001' union allselect 'B0003','2012-06-01 07:50:00','001' union allselect 'B0001','2012-06-01 12:01:00','002' union allselect 'B0002','2012-06-01 12:01:00','002' union allselect 'B0003','2012-06-01 12:03:00','002' union allselect 'B0001','2012-06-01 13:29:00','002' union allselect 'B0002','2012-06-01 13:29:00','002' union allselect 'B0003','2012-06-01 13:28:00','001' union allselect 'B0001','2012-06-01 17:35:00','001' union allselect 'B0002','2012-06-01 17:35:00','001' union allselect 'B0003','2012-06-01 17:30:00','001' union allselect 'B0001','2012-06-02 07:58:00','001' union allselect 'B0002','2012-06-02 07:58:00','001' union allselect 'B0003','2012-06-02 07:50:00','001' union allselect 'B0001','2012-06-02 12:01:00','002' union allselect 'B0002','2012-06-02 12:01:00','002' union allselect 'B0003','2012-06-02 12:03:00','002' union allselect 'B0001','2012-06-02 13:29:00','002' union allselect 'B0002','2012-06-02 13:29:00','002' union allselect 'B0003','2012-06-02 13:28:00','001' union allselect 'B0001','2012-06-02 17:35:00','001' union allselect 'B0002','2012-06-02 17:35:00','001' union allselect 'B0003','2012-06-02 17:30:00','001' union allselect 'B0001','2012-06-03 07:58:00','001' union allselect 'B0002','2012-06-03 07:58:00','001' union allselect 'B0003','2012-06-03 07:50:00','001' union allselect 'B0001','2012-06-03 12:01:00','002' union allselect 'B0002','2012-06-03 12:01:00','002' union allselect 'B0003','2012-06-03 12:03:00','002' union allselect 'B0001','2012-06-03 13:29:00','002' union allselect 'B0002','2012-06-03 13:29:00','002' union allselect 'B0003','2012-06-03 13:28:00','001' union allselect 'B0001','2012-06-03 17:35:00','001' union allselect 'B0002','2012-06-03 17:35:00','001' union allselect 'B0003','2012-06-03 17:30:00','001'go--问题：如何查询两天以上(含两天)，刷卡时间与刷卡机器号相同的员工,显示结果最好有如下栏位;with tas(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_DATTIMgroup by a.WKR_INCOD,b.WKR_INCOD,a.dehaving DATEDIFF(DD,MIN(a.WKR_DATTIM),MAX(b.WKR_DATTIM))>=2/*工号一 工号二 起始时间 结束时间------------------------------------------------B0001 B0002 2012-06-01 2012-06-03*/