我有问题，在MySQL查询与许多子查询和用户定义的变量

wswtfjt7 于 2023-03-17 发布在 Mysql

关注(0)|答案(1)|浏览(129)

我需要对所有相交的IP范围进行并集运算，然后检查单个IP地址是否在其中的某个范围内。
我有这样的表，它只是示例mysql> select * from ips_test;

`+----+-------------+------+------+
| id | ip          | flag | fol  |
+----+-------------+------+------+
|  1 | 10.10.10.10 | f    |   -1 |
|  2 | 10.10.10.18 | l    |    1 |
|  3 | 10.10.10.13 | f    |   -1 |
|  4 | 10.10.10.19 | l    |    1 |
|  5 | 10.10.10.20 | f    |   -1 |
|  6 | 10.10.10.22 | l    |    1 |
|  7 | 10.10.10.21 | f    |   -1 |
|  8 | 10.10.10.23 | l    |    1 |
|  9 | 10.10.10.11 | a    |    0 |
| 10 | 10.10.10.16 | a    |    0 |
| 11 | 10.10.10.18 | a    |    0 |
| 12 | 10.10.10.19 | a    |    0 |
| 13 | 10.10.10.26 | a    |    0 |
| 14 | 10.10.10.10 | a    |    0 |
+----+-------------+------+------+
14 rows in set (0.00 sec)`

这里，“F”标志表示这是IP范围的开始，“L”是范围的结束，而“A”是单独的IP地址。“F0L”是用于我的方法的标志，该方法用于产生大量的相互交叉的IP范围的并集。
我可以通过下一个查询找到这些联合

select ip, flag, fol, block_border from
(
select *,
IF(block_border=-1,@chain:=@chain+1,@chain:=0) as "min_one_chain" from
(
select * from
(
select *, sum(fol) over(order by ip_num) as block_border from
(select *, INET_ATON(ip) as ip_num from ips_test order by ip_num
) as sorted_fol
) as bl_border
where block_border=0 OR (block_border=-1 AND fol=-1)
) as almost_done, (select @chain:=0) as init
) as done where (min_one_chain=0 OR min_one_chain=1) AND (flag!='a');

具有下一个输出

+-------------+------+------+--------------+
| ip          | flag | fol  | block_border |
+-------------+------+------+--------------+
| 10.10.10.10 | f    |   -1 |           -1 |
| 10.10.10.19 | l    |    1 |            0 |
| 10.10.10.20 | f    |   -1 |           -1 |
| 10.10.10.23 | l    |    1 |            0 |
+-------------+------+------+--------------+
4 rows in set, 3 warnings (0.00 sec)

所以我找到了我在这一步需要的东西，排序的IP范围，它是所有相交的IP范围的并集。
接下来，我需要输出所有的ip地址，但是要有唯一的、不相交的ip范围，并排除在这些范围内的单个ip地址。
这里我遇到了问题，下一个查询

select ip, ip_num, flag, fol,
IF(flag='f', @range_inner:=1, @range_inner:=@range_inner) as "inner_f",
IF(flag='l', @range_inner:=0, @range_inner:=@range_inner) as "inner_l"
from
(
(select ip, flag, fol, INET_ATON(ip) as ip_num from ips_test where flag='a')
UNION
(
select ip, flag, fol, ip_num from
(
select *,
IF(block_border=-1,@chain:=@chain+1,@chain:=0) as "min_one_chain" from
(
select * from
(
select *, sum(fol) over(order by ip_num) as block_border from
(select *, INET_ATON(ip) as ip_num from ips_test order by ip_num
) as sorted_fol
) as bl_border
where block_border=0 OR (block_border=-1 AND fol=-1)
) as almost_done, (select @chain:=0) as init
) as done where min_one_chain=0 OR min_one_chain=1
) order by ip_num asc, fol asc
) as ordered_ips, (select @range_inner:=0) as init where "inner_l"=0 OR flag!='a';

其产生以下输出

+-------------+-----------+------+------+---------+---------+
| ip          | ip_num    | flag | fol  | inner_f | inner_l |
+-------------+-----------+------+------+---------+---------+
| 10.10.10.10 | 168430090 | f    |   -1 |       1 |       1 |
| 10.10.10.10 | 168430090 | a    |    0 |       1 |       1 |
| 10.10.10.11 | 168430091 | a    |    0 |       1 |       1 |
| 10.10.10.16 | 168430096 | a    |    0 |       1 |       1 |
| 10.10.10.18 | 168430098 | a    |    0 |       1 |       1 |
| 10.10.10.19 | 168430099 | a    |    0 |       1 |       1 |
| 10.10.10.19 | 168430099 | l    |    1 |       1 |       0 |
| 10.10.10.20 | 168430100 | f    |   -1 |       1 |       1 |
| 10.10.10.23 | 168430103 | l    |    1 |       1 |       0 |
| 10.10.10.26 | 168430106 | a    |    0 |       0 |       0 |
+-------------+-----------+------+------+---------+---------+
10 rows in set, 9 warnings (0.00 sec)

