oracle使用not in导致结果不准确

Question

问题遇到的现象和发生背景

一张表格有ID,IP,MAC,三列，分别求出同ip客户，同mac客户，ipmac都相同客户，并且对应客户只出现在一个结果中。如果客户A的ip与mac和客户2的ip与mac分别有一条相同记录，也认为是ipmac都相同。
现在遇到问题，仅ip相同的客户，查询结果是在ipmac都相同下显示

用代码块功能插入代码，请勿粘贴截图

建表
create table IP_MAC
(
id NUMBER(10),
ip VARCHAR2(20),
mac VARCHAR2(30)
)

插入数据
insert into IP_MAC values(1, 'ip1', 'mac1');
insert into IP_MAC values(1, 'ip2', 'mac2');
insert into IP_MAC values(1, 'ip3', 'mac3');

insert into IP_MAC values(2, 'ip1', 'mac2');
insert into IP_MAC values(2, 'ip3', 'mac3');

insert into IP_MAC values(3, 'ip1', 'mac2');

insert into IP_MAC values(4, 'ip2', 'mac1');

insert into IP_MAC values(5, 'ip1', 'mac11');
commit;

查询语句

with tmp as (
select a.id
,case when a.ip = b.ip and a.mac <> b.mac then b.id else null end id_ip
,case when a.ip <> b.ip and a.mac = b.mac then b.id else null end id_mac
,case when a.ip = b.ip and a.mac = b.mac then b.id else null end id_ipmac
from IP_MAC a
join IP_MAC b
on a.id<>b.id and(a.ip=b.ip or a.mac=b.mac)
group by a.id
,case when a.ip = b.ip and a.mac <> b.mac then b.id else null end
,case when a.ip <> b.ip and a.mac = b.mac then b.id else null end
,case when a.ip = b.ip and a.mac = b.mac then b.id else null end
)
select a.id
,en_concat(case when id_ip is not null
and a.id_ip not in (select id_mac from tmp where id_mac is not null )
and id_ip not in (select id_ipmac from tmp where id_ipmac is not null )
then id_ip else null end) ip
,en_concat(case when id_mac is not null and id_ip not in (select id_ip from tmp where id_ip is not null )
and id_mac not in (select id_ipmac from tmp where id_ipmac is not null ) then id_mac else null end) mac

,en_concat(distinct case when id_ipmac is not null then id_ipmac
when id_ip in (select id_mac from tmp where id_mac is not null ) and id_ip in (select id_ip from tmp where id_ip is not null ) then id_ip
else null end) ipmac

from tmp a
group by a.id

运行结果及报错内容

ID为5的数据，应该是IP列为1,2,3，IPMAC列为空， 但是查询结果是相反的。

我的解答思路和尝试过的方法

感觉是 not in 有了 null值导致的， 但是排查不出结果

我想要达到的结果

ID为5的数据，应该是IP列为1,2,3，IPMAC列为空

Answer 1

1、展示优先级：客户id：当存在 ip、mac 同时满足的，则优先展示在ipmac中，其次，如果有 ip 满足的，则展示在 ip 列中，最后，如果有 mac 满足的，则展示在 mac 列中

2、添加一条哪个都不满足的特殊数据和一条仅mac满足的数据

    insert into IP_MAC values(6, 'ip4', 'mac4');
    insert into IP_MAC values(7, 'ip5', 'mac11');

3、实现SQL

WITH a AS (
        SELECT a.id id_a, b.id id_b
             , ( CASE WHEN a.ip = b.ip AND a.mac = b.mac THEN b.id ELSE NULL END ) ipmac
             , ( CASE WHEN a.ip = NVL( b.ip, '-' ) AND a.mac != NVL( b.mac, '-' ) THEN b.id ELSE NULL END ) ip
             , ( CASE WHEN a.ip != NVL( b.ip, '-' ) AND a.mac = NVL( b.mac, '-' ) THEN b.id ELSE NULL END ) mac--, a.*, b.*
          FROM ip_mac a LEFT JOIN ip_mac b ON ( a.ip = b.ip OR a.mac = b.mac ) AND a.id != b.id )
    , b AS (
        SELECT id_a, ',' || to_char( wmsys.wm_concat( ipmac ) ) || ',' ipmac
             , to_char( wmsys.wm_concat( ip ) ) || ',' ip
          FROM a GROUP BY id_a )
SELECT a.id_a AS ID, to_char( wmsys.wm_concat( CASE WHEN b.ipmac LIKE '%,' || a.ip || ',%' THEN NULL ELSE a.ip END ) ) ip
     , to_char( wmsys.wm_concat( CASE WHEN b.ipmac || b.ip LIKE '%,' || a.mac || ',%' THEN NULL ELSE a.mac END ) ) mac
     , to_char( wmsys.wm_concat( a.ipmac ) ) ipmac
  FROM a JOIN b ON a.id_a = b.id_a
 GROUP BY a.id_a;

输出结果：

输出结果各列id顺序未排序，是 wm_concat 函数的问题，我的是10g的数据库，没有listagg函数，如果你是11g的，可以使用listagg函数替换 wm_concat ，而且可以进行排序输出：listagg(column_name,',') within group(order by column_id)

4、再次看了下题主的输出要求，发现ip和mac即便不在一条记录上满足，即 id 为 1 的 ip1 与 id为 3 的 ip1 相匹配，但id为 3 的这条记录的 mac 值 mac2 与 id 为 1 的另一条记录：ip2 mac2 中的mac2相匹配，这样似乎也算到 ipmac共同匹配的头上了，所以对上述SQL进行了修改：

WITH a AS (
        SELECT a.id id_a, b.id id_b
             , ( CASE WHEN a.ip = NVL( b.ip, '-' ) AND a.mac != NVL( b.mac, '-' ) THEN b.id ELSE NULL END ) ip
             , ( CASE WHEN a.ip != NVL( b.ip, '-' ) AND a.mac = NVL( b.mac, '-' ) THEN b.id ELSE NULL END ) mac--, a.*, b.*
             , COUNT( ( CASE WHEN a.ip = NVL( b.ip, '-' ) THEN b.id ELSE NULL END ) ) OVER( PARTITION BY a.id, b.id ) cnt_ip
             , COUNT( ( CASE WHEN a.mac = NVL( b.mac, '-' ) THEN b.id ELSE NULL END ) ) OVER( PARTITION BY a.id, b.id ) cnt_mac
          FROM ip_mac a LEFT JOIN ip_mac b ON ( a.ip = b.ip OR a.mac = b.mac ) AND a.id != b.id )
    , b AS (
        SELECT DISTINCT id_a, ( CASE WHEN cnt_ip > 0 AND cnt_mac > 0 THEN a.id_b ELSE NULL END ) ipmac
             , ( CASE WHEN cnt_ip > 0 AND cnt_mac < 1 THEN a.ip ELSE NULL END ) ip
             , ( CASE WHEN cnt_ip < 1 AND cnt_mac > 0 THEN a.mac ELSE NULL END ) mac
          FROM a )
SELECT id_a AS ID, to_char( wmsys.wm_concat( ip ) ) ip
     , to_char( wmsys.wm_concat( mac ) ) mac
     , to_char( wmsys.wm_concat( ipmac ) ) ipmac
  FROM b GROUP BY id_a;

输出结果：

这个就跟题主的输出一致了（顺序问题请使用listagg）