Início / dba / Perguntas / 133828
Accepted
rwcommand
Asked: 2016-03-31 06:20:07 +0800 CST

Listando linhas com uma condição WHERE

  • 772

Estou lutando com uma consulta SQL que lista os hosts que estão offline.

Problema

O problema ocorre quando preciso listar hosts que não possuem nenhuma porta aberta (status = 0), portanto, olhando a tabela na parte inferior, deve listar apenas 192.168.1.2, porém lista todos os hosts.

Eu tentei muitas consultas + subconsultas sem sorte ainda, ficaria grato se você pudesse me dizer onde estou errando e me informar qual é a consulta correta. Obrigada.

MariaDB [scanner]> SELECT DISTINCT ports.ip_add FROM ports WHERE ports.status = FALSE;
+-------------+
| ip_add      |
+-------------+
| 192.168.1.1 |
| 192.168.1.2 |
| 192.168.1.3 |
+-------------+

Consulta de trabalho semelhante

Esta consulta lista todos os hosts que possuem pelo menos uma porta aberta (status = 1), ótimo.

MariaDB [scanner]> SELECT DISTINCT ports.ip_add FROM ports WHERE ports.status = TRUE;
+-------------+
| ip_add      |
+-------------+
| 192.168.1.1 |
| 192.168.1.3 |
+-------------+

Mesa

MariaDB [scanner]> SELECT * FROM ports LIMIT 9;
+--------+-------------+----------+------------+---------------------+
| id     | ip_add      | port     | status     | probe_meta          |
+--------+-------------+----------+------------+---------------------+
|      1 | 192.168.1.1 |       22 |          1 | 2016-03-29 00:01:00 |
|      2 | 192.168.1.1 |       21 |          1 | 2016-03-29 00:02:00 |
|      3 | 192.168.1.1 |       23 |          1 | 2016-03-29 00:03:00 |
|      4 | 192.168.1.2 |       22 |          0 | 2016-03-29 00:05:00 |
|      5 | 192.168.1.2 |       21 |          0 | 2016-03-29 00:06:00 |
|      6 | 192.168.1.2 |       23 |          0 | 2016-03-29 00:07:00 |
|      7 | 192.168.1.3 |       22 |          1 | 2016-03-29 00:09:00 |
|      8 | 192.168.1.3 |       21 |          0 | 2016-03-29 00:10:00 |
|      9 | 192.168.1.3 |       23 |          0 | 2016-03-29 00:11:00 |
+--------+-------------+----------+------------+---------------------+
mysql mysql-5.5

3 respostas

  1. Best Answer

    Outra forma de fazer isso seria agregar a statuscoluna por ip_addr, onde o total é igual a zero.

    O banco de ensaio:

    CREATE TABLE `ports`
(
    `id` INT NOT NULL
    , `ip_add` VARCHAR(15) NOT NULL
    , `port` INT NOT NULL
    , `status` BIT NOT NULL
    , `probe_meta` DATETIME
);

INSERT INTO `ports` 
VALUES (1, '192.168.0.1', 22, 1, '2016-03-21 00:00:00');

INSERT INTO `ports` 
VALUES (1, '192.168.0.2', 22, 0, '2016-03-21 00:01:00');

INSERT INTO `ports` 
VALUES (1, '192.168.0.3', 22, 1, '2016-03-21 00:02:00');

INSERT INTO `ports` 
VALUES (1, '192.168.0.3', 22, 0, '2016-03-21 00:03:00');

    A pergunta:

    SELECT ip_add
FROM `ports`
GROUP BY ip_add
HAVING SUM(status) = 0;

    Os resultados:

    insira a descrição da imagem aqui

    SQLFiddleName

    • 4

  2. A sintaxe do MySQL não é minha especialidade, mas esta é a maneira lógica de fazer isso no T-SQL, então você entende como precisa filtrar:

    select distinct ip_add 
from ports 
where status = 0
and ip_add not in 
(
    select ip_add 
    from ports 
    where status = 1
    and ip_add is not null
)

    Esta é a maneira rápida e suja, embora existam maneiras mais eficientes de fazer isso se o conjunto de dados for grande, mas esperamos que você tenha uma ideia.

    • 3

  3. O problema que você descreve é ​​conhecido como divisão relacional. Em matemática você poderia dizer algo como: FORALL x: p(x). Isso porém não é possível expressar em SQL, então teremos que transformar a formulação do problema. Uma maneira comum é dizer que: o NOT EXISTS x:NOT p(x)que pode ser expresso em SQL. No entanto, no seu caso, provavelmente é mais fácil apenas contar as portas com status 0 e comparar com o número total de portas:

    SELECT ports.ip_add 
FROM ports
GROUP BY ports.ip_add
HAVING COUNT(1) = COUNT(case when ports.status = 0 THEN 1 END);
    • 3

relate perguntas