oracle中in用法的优化,使用not in的如何优化

8楼结果不对

SQL> with SIGN_DETAIL_INFO (userName,signDate)

2  as(

3  select 1 , date'2019-1-18' from dual union

4  select 1 , date'2019-1-19' from dual union

5  select 2 , date'2019-1-18' from dual union

6  select 2 , date'2019-1-20' from dual)

7  select a.userName

8    from SIGN_DETAIL_INFO a

9  where a.signDate between

10         to_date('2019-01-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and

11         to_date('2019-01-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

12     and a.userName not in

13         (select a.userName

14            from SIGN_DETAIL_INFO a

15           where signDate =

16                 to_date('2019-01-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss'));

USERNAME

----------

2

已用时间:  00: 00: 00.01

SQL>

SQL>

SQL> with SIGN_DETAIL_INFO (userName,signDate)

2  as(

3  select 1 , date'2019-1-18' from dual union

4  select 1 , date'2019-1-19' from dual union

5  select 2 , date'2019-1-18' from dual union

6  select 2 , date'2019-1-20' from dual)

7  select a.userName

8    from SIGN_DETAIL_INFO a

9  where signDate between date '2019-01-17' and date '2019-01-19'

10  group by userName

11  having count(case when signDate=date '2019-01-19' then 1 end)=0;

USERNAME

----------

2

已用时间:  00: 00: 00.00

SQL>

SQL>

SQL> with SIGN_DETAIL_INFO (userName,signDate)

2  as(

3  select 1 , date'2019-1-18' from dual union

4  select 1 , date'2019-1-19' from dual union

5  select 2 , date'2019-1-18' from dual union

6  select 2 , date'2019-1-20' from dual)

7  select userName from ( select case when signDate=date '2019-01-19' then null else  a.userName end userName

8    from SIGN_DETAIL_INFO a

9  where a.signDate between

10         to_date('2019-01-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and

11         to_date('2019-01-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) where userName is not null;

USERNAME

----------

1

2

已用时间:  00: 00: 00.01