php - 查找两个表中 1 个字段的数据重叠 [MySQL]

Question

我有一个表“site_ipv4”。它有一个 ip_binary 字段。我需要比较特定 ip_network_length 记录的 ip_binary 表示形式，看看它是否与同一个表中 id_status =“A”记录的另一个匹配。如果它们匹配，请将它们写在报告中。

例如，假设您拥有三个带有 ipv4 地址的站点许可证:

user_id | id_status | ip_binary                        | ip_network_length
800AA   | A         | 10000011111001100100101011110010 | 32
800AB   | A         | 10000011111001100100101000000000 | 24
800AC   | X         | 10000011111001100000000000000000 | 16

(1) 看第一个，32 位长度意味着您拥有整个字符串(“10000011111001100100101011110010”)。这与第二个帐户不匹配。第三个帐户是 id_status = “X”，因此您可以忽略它。

(2) 查看第二个，24 位长度意味着您拥有较短的字符串(“100000111110011001001010”)。这与第一个帐户的前 24 位匹配，因此您必须报告此处存在重叠。第三个帐户是 id_status = “X”，因此您可以忽略它。

(3) 看第三个，它是 id_status = “X”，所以你可以忽略它。

如何比较字段以找到重叠的 ip_binary？

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(user_id VARCHAR(12) NOT NULL PRIMARY KEY
,id_status CHAR(1) NOT NULL
,ip_binary CHAR(32) NOT NULL
,ip_network_length INT NOT NULL
);

INSERT INTO my_table VALUES
('800AA','A',10000011111001100100101011110010,32),
('800AB','A',10000011111001100100101000000000,24),
('800AC','X',10000011111001100000000000000000,16);


SELECT x.*
     , y.user_id
  FROM my_table x
  JOIN my_table y
    ON y.user_id <> x.user_id
   AND y.ip_binary LIKE CONCAT(SUBSTR(x.ip_binary,1,x.ip_network_length),'%')
 WHERE x.id_status <> 'x';

+---------+-----------+----------------------------------+-------------------+---------+
| user_id | id_status | ip_binary                        | ip_network_length | user_id |
+---------+-----------+----------------------------------+-------------------+---------+
| 800AB   | A         | 10000011111001100100101000000000 |                24 | 800AA   |
+---------+-----------+----------------------------------+-------------------+---------+