select A.* into ALLsame from A,B where A.IP=B.IP and (left(A.MAC,17)=B.MAC or right(A.MAC,17)=B.MAC or substring(A.MAC,18,34)=B.MAC or substring(A.MAC,35,51)=B.MAC or substring(A.MAC,52,68)=B.MAC or substring(A.MAC,69,85)=B.MAC)
17位MAC,分号在哪儿呢?
17位MAC,分号在哪儿呢?
IP 相同,MAC 包含
IP 相同,MAC 不包含
IP 不同,MAC 包含
IP 不同,MAC 不包含
从逻辑上看,四种情况总和,为A表总的记录数.如果只以这两列来比较,应该得到肯定的结果.出错的原因,只有你的语句错误了.
IP MAC
192.100.168.100 'aa;bb;dd'
B:
IP MAC
192.100.168.100 'aa'
192.100.168.101 'bb'
192.100.168.102 'cc'
192.100.168.103 'dd'ALLsame:1
IPsame:0
MACsame:2这三种情况加起来就3条了,远超你A表的1条。注意你是用内联接,如果你用exists/not exists来判断,那应该相同。
select A.* into ALLsame from A where exists (select 1 from B where A.IP=B.IP and charindex(';'+B.MAC+';', ';'+A.MAC+';')>0)抽出表A中IP和表B中相同但MAC不相同的数据形成表IPsame;
--select A.* into IPsame from A,B where A.IP=B.IP and (left(A.MAC,17) <>B.MAC and right(A.MAC,17) <>B.MAC and substring(A.MAC,18,34) <>B.MAC and substring(A.MAC,35,51) <>B.MAC and substring(A.MAC,52,68) <>B.MAC and substring(A.MAC,69,85) <>B.MAC)
select A.* into IPsame from A where exists (select 1 from B where A.IP=B.IP and charindex(';'+B.MAC+';', ';'+A.MAC+';')=0)抽出表A中MAC和表B中相同但IP不相同的数据形成表MACsame;
--select A.* into MACsame from A,B where A.IP <>B.IP and (left(A.MAC,17)=B.MAC or right(A.MAC,17)=B.MAC or substring(A.MAC,18,34)=B.MAC or substring(A.MAC,35,51)=B.MAC or substring(A.MAC,52,68)=B.MAC or substring(A.MAC,69,85)=B.MAC)
select A.* into IPsame from A where exists (select 1 from B where A.IP<>B.IP and charindex(';'+B.MAC+';', ';'+A.MAC+';')>0)抽出IP和MAC两者完全不等的形成表Nosame.
select A.* into alldiff from A where not exists (select 1 from B where A.IP=B.IP or charindex(';'+B.MAC+';', ';'+A.MAC+';')>0)