假设有一个只有一个字段的表。表名为address
,有一个名为ip
的字段,其中包含一个IPV4地址作为其值
192.168.120.201
192.168.120.202
192.168.120.203
192.168.120.204
192.168.120.205
192.168.121.3
192.168.121.50
我需要在这个表上运行一个查询,它将返回数据COUNT
上的前三个八位
预期输出
网络数192.168.120 5
192.168.121 3
我尝试使用SUBSTR
like
SELECT SUBSTR(ip,1,10) as network,COUNT(*) as c FROM address GROUP BY network HAVING(c>1)
但问题是,这个SUBSTR
只会像预期的那样工作,如果所有的前3个八位组每个都有3个数字,但是这将在任何ip地址上打破,在前三个八位组中每个都没有3个数字。例如,
192.168.0.0到
192.2.3.50
192.23.4.60
是否有任何替代上述查询将在上述所有情况下工作?
不要做字符串操作。你最好将ip转换为整数并使用一些位掩码,例如
SELECT INET_NTOA(INET_ATON(ipfield) & 0xFFFFFF00)
您可以使用substring_index
来做到这一点:
SELECT substring_index(network, '.', 3) AS Octet,
COUNT(*)
FROM address
GROUP BY Octet
下面是一个SQLFiddle的例子
我建议使用SUBSTRING_INDEX
:
SELECT SUBSTRING_INDEX(ip, '.', 3) as network, COUNT(*) as c
FROM address
GROUP BY network
HAVING(c>1)
LIMIT 500