一个表有两个字段,想查询那些srcip有对应5个或者5个以上连续dstip
table ip
+-------------+-------------+
| srcip | dstip |
+-------------+-------------+
| 192.168.0.2 | 192.168.1.4 |
| 192.168.0.2 | 192.168.1.3 |
| 192.168.0.2 | 192.168.1.5 |
| 192.168.0.2 | 192.168.1.6 |
| 192.168.0.2 | 192.168.1.7 |
| 192.168.0.2 | 192.168.1.2 |
| 192.168.0.5 | 192.168.1.2 |
+-------------+-------------+
我想用存储过程写一个。
BEGIN
#Routine body goes here...
DECLARE lastSrcIP VARCHAR(255) DEFAULT "";
DECLARE lastDestIP INT;
DECLARE srcIP VARCHAR(255) DEFAULT "";
DECLARE destIP INT;
DECLARE count INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE ip_cursor CURSOR FOR SELECT srcip, INET_ATON(dstip) dest_ip from ip where srcip in ( SELECT p.srcip from ip p GROUP BY srcip HAVING COUNT(p.dstip) >= 5) ORDER BY srcip, dest_ip;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN ip_cursor;
REPEAT
FETCH ip_cursor INTO srcIP, destIP;
IF done=0 THEN
INSERT INTO tbl_src_ip VALUES(UUID(), srcIP);
# 计数开始
IF srcIP = lastSrcIP THEN
IF (destIP - lastDestIP) = 1 THEN
SET COUNT = COUNT + 1;
END IF;
IF (destIP - lastDestIP) <> 1 THEN
SET COUNT = 1;
END IF;
END IF;
IF srcIP <> lastSrcIP THEN
# 新的srcIP组
SET count = 1;
SET lastSrcIP = srcIP;
SET lastDestIP = destIP;
END IF;
# 如果COUNT == 5，则找到
IF COUNT = 5 THEN
## 放入临时表验证
INSERT INTO tbl_src_ip VALUES(UUID(), srcIP);
END IF;
END IF;
UNTIL done
END REPEAT;
CLOSE ip_cursor;
END
程序还有待调通。
关键点：
mysql> SELECT srcip, INET_ATON(dstip) dest_ip from ip where srcip in ( SELECT p.srcip from ip p GROUP BY srcip HAVING COUNT(p.dstip) >= 5) ORDER BY srcip, dest_ip;
+-------------+------------+
| srcip | dest_ip |
+-------------+------------+
| 192.168.0.2 | 3232235778 |
| 192.168.0.2 | 3232235779 |
| 192.168.0.2 | 3232235780 |
| 192.168.0.2 | 3232235781 |
| 192.168.0.2 | 3232235782 |
| 192.168.0.2 | 3232235783 |
+-------------+------------+
6 rows in set
1.用GROUP BY和HAVING子句找出dstip大于等于5个的记录
2.使用INET_ATON函数将dstip转成Integer类型。
说一下思路，SQL忘的差不多了。
为了方便描述，表简化为src为字符串，dist为整数，表称为t。
t join自己 t1，条件为src相等，t1.dist-t.dist in [0，4]
结果为
IPa 1 1
IPa 1 2
……
IPa 1 5
从这个结果group by t.src， t.dist having count(*) = 5