Oracle选择具有多列条件的记录 [英] Oracle selecting record with multiple column condition

问题描述

在这里，我想为事件37和49选择enames为date为空的enames，并且还为这两个事件(37,49)未链接的ename选择一个.如果任何事件的date不为null，则不应选择该ename.在此示例中，此处应仅获取C1，D1，E1 ename. D1和E1，因为未链接到事件编号37和49

Here i want to select the enames for which the date is null for both the events 37 and 49 AND also the enames for which this two events(37,49) are not linked. If date for any event is not null then it shouldn't select that ename. Here in this example it should fetch only C1,D1,E1 ename. D1 and E1 because does not link to event no 37 and 49

有人可以帮我吗?

id  ename   event   date
1   A1      37      1-Oct-16
2   A1      49      NULL
3   C1      37      NULL
4   C1      49      NULL
5   D1      50      NULL    
6   E1      30      NULL

只需再添加一个条件即可.请

Just added one more condition. Please

推荐答案

计数相关行.如果只需要ename

Count relevant rows. If only ename is needed

select ename
from mytable
where event in (37,49) and date is null
group by ename
having count(*) = 2

编辑

遵循新的条件

select distinct ename
from mytable t1
where not exists ( 
    select 1 
    from mytable t2
    where t2.ename = t1.ename and t2.event in (37,49) and t2.date is not NULL)
    ;

这篇关于Oracle选择具有多列条件的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！