选择连续3天缺席的员工 [英] Select employee who were absent for 3 consecutive days

问题描述

emp_id leave_date

1 3-aug-13

1 4-aug-13

1 10-aug-13
1 13-aug-13

2 10-aug-13

2 13-aug-13

3 3- aug-13

3 4-aug-13

3 5-aug-13

3 10-aug-13



我的尝试：



想要连续3天缺席的员工ID

emp_id leave_date
1 3-aug-13
1 4-aug-13
1 10-aug-13
1 13-aug-13
2 10-aug-13
2 13-aug-13
3 3-aug-13
3 4-aug-13
3 5-aug-13
3 10-aug-13

What I have tried:

want employee id who were absent for 3 consecutive days

推荐答案

首先，检查您的数据类型。

如果Leave_date存储在DATETIME，DATETIME2中。或DATE字段，那么它是可能的 - 但不简单。如果它存储在VARCHAR或NVARCHAR中（就像你从样本数据中看到的那样）那么它就不是。



即使这样，它也很复杂：sql server - SQL：按连续记录分组 - 堆栈溢出 [ ^ ]

First, check your datatypes.
If Leave_date is stored in a DATETIME, DATETIME2. or DATE field, then it's possible - but not simple. If it's stored in VARCHAR or NVARCHAR (as it would seem from your sample data) then it isn't.

Even then, it's complicated: sql server - SQL: Group By on Consecutive Records - Stack Overflow[^]

选择id，rw来自



（

选择id，dd，dateadd（dd，row_number（）over（按ID顺序分区） dd desc），dd）作为一个来自emp

）的a.id，a.rw



组
有计数（*）= 3

select id, rw from

(
select id,dd,dateadd(dd,row_number()over(partition by id order by dd desc),dd) as rw from emp
)as a

group by a.id,a.rw
having count(*)=3

这篇关于选择连续3天缺席的员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！