而这正是我想得到的

+-------------+-----------+------+------+---------+---------+
| ip          | ip_num    | flag | fol  | inner_f | inner_l |
+-------------+-----------+------+------+---------+---------+
| 10.10.10.10 | 168430090 | f    |   -1 |       1 |       1 |
| 10.10.10.19 | 168430099 | l    |    1 |       1 |       0 |
| 10.10.10.20 | 168430100 | f    |   -1 |       1 |       1 |
| 10.10.10.23 | 168430103 | l    |    1 |       1 |       0 |
| 10.10.10.26 | 168430106 | a    |    0 |       0 |       0 |
+-------------+-----------+------+------+---------+---------+
10 rows in set, 9 warnings (0.00 sec)

不包括

+-------------+-----------+------+------+---------+---------+
| ip          | ip_num    | flag | fol  | inner_f | inner_l |
+-------------+-----------+------+------+---------+---------+
| 10.10.10.10 | 168430090 | f    |   -1 |       1 |       1 |
| 10.10.10.10 | 168430090 | a    |    0 |       1 |       1 | !!!! theese
| 10.10.10.11 | 168430091 | a    |    0 |       1 |       1 | !!!! ip
| 10.10.10.16 | 168430096 | a    |    0 |       1 |       1 | !!!! adresses
| 10.10.10.18 | 168430098 | a    |    0 |       1 |       1 | !!!! which are inside 
| 10.10.10.19 | 168430099 | a    |    0 |       1 |       1 | !!!! ip range
| 10.10.10.19 | 168430099 | l    |    1 |       1 |       0 |
| 10.10.10.20 | 168430100 | f    |   -1 |       1 |       1 |
| 10.10.10.23 | 168430103 | l    |    1 |       1 |       0 |
| 10.10.10.26 | 168430106 | a    |    0 |       0 |       0 |
+-------------+-----------+------+------+---------+---------+
10 rows in set, 9 warnings (0.00 sec)

我该如何解决这个问题？
为方便起见，我将提供表创建代码和insert语句

mysql> show create table ips_test;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                      |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ips_test | CREATE TABLE `ips_test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ip` char(15) DEFAULT NULL,
  `flag` enum('a','f','l') DEFAULT NULL,
  `fol` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

和INSERT语句

mysql> insert into for_ips.ips_test(ip,flag,fol) values('10.10.10.10','f',-1),('10.10.10.18','l',1),('10.10.10.13','f',-1),('10.10.10.19','l',1);

mysql> insert into for_ips.ips_test(ip,flag,fol) values('10.10.10.20','f',-1),('10.10.10.22','l',1),('10.10.10.21','f',-1),('10.10.10.23','l',1);

mysql> insert into ips_test(ip,flag,fol) values ('10.10.10.11','a',0),('10.10.10.16','a',0),('10.10.10.18','a',0),('10.10.10.19','a',0),('10.10.10.26','a',0);

mysql

来源：https://stackoverflow.com/questions/75754000/i-have-problem-in-mysql-query-with-many-subqueries-and-user-defined-variables

1条答案

按热度按时间

7lrncoxx1#

正在折叠IP范围：

WITH 
cte1 AS (
  SELECT DISTINCT
         INET_ATON(ip) ip,
         SUM(CASE flag WHEN 'f' THEN 1
                       WHEN 'l' THEN -1
                       ELSE 0
                       END) OVER (ORDER BY ip) weight
  FROM ips_test
),
cte2 AS (
  SELECT ip, SUM(weight = 0) OVER (ORDER BY ip DESC) grp
  FROM cte1
)
SELECT INET_NTOA(MIN(ip)) ip_start,
       INET_NTOA(MAX(ip)) ip_end
FROM cte2
GROUP BY grp
ORDER BY 1;

| ip_启动|IP_结束|
| - ------|- ------|
| 10.10.10.10 | 10.10.10.19 |
| 10.10.10.20 | 10.10.10.23 |
| 10.10.10.26 | 10.10.10.26 |
fiddle和一些解释。
输出与您想要的不同，但我希望您可以编辑外部查询（或将其转换为第三个CTE并添加另一个外部查询），并获得您需要的输出。
PS.查询假设您的源数据是一致的。

赞(0）回复(0）举报 2023-03-17

我来回答

我有问题，在MySQL查询与许多子查询和用户定义的变量

1条答案

相关问题

热门标签

最新问答