日期:2014-05-17  浏览次数:20503 次

如何查询代打卡嫌疑人清单
考勤系统原始打卡记录表结构如下:
WORKRECO (WKR_INCOD char(7) NOT NULL,--工号
WKR_DATTIM smalldatetime NOT NULL, --刷卡时间
WKR_CACS varchar(3) NOT NULL) --刷卡机器号

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

工号1,工号2,起始日期,截止日期

------解决方案--------------------
写死我了,希望你看得懂。。。
SQL code

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

------解决方案--------------------
SQL code

--> 测试数据:[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]
s