oracle 查找连续工作 5 天且输出日期范围为日期的用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14523906/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find Users who worked for 5 consecutive days with date-range in output
提问by user841311
I have a table has data similar to below
我有一个表有类似于下面的数据
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
I want to get list of employees between a date range(say for the last 3 months) who worked for code W conescutively for 5 days with the date range in the output. Each employee can have multiple records for a single day with different codes.
我想获得一个日期范围（比如过去 3 个月）之间的员工列表，他们连续为代码 W 工作了 5 天，输出中的日期范围。每个员工在一天内可以有多个不同代码的记录。
Expected Output is
预期输出为
Emp Date-Range
--- ----------
E1 11/1 -11/5
Below is what I tried but I didn't come close at all to the output I seek
以下是我尝试过的，但我根本没有接近我寻求的输出
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;
If Emp E1 has worked for 10 consecutive days, he should be listed twice in the output with both date ranges. If E1 has worked for 9 days consecutively(11/1 to 11/9), he should be listed only once with the date range 11/1 to 11/9.
如果 Emp E1 连续工作了 10 天，他应该在两个日期范围的输出中列出两次。如果E1连续工作了9天（11/1到11/9），他应该只列出一次，日期范围是11/1到11/9。
I have already seen questions which are similar but none of them exactly worked out for me. My database is Oracle 10G and no PL/SQL.
我已经看到类似的问题，但没有一个完全适合我。我的数据库是 Oracle 10G，没有 PL/SQL。
回答by Florin Ghita
You may start from here:
你可以从这里开始：
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';
those rows with days_worked_last_5_days=5 are what you search.
那些 days_worked_last_5_days=5 的行就是你要搜索的。
回答by a_horse_with_no_name
I'm not sure I understood everything correctly, but something like this might get you started:
我不确定我是否正确理解了所有内容，但是这样的事情可能会让您开始：
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
SQLFiddle：http://sqlfiddle.com/#!4/ad7ae/3
Note that I used workdateinstead of the dateas it is a bad idea to use a reserved word as a column name.
请注意，我使用了workdate而不是date使用保留字作为列名是一个坏主意。
回答by Pedro Pires
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
Where table example is :
表示例是：
EMP(varchar2), date, 'W' or 'F'
EMP(varchar2)、日期、'W' 或 'F'
回答by jITHIN
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;
