首頁(yè) 猿問(wèn) sql 求一個(gè)算法

sql 求一個(gè)算法

JavaScript

回首憶惘然 2019-04-08 09:21:36

一個(gè)表有兩個(gè)字段,想查詢那些srcip有對(duì)應(yīng)5個(gè)或者5個(gè)以上連續(xù)dstiptableip+-------------+-------------+|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|+-------------+-------------+

查看完整描述

2 回答

紅糖糍粑

TA貢獻(xiàn)1815條經(jīng)驗(yàn) 獲得超6個(gè)贊

我想用存儲(chǔ)過(guò)程寫一個(gè)。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);#計(jì)數(shù)開(kāi)始IFsrcIP=lastSrcIPTHENIF(destIP-lastDestIP)=1THENSETCOUNT=COUNT+1;ENDIF;IF(destIP-lastDestIP)<>1THENSETCOUNT=1;ENDIF;ENDIF;IFsrcIP<>lastSrcIPTHEN#新的srcIP組SETcount=1;SETlastSrcIP=srcIP;SETlastDestIP=destIP;ENDIF;#如果COUNT==5，則找到IFCOUNT=5THEN##放入臨時(shí)表驗(yàn)證INSERTINTOtbl_src_ipVALUES(UUID(),srcIP);ENDIF;ENDIF;UNTILdoneENDREPEAT;CLOSEip_cursor;END程序還有待調(diào)通。關(guān)鍵點(diǎn)：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個(gè)的記錄2.使用INET_ATON函數(shù)將dstip轉(zhuǎn)成Integer類型。

反對(duì) 回復(fù) 2019-04-08

一只斗牛犬

TA貢獻(xiàn)1784條經(jīng)驗(yàn) 獲得超2個(gè)贊

說(shuō)一下思路，SQL忘的差不多了。為了方便描述，表簡(jiǎn)化為src為字符串，dist為整數(shù)，表稱為t。tjoin自己t1，條件為src相等，t1.dist-t.distin[0，4]結(jié)果為IPa11IPa12……IPa15從這個(gè)結(jié)果groupbyt.src，t.disthavingcount(*)=5