Início / dba / Perguntas / 100788
Accepted
Jehad Keriaki
Asked: 2015-05-07 10:24:58 +0800 CST

Obtendo a contagem do mesmo valor em linhas consecutivas que são ordenadas por campos diferentes

  • 772

Tenho uma tabela com a seguinte estrutura

finals(ID, name, result)

CREATE TABLE `finals` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `result` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Onde:

  • IDé uma tecla de incremento automático,
  • nameé o nome da pessoa,
  • resulté aprovado ou reprovado

Dados de amostra

mysql> select * from finals;
+----+------+--------+
| id | name | result |
+----+------+--------+
|  1 | John | Pass   |
|  2 | John | Fail   |
|  3 | John | Pass   |
|  4 | Kyle | Pass   |
|  5 | John | Pass   |
|  6 | Kyle | Pass   |
|  7 | Kyle | Pass   |
|  8 | Kyle | Fail   |
+----+------+--------+
8 rows in set (0.00 sec)

Explicação

Estou tentando obter os nomes das pessoas que têm pelo menos 3 "Pass" consecutivos . Portanto, o resultado desejado é: "Kyle" Porque "Kyle" tem 3 "Pass" cosecutivos, IDs 4, 6, 7 .

Observe que não me importo se esta sequência for interrompida por um registro de outra pessoa, mas me importo se for interrompida por um registro da mesma pessoa com o valor "Fail" no resultado.

É por isso que "John" não está no resultado desejado. ou seja, embora "John" tenha 3 "pass" no total, eles são interrompidos com um "Fail", então quero excluí-lo do resultado.

Eu tentei esta consulta:

mysql> SELECT name, GROUP_CONCAT(result ORDER BY id) as result_str FROM finals GROUP BY name having result_str LIKE '%Pass,Pass,Pass%';
+------+---------------------+
| name | result_str          |
+------+---------------------+
| Kyle | Pass,Pass,Pass,Fail |
+------+---------------------+
1 row in set (0.00 sec)

Funciona, e este é o resultado desejado. No entanto, estou procurando maneiras alternativas de

  • Seja mais genérico
  • evite as restrições no comprimento do resultado de GROUP_CONCAT.

Agradeço antecipadamente.

mysql

1 respostas

  1. Best Answer

    Eu tenho uma solução que não precisaGROUP_CONCAT

    SOLUÇÃO PROPOSTA

    SET @x = 0;
SET @name = '';
SET @result = '';
SELECT name,consecutive FROM
(SELECT
    name,
    (@nametag:=MD5(CONCAT(name,':',result))),
    (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    (@name:=@nametag) inc
FROM finals ORDER BY name,id) A
WHERE consecutive >= 3;

    SOLUÇÃO PROPOSTA EXECUTADA

    mysql> SELECT name,consecutive FROM
    -> (SELECT
    ->     name,
    ->     (@nametag:=MD5(CONCAT(name,':',result))),
    ->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    ->     (@name:=@nametag) inc
    -> FROM finals ORDER BY name,id) A
    -> WHERE consecutive >= 3;
+------+-------------+
| name | consecutive |
+------+-------------+
| Kyle |           3 |
+------+-------------+
1 row in set (0.02 sec)

mysql>

    SAÍDA DA SUBCONSULTA

    mysql> SELECT
    ->     name,
    ->     (@nametag:=MD5(CONCAT(name,':',result))),
    ->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    ->     (@name:=@nametag) inc
    -> FROM finals ORDER BY name,id;
+------+------------------------------------------+-------------+----------------------------------+
| name | (@nametag:=MD5(CONCAT(name,':',result))) | consecutive | inc                              |
+------+------------------------------------------+-------------+----------------------------------+
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 534b3d163a04b74a72c6dbe68db1c01e         |           1 | 534b3d163a04b74a72c6dbe68db1c01e |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           2 | 84cc30b986fe149dfb765dd09fad8a60 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           1 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           2 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           3 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 4a4e0aaa102c37f098bd6afd13ccfea0         |           1 | 4a4e0aaa102c37f098bd6afd13ccfea0 |
+------+------------------------------------------+-------------+----------------------------------+
8 rows in set (0.00 sec)

mysql>

    DE UMA CHANCE !!!

    Acabei de responder a uma pergunta semelhante há 2 dias ( Valor lógico da expressão de atribuição )

    • 3

relate perguntas