find-invalid-ip-addresses

java

python

python3

mysql

mssql

oraclesql

csharp

javascript

typescript

bash

swift

kotlin

dart

golang

ruby

scala

rust

racket

erlang

elixir

pythondata

postgresql

cangjie

Database

Table: <code> logs</code>

<pre>
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| log_id | int |
| ip | varchar |
| status_code | int |
+-------------+---------+
log_id is the unique key for this table.
Each row contains server access log information including IP address and HTTP status code.
</pre>

Write a solution to find invalid IP addresses. An IPv4 address is invalid if it meets any of these conditions:

<ul>
	<li>Contains numbers greater than <code>255</code> in any octet</li>
	<li>Has leading zeros in any octet (like <code>01.02.03.04</code>)</li>
	<li>Has less or more than <code>4</code> octets</li>
</ul>

Return the result table ordered by <code>invalid_count</code>,&nbsp;<code>ip</code>&nbsp;in descending order respectively.&nbsp;

The result format is in the following example.

&nbsp;
Example:

<div class="example-block">
Input:

logs table:

<pre class="example-io">
+--------+---------------+-------------+
| log_id | ip | status_code | 
+--------+---------------+-------------+
| 1 | 192.168.1.1 | 200 | 
| 2 | 256.1.2.3 | 404 | 
| 3 | 192.168.001.1 | 200 | 
| 4 | 192.168.1.1 | 200 | 
| 5 | 192.168.1 | 500 | 
| 6 | 256.1.2.3 | 404 | 
| 7 | 192.168.001.1 | 200 | 
+--------+---------------+-------------+
</pre>

Output:

<pre class="example-io">
+---------------+--------------+
| ip | invalid_count|
+---------------+--------------+
| 256.1.2.3 | 2 |
| 192.168.001.1 | 2 |
| 192.168.1 | 1 |
+---------------+--------------+
</pre>

Explanation:

<ul>
	<li>256.1.2.3&nbsp;is invalid because 256 &gt; 255</li>
	<li>192.168.001.1&nbsp;is invalid because of leading zeros</li>
	<li>192.168.1&nbsp;is invalid because it has only 3 octets</li>
</ul>

The output table is ordered by invalid_count, ip in descending order respectively.
</div>

find_invalid_ips

Find Invalid IP Addresses

表：<code>logs</code>

<pre>
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| log_id | int |
| ip | varchar |
| status_code | int |
+-------------+---------+
log_id 是这张表的唯一主键。
每一行包含服务器访问日志信息，包括 IP 地址和 HTTP 状态码。
</pre>

编写一个解决方案来查找 无效的 IP 地址。一个 IPv4 地址如果满足以下任何条件之一，则无效：

<ul>
	<li>任何 8 位字节中包含大于 255 的数字</li>
	<li>任何 8 位字节中含有 前导零（如&nbsp;<code>01.02.03.04</code>）</li>
	<li>少于或多于&nbsp;<code>4</code>&nbsp;个 8 位字节</li>
</ul>

返回结果表分别以&nbsp;<code>invalid_count</code>，<code>ip</code>&nbsp;降序&nbsp;排序。

结果格式如下所示。

&nbsp;

示例：

<div class="example-block">
输入：

logs 表：

<pre class="example-io">
+--------+---------------+-------------+
| log_id | ip | status_code | 
+--------+---------------+-------------+
| 1 | 192.168.1.1 | 200 | 
| 2 | 256.1.2.3 | 404 | 
| 3 | 192.168.001.1 | 200 | 
| 4 | 192.168.1.1 | 200 | 
| 5 | 192.168.1 | 500 | 
| 6 | 256.1.2.3 | 404 | 
| 7 | 192.168.001.1 | 200 | 
+--------+---------------+-------------+
</pre>

输出：

<pre class="example-io">
+---------------+--------------+
| ip | invalid_count|
+---------------+--------------+
| 256.1.2.3 | 2 |
| 192.168.001.1 | 2 |
| 192.168.1 | 1 |
+---------------+--------------+
</pre>

解释：

<ul>
	<li>256.1.2.3 是无效的，因为&nbsp;256 &gt; 255</li>
	<li>192.168.001.1 是无效的，因为有前导零</li>
	<li>192.168.1 是非法的，因为只有 3 个 8 位字节</li>
</ul>

输出表分别以&nbsp;<code>invalid_count</code>，<code>ip</code>&nbsp;降序排序。
</div>