首页 文章

如何在oracle sql中查找日期范围内的缺失数据

提问于
浏览 次
2

我有 table （ATTENDANCE）包含员工的出勤情况如下

EMPNO   DATE
2     07/11/2016
2     07/12/2016
3     07/12/2016
6     07/13/2016
7     07/13/2016

其中包含员工编号（empno）和参加日期（日期）

另一个表（EMPLOYEES）包含公司中的所有雇员，例如这样

EMPNO   NAME
1       Musa
2       Ali 
3       Khalid
6       James
7       Sara

我可以通过使用此代码查找某个员工是否在特定日期缺席

select empno 
from EMPLOYEES 
where empno not in (select empno 
               from ATTENDANCE
               where date = '07/11/2016')

我想在特定范围之间的任何一天检索缺席员工的名单
我试图使用这个查询

select empno 
from EMPLOYEES 
where empno not in (select empno 
               from ATTENDANCE
               where  date between '07/11/2016' and  '07/13/2016'  )

但这会给我那些在这个范围内缺席的人 If an employee was absent in one day only it will not retreive it

我想要一个查询来检索empno和员工缺席的日期？

Update: 我也试过这个，正如 sagi 提到的那样

select empno 
from EMPLOYEES 
where empno not in (select empno
    from ATTENDANCE
    group by empno
    having count(empno) = to_date(' 07/09/2016','mm,dd,rrrr') - to_date('07/13/2016','mm,dd,rrrr') )

这将有效，但它不会给我 which date the employee was absent

sql oracle

3 回答

  • 2

    我想要一个查询来检索empno和员工缺席的日期

    在这种情况下，您需要使用例如此查询生成给定范围内的所有可能日期：

    SELECT date '2016-07-11' + level - 1 As "DATE" from dual
CONNECT BY LEVEL <= date '2016-07-13' - date '2016-07-11' + 1;

DATE            
-----------------
16/07/11 00:00:00
16/07/12 00:00:00
16/07/13 00:00:00

    然后使用交叉连接生成所有可能的对：date employe

    SELECT e.empno, d."DATE"
FROM (
    SELECT date '2016-07-11' + level - 1 As "DATE" from dual
    CONNECT BY LEVEL <= date '2016-07-13' - date '2016-07-11' + 1
) d
CROSS JOIN empno e

    然后使用外连接和IS NULL条件过滤掉现有记录（仅保留现有的对：empno date）

    SELECT e.empno, d."DATE"
FROM (
    SELECT date '2016-07-11' + level - 1 As "DATE" from dual
    CONNECT BY LEVEL <= date '2016-07-13' - date '2016-07-11' + 1
  ) d
CROSS JOIN EMPLOYEES e 
LEFT JOIN ATTENDANCE a 
ON ( a.empno = e.empno AND a."DATE" = d."DATE" )
WHERE a."DATE" IS NULL
order by 1,2
    回复于
  • 0

    从EMPLOYEES e中选择distinct empno，在e.empno = a.empno和e.date = a.date中选择ATTENDANCE a，其中a.empno为NULL

    回复于
  • 0

    这里更清洁的方法是使用CTE来识别出勤表中的所有不同日期 .

    然后，我们与employees表进行交叉连接，以获得所有可能的组合

    最后，我们将检查出勤表中是否存在EmpNo，Date元组 .

    With Dates (date)  as (Select Distinct Date from Attendance )

Select Emp.EmpNo, dates.date from employee emp cross join Dates
where (emp.empno, dates.date) not in
(Select Empno, date from Attendance
    回复于

相关问题