sql 求一个算法

Question

一个表有两个字段,想查询那些srcip有对应5个或者5个以上连续dstip
tableip
+-------------+-------------+
|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|
+-------------+-------------+

Answer 1

我想用存储过程写一个。BEGIN#Routinebodygoeshere...DECLARElastSrcIPVARCHAR(255)DEFAULT"";DECLARElastDestIPINT;DECLAREsrcIPVARCHAR(255)DEFAULT"";DECLAREdestIPINT;DECLAREcountINTDEFAULT0;DECLAREdoneINTDEFAULT0;DECLAREip_cursorCURSORFORSELECTsrcip,INET_ATON(dstip)dest_ipfromipwheresrcipin(SELECTp.srcipfromippGROUPBYsrcipHAVINGCOUNT(p.dstip)>=5)ORDERBYsrcip,dest_ip;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;OPENip_cursor;REPEATFETCHip_cursorINTOsrcIP,destIP;IFdone=0THENINSERTINTOtbl_src_ipVALUES(UUID(),srcIP);#计数开始IFsrcIP=lastSrcIPTHENIF(destIP-lastDestIP)=1THENSETCOUNT=COUNT+1;ENDIF;IF(destIP-lastDestIP)1THENSETCOUNT=1;ENDIF;ENDIF;IFsrcIPlastSrcIPTHEN#新的srcIP组SETcount=1;SETlastSrcIP=srcIP;SETlastDestIP=destIP;ENDIF;#如果COUNT==5，则找到IFCOUNT=5THEN##放入临时表验证INSERTINTOtbl_src_ipVALUES(UUID(),srcIP);ENDIF;ENDIF;UNTILdoneENDREPEAT;CLOSEip_cursor;END程序还有待调通。关键点：mysql>SELECTsrcip,INET_ATON(dstip)dest_ipfromipwheresrcipin(SELECTp.srcipfromippGROUPBYsrcipHAVINGCOUNT(p.dstip)>=5)ORDERBYsrcip,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|+-------------+------------+6rowsinset1.用GROUPBY和HAVING子句找出dstip大于等于5个的记录2.使用INET_ATON函数将dstip转成Integer类型。

Answer 2

说一下思路，SQL忘的差不多了。为了方便描述，表简化为src为字符串，dist为整数，表称为t。tjoin自己t1，条件为src相等，t1.dist-t.distin[0，4]结果为IPa11IPa12……IPa15从这个结果groupbyt.src，t.disthavingcount(*)=5