问题遇到的现象和发生背景
一张表格有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列为空