查找连续5天，日期范围内输出谁工作的用户(Find Users who worked for 5 c

我有一个表有类似下面的数据

  Emp  Date        Code     
  ---  --------    ---- 
  E1  11/1/2012    W 
  E1  11/1/2012    V   
  E2  11/1/2012    W   
  E1  11/2/2012    W
  E1  11/3/2012    W
  E1  11/4/2012    W
  E1  11/5/2012    W

我希望得到一个日期范围之间的员工列表（说在过去的3个月）为谁码W¯¯conescutively 5天，在输出的日期范围内的工作。 每个员工都可以有不同的代码的单日多条记录。

预期成果是

Emp   Date-Range 
---   ----------
 E1   11/1 -11/5

下面是我试过，但我没有接近在所有输出我求

 SELECT distinct user, MIN(date) startdate, MAX(date) enddate
FROM (SELECT user, date, (TRUNC(date) - ROWNUM) tmpcol
      FROM (SELECT user, date
              FROM tablename
             where date between to_date('10/01/2012','mm/dd/yyyy') and to_date('10/03/2012','mm/dd/yyyy')
             ORDER BY user, date) ot) t
 GROUP BY user, tmpcol
 ORDER BY user, startdate;

如果E1的Emp已连续10天的工作，他应该与两个日期范围输出中列出两次。 如果E1已经为连续9天（11/1 11/9至）工作，他应与日期范围11/1 11/9到上市只有一次。

我已经看到了这是类似的问题，但没有人确切地摸索出适合我。 我的数据库是Oracle 10g和没有PL / SQL。

Answer 1:

我不知道我理解正确的一切，但这样的事情可能让你开始：

select emp, 
       sum(diff) as days,
       to_char(min(workdate), 'yyyy-mm-dd') as work_start,
       to_char(max(workdate), 'yyyy-mm-dd') as work_end
from (       
  select *
  from (
    select emp, 
           workdate, 
           code, 
           nvl(workdate - lag(workdate) over (partition by emp, code order by workdate),1) as diff
    from tablename
    where code = 'W'
     and workdate between ...
  ) t1
  where diff = 1 -- only consecutive rows
) t2
group by emp
having sum(diff) = 5

SQLFiddle： http://sqlfiddle.com/#!4/ad7ae/3

请注意，我用workdate代替date ，因为它是一个坏主意，使用保留字作为列名。

Answer 2:

您可以从这里开始：

select 
emp, count(*) over (partition by emp, code order by date_worked range interval '5' day preceding) as days_worked_last_5_days
from table
where code='W';

与days_worked_last_5_days = 5的那些行是你搜索什么。

看到这个小提琴。

Answer 3:

Select emp, data-5, data from (SELECT EMP, DATA, WORK,lag, lead, row_number() over(PARTITION BY emp--, DATA 
ORDER BY DATA asc) rn
  FROM (SELECT emp,
               data,
               work,
               LAG (data) OVER (PARTITION BY emp ORDER BY data ASC) LAG,
               LEAD (data) OVER (PARTITION BY emp ORDER BY data ASC) LEAD
          FROM (SELECT emp,
                       data,
                       work,
                       ROW_NUMBER ()
                           OVER (PARTITION BY emp, data ORDER BY data ASC)
                           rn
                  FROM example)
         WHERE rn = 1) a
WHERE a.data + 1 = LEAD AND a.data - 1 = LAG
) WHERE rn = 5

当表的例子是：

EMP（VARCHAR2），日期， 'W' 或 'F'

Answer 4:

SELECT * FROM (
SELECT USERID,USEDATE,WRK,RANK() OVER (PARTITION BY USERID,WRK ORDER BY USEDATE ) AS RNK1 FROM USER1  )U1 JOIN
(
SELECT USERID,USEDATE,WRK,RANK() OVER (PARTITION BY USERID,WRK ORDER BY USEDATE ) AS RNK2 FROM USER1  )U2 ON U1.USERID=U2.USERID AND U1.RNK1+3=U2.RNK2 AND U2.USEDATE-U1.USEDATE=3;

文章来源: Find Users who worked for 5 consecutive days with date-range in output