求教关于提取考勤打卡记录的sql语句。

员工每天打四次卡，分别是每天 

8:00（上班）
12:00（下班）
13:30（上班）
17:30（下班）


如果每次打卡的时候，在20分钟内多次重复打卡，那么只是第一次打卡的记录有效，用 sql 语句有没有办法提取每天有效的4次打卡记录？ 

11 个解决方案

#1

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([name] [nvarchar](10),[time] [datetime])
INSERT INTO [tb]
SELECT 'a','2010-11-01 07:42:00.000' UNION ALL
SELECT 'a','2010-11-01 07:52:00.000' UNION ALL
SELECT 'a','2010-11-01 12:02:00.000' UNION ALL
SELECT 'a','2010-11-01 12:12:00.000' UNION ALL
SELECT 'a','2010-11-01 13:02:00.000' UNION ALL
SELECT 'a','2010-11-01 13:22:00.000' UNION ALL
SELECT 'a','2010-11-01 17:42:00.000' UNION ALL
SELECT 'a','2010-11-01 17:45:00.000' UNION ALL
SELECT 'a','2010-11-02 07:42:00.000' UNION ALL
SELECT 'a','2010-11-02 07:52:00.000' UNION ALL
SELECT 'a','2010-11-02 12:02:00.000' UNION ALL
SELECT 'a','2010-11-02 12:12:00.000' UNION ALL
SELECT 'a','2010-11-02 13:02:00.000' UNION ALL
SELECT 'a','2010-11-02 13:22:00.000' UNION ALL
SELECT 'a','2010-11-02 17:42:00.000' UNION ALL
SELECT 'a','2010-11-02 17:45:00.000' UNION ALL
SELECT 'b','2010-11-01 07:42:00.000' UNION ALL
SELECT 'b','2010-11-01 07:52:00.000' UNION ALL
SELECT 'b','2010-11-01 12:02:00.000' UNION ALL
SELECT 'b','2010-11-01 12:12:00.000' UNION ALL
SELECT 'b','2010-11-01 13:02:00.000' UNION ALL
SELECT 'b','2010-11-01 13:22:00.000' UNION ALL
SELECT 'b','2010-11-01 17:42:00.000' UNION ALL
SELECT 'b','2010-11-01 17:45:00.000' UNION ALL
SELECT 'b','2010-11-02 07:42:00.000' UNION ALL
SELECT 'b','2010-11-02 07:52:00.000' UNION ALL
SELECT 'b','2010-11-02 12:02:00.000' UNION ALL
SELECT 'b','2010-11-02 12:12:00.000' UNION ALL
SELECT 'b','2010-11-02 13:02:00.000' UNION ALL
SELECT 'b','2010-11-02 13:22:00.000' UNION ALL
SELECT 'b','2010-11-02 17:42:00.000' UNION ALL
SELECT 'b','2010-11-02 17:45:00.000' UNION ALL
SELECT 'b','2010-11-02 17:56:00.000'


-->SQL查询如下:
;WITH t1 AS 
(
	SELECT rn=ROW_NUMBER()OVER(PARTITION BY NAME,CONVERT(VARCHAR,TIME,23) ORDER BY TIME),name,TIME 
	FROM tb 
	WHERE abs(datediff(mi,CONVERT(VARCHAR,TIME,8),'08:00'))<=20
),t2 AS 
(
	SELECT rn=ROW_NUMBER()OVER(PARTITION BY NAME,CONVERT(VARCHAR,TIME,23) ORDER BY TIME),name,TIME 
	FROM tb 
	WHERE abs(datediff(mi,CONVERT(VARCHAR,TIME,8),'12:00'))<=20
),t3 AS 
(
	SELECT rn=ROW_NUMBER()OVER(PARTITION BY NAME,CONVERT(VARCHAR,TIME,23) ORDER BY TIME),name,TIME 
	FROM tb 
	WHERE abs(datediff(mi,CONVERT(VARCHAR,TIME,8),'13:30'))<=20
),t4 AS 
(
	SELECT rn=ROW_NUMBER()OVER(PARTITION BY NAME,CONVERT(VARCHAR,TIME,23) ORDER BY TIME),name,TIME 
	FROM tb 
	WHERE abs(datediff(mi,CONVERT(VARCHAR,TIME,8),'17:30'))<=20
)
SELECT NAME,TIME FROM t1 WHERE rn=1
UNION 
SELECT NAME,TIME FROM t2 WHERE rn=1
UNION 
SELECT NAME,TIME FROM t3 WHERE rn=1
UNION 
SELECT NAME,TIME FROM t4 WHERE rn=1

/*
NAME       TIME
---------- -----------------------
a          2010-11-01 07:42:00.000
a          2010-11-01 12:02:00.000
a          2010-11-01 13:22:00.000
a          2010-11-01 17:42:00.000
a          2010-11-02 07:42:00.000
a          2010-11-02 12:02:00.000
a          2010-11-02 13:22:00.000
a          2010-11-02 17:42:00.000
b          2010-11-01 07:42:00.000
b          2010-11-01 12:02:00.000
b          2010-11-01 13:22:00.000
b          2010-11-01 17:42:00.000
b          2010-11-02 07:42:00.000
b          2010-11-02 12:02:00.000
b          2010-11-02 13:22:00.000
b          2010-11-02 17:42:00.000

(16 行受影响)
*/

好久没写了，方法不够好，但应该能得出结果。

#2

up 楼上的

#3

参考1楼的

#4

考勤需要一个方案，绝不是三两句高明的SQL之力

#5

引用 1 楼 htl258 的回复:

SQL code
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([name] [nvarchar](10),[time] [datetime])
INSERT INTO [tb]
SELECT 'a','2010-11-01 07:42:00.0……

MARK

#6

二楼的能否把 where 子句改成CONVERT(VARCHAR,TIME,8) between ？这样可以用CONVERT(VARCHAR,TIME,8)做个索引。应该快一点吧？

#7

学习了

#8

顶二楼 很敬业。。。 支持。。

#9

我的是 sql server 2000，没有 ROW_NUMBER()这个函数，还有其他算法吗？

#10

那就只能使用临时表了。

#11

每天回帖即可获得10分可用分