Translate

terça-feira, 17 de fevereiro de 2015

Mysql - rank de valores (numerar registros de acordo com o valor de uma coluna)

Neste artigo, vamos apresentar 7 exemplos de como exibir as posições de acordo com valores de uma coluna, ou explicando de uma forma mais simples, como ranquear os valores de uma coluna.

No 1º exemplo não levamos em consideração os empates, a partir 2º exemplo os empates serão considerados.

Caso tenha interesse, faça o download dos exemplos ou veja o script no github.
Você também pode executar os exemplos através do sqlfiddle

1º) Exemplo

Queremos exibir a classificação dos atletas em uma corrida de acordo com o tempo gasto para completar a prova.
Para exibir a classificação vamos utilizar a tabela "tb_corrida" exibida abaixo:



SOLUÇÃO:

Podemos resolver este problema de duas formas:

1ª FORMA:  declarar uma variável e utiliza-lá em um select. No nosso exemplo, vamos utilizar a variável @posicao. A variável posição é incrementada 1 unidade a cada registro.
Os registros foram ordenados de acordo com o tempo gasto através da clausula "ORDER BY".

SET @posicao:=0;
SELECT
    @posicao:=@posicao+1 AS posicao,
    inscricao,
    grupo,
    TIME_FORMAT(tempo_gasto, '%H:%i:%s') AS tempo_gasto
FROM tb_corrida
ORDER BY tempo_gasto;

Após a execução da sentença acima, teremos o resultado a seguir:


2ª FORMA:  também é possível, usar uma variável dentro de uma subquery sem a necessidade de declara-lá no inicio através da clausula "SET".
No nosso caso, criamos uma subquery que recebeu o alias (apelido) de "classificacao". Fizemos um "CROSS JOIN" da subquery "classificacao" com a tabela  "tb_corrida".
Neste exemplo, vamos utilizar a variável @posicao

SELECT
    @posicao := @posicao + 1 AS posicao,
    tb_corrida.inscricao,
    tb_corrida.grupo,
    TIME_FORMAT(tb_corrida.tempo_gasto, '%H:%i:%s') AS tempo_gasto
FROM
(SELECT @posicao := 0) classificacao CROSS JOIN tb_corrida
ORDER BY tempo_gasto;

2º) Exemplo

Queremos exibir a classificação dos atletas em uma corrida de acordo com o tempo gasto para completar a prova, levando em consideração o empate. Vamos utilizar a mesma tabela do 1º exemplo "tb_corrida".


SOLUÇÃO:

Podemos resolver este problema de duas formas:

1ª FORMA: declarar duas variáveis e utilizá-las em um select. No nosso exemplo, vamos utilizar a variável @posicao e a variável @tempo_gasto.
Caso o valor do tempo for igual ao tempo anterior manteremos o valor da variável @posicao, caso contrário o valor da variável @posicao será  incrementado em uma 1 unidade.
 

SET @posicao:=0;
SET @tempo_gasto:=NULL;
SELECT
@posicao:=
    CASE
        WHEN CAST(@tempo_gasto AS TIME)=tempo_gasto THEN @posicao
        ELSE @posicao + 1
    END
AS posicao,
inscricao,
grupo,
@tempo_gasto:=  CAST(tempo_gasto AS TIME) as tempo_gasto
FROM tb_corrida
ORDER BY tempo_gasto;

Após a execução da sentença acima,  teremos o resultado da imagem seguir:




2ª FORMA: também é possível, usar as variáveis dentro de uma subquery, sem a necessidade declará-las através da clausulá "SET"
Devemos fazer um "CROSS JOIN" da subquery "classificacao" com a tabela tabela "tb_corrida".

SELECT
@posicao:=
    CASE
        WHEN CAST(@tempo_gasto AS TIME) =tempo_gasto THEN @posicao
        ELSE @posicao + 1
    END
AS posicao,
inscricao,
grupo,
@tempo_gasto:= tempo_gasto as tempo_gasto
FROM
(SELECT @posicao:=0, @tempo_gasto:=CAST(NULL AS time)) classificacao 
CROSS JOIN tb_corrida
ORDER BY tempo_gasto;


3º) Exemplo

Exibir a classificação dos atletas por grupo ("A", "B", "C").

SOLUÇÃO

Podemos resolver este problema de duas formas:

1ª FORMA

Observe que os registros foram ordenados por grupo e tempo gasto.
Cada vez que se inicia um novo grupo é atribuído o valor 1 a variável @posicao, para que a contagem seja reiniciada.
Veja o trecho destacado em azul.

SET @posicao:=0;
SET @grupo:='';
SET @tempo_gasto:=cast('00:00:00' AS time);
SELECT
@posicao:=
    CASE
        WHEN @grupo<>grupo THEN 1
        WHEN CAST(@tempo_gasto AS TIME) = tempo_gasto THEN @posicao
        ELSE @posicao + 1
    END
AS posicao,
inscricao,
@grupo := grupo as grupo,
@tempo_gasto:= CAST(tempo_gasto AS TIME)  as tempo_gasto
FROM tb_corrida
ORDER BY grupo, tempo_gasto;

Após a execução da sentença acima, teremos o resultado da imagem seguir:



2ª FORMA: também podemos resolver o terceiro exemplo utilizando subquery.

SELECT
@posicao:=
    CASE
        WHEN @grupo<>grupo THEN 1
        WHEN  CAST(@tempo_gasto AS TIME) = tempo_gasto  THEN @posicao
        ELSE @posicao + 1
    END
AS posicao,
inscricao,
@grupo := grupo AS grupo,
@tempo_gasto:=  CAST(tempo_gasto AS TIME) as tempo_gasto
FROM
(SELECT @posicao := 0, @tempo_gasto := CAST(NULL AS time), @grupo:='') classificacao
CROSS JOIN tb_corrida
ORDER BY grupo, tempo_gasto;

4º) Exemplo

Exibir a classificação dos atletas até a 4ª posição.

SOLUÇÃO:

Para resolver este problema os registros foram ordenados por tempo gasto.
Foi criada uma subquery, a qual chamamos de "tb_colocacoes".
A seguir, foram filtrados os registros com posição menor ou igual a 4.

SET @posicao:= 0;
SET @tempo_gasto:= NULL;
SELECT * FROM
(
    SELECT
    @posicao:=
        CASE
            WHEN CAST(@tempo_gasto AS time) = tempo_gasto THEN @posicao
            ELSE @posicao + 1
        END
    AS posicao,
    inscricao,
    grupo,
    @tempo_gasto:= CAST(tempo_gasto AS TIME) as tempo_gasto
    FROM tb_corrida
    ORDER BY tempo_gasto
) tb_colocacoes WHERE posicao <= 4;

Após a execução da sentença acima,  teremos o seguinte resultado:



5º) Exemplo

Os atletas que alcançaram até a 2ª colocação por grupo

SOLUÇÃO:

Para resolver este problema os registros foram ordenados por grupo e tempo gasto.
Foi utilizada uma subquery, a qual chamamos de "tb_colocacoes".
A seguir, foram filtrados os registros com posição menor ou igual a 2.

SET @posicao :=0;
SET @grupo :='';
SET @tempo_gasto := NULL;
SELECT * FROM
(
    SELECT
    @posicao:=
        CASE
            WHEN @grupo<>grupo THEN 1
            WHEN CAST(@tempo_gasto AS TIME) = tempo_gasto THEN @posicao
            ELSE @posicao + 1
        END
    AS posicao,
    inscricao,
    @grupo := grupo as grupo,
    @tempo_gasto:=   CAST(tempo_gasto AS TIME) AS tempo_gasto
    FROM tb_corrida
    ORDER BY grupo, tempo_gasto
) tb_colocacoes  WHERE posicao <= 2;

Após a execução da sentença acima, teremos o resultado da imagem seguir:


6º) Exemplo

Vamos listar o ranking de vendedores de uma loja, de acordo com o total de mercadorias vendidas. Neste exemplo, vamos utilizar uma nova tabela chamada "tb_vendas". Veja a imagem da tabela "tb_vendas" a seguir:


SOLUÇÃO

Veja que antes de criar o ranking com as posições, tivemos que somar o total para cada vendedor, esta soma foi armazenada em uma subquery que recebeu o alias (apelido) de "total vendas".
Perceba que neste exemplo, estamos ordenando os totais por vendedor de forma decrescente (do maior para o menor)
 "ORDER BY total_vendedor DESC", pois os vendedores que venderão mais serão os primeiros.  

SET @posicao:=0;
SET @total_vendedor:= 0.0;
SELECT
@posicao:=
    CASE
        WHEN @total_vendedor=total_vendedor THEN @posicao
        ELSE @posicao + 1
    END
AS posicao,
vendedor_id,
@total_vendedor:= total_vendedor as total_vendedor
FROM
(
    SELECT vendedor_id, SUM(valor) AS total_vendedor FROM
    tb_vendas GROUP BY vendedor_id
) total_vendas
ORDER BY total_vendedor DESC;

Após a execução da sentença acima, teremos o resultado da imagem seguir:


7º) Exemplo

 Listar os vendedores que foram classificados até 3º lugar. Neste exemplo, também vamos utilizar a tabela "tb_vendas".

SOLUÇÃO

Armazenamos todas as posições na subquery "vendas_gerais" e filtramos os vendedores com a posição menor ou igual a 3.

SET @posicao:=0;
SET @total_vendedor:= 0.0;
SELECT * FROM
(
    SELECT
    @posicao:=
        CASE
            WHEN @total_vendedor=total_vendedor THEN @posicao
            ELSE @posicao + 1
        END
    AS posicao,
    vendedor_id,
    @total_vendedor:= total_vendedor as total_vendedor
    FROM
    (
        SELECT vendedor_id, SUM(valor) AS total_vendedor FROM
        tb_vendas GROUP BY vendedor_id
    ) total_vendas
    ORDER BY total_vendedor DESC

) vendas_gerais WHERE posicao <= 3;

Após a execução da sentença acima, teremos o resultado da imagem seguir:



Deixe o seu comentário ou sugestão.
Gostou?  Siga no Google +  ou Facebook.

19 comentários:

  1. Olá ... No 3°Exemplo > 2°Forma - Como eu faria para utilizar uma coluna com nomes classificados em ordem alfabética ao invés de tempo_gasto? Adaptei o seu código, mas a coluna "posição" fica fora de ordem.

    ResponderExcluir
    Respostas
    1. Olá, primeiramente grata pelo contato.
      Envio o script abaixo, por favor veja se ajuda, qualquer coisa, entre em contato.

      /**
      *Exemplo para ordenar por grupo e nome
      *Crie a tabela "tb_corrida"
      */

      CREATE TABLE tb_corrida
      (
      grupo char(1),
      inscricao integer,
      nome varchar(255),
      tempo_gasto time,
      CONSTRAINT pk_inscricao PRIMARY KEY(inscricao)
      );

      /**
      *Inclusão dos registros na tabela "tb_corrida"
      */
      INSERT INTO tb_corrida VALUES
      ('B', 2480, 'Mariana Rodrigues' , '00:57:42'),
      ('B', 2481, 'Claudia Santos' , '00:58:42'),
      ('A', 2482, 'Carlos Rodrigues' , '00:55:32'),
      ('A', 2483, 'Rafael Oliveira' , '00:58:42'),
      ('B', 2484, 'Juliana Silva' , '01:10:42'),
      ('A', 2485, 'Patricia Pereira' , '00:59:42'),
      ('C', 2489, 'Ronaldo Gonçalves' , '01:42:47'),
      ('C', 2490, 'Cristiano Ferreira' , '02:55:32'),
      ('C', 2495, 'Roberto Abravanel' , '00:51:32'),
      ('C', 2496, 'Cristina Dias' , '01:42:47');


      /*
      *PARA ORDENAR POR GRUPO E NOME DO ATLETA CRIEI UMA NOVA
      *SUBQUERY E ORDENEI POR GRUPO E NOME
      */
      SELECT
      grupo,
      nome,
      inscricao,
      posicao,
      tempo_gasto
      FROM
      (
      SELECT
      @posicao:=
      CASE
      WHEN @grupo<>grupo THEN 1
      WHEN CAST(@tempo_gasto AS TIME) = tempo_gasto THEN @posicao
      ELSE @posicao + 1
      END
      AS posicao,
      inscricao,
      nome,
      @grupo := grupo AS grupo,
      @tempo_gasto:= CAST(tempo_gasto AS TIME) as tempo_gasto
      FROM
      (SELECT @posicao := 0, @tempo_gasto := CAST(NULL AS time), @grupo:='') classificacao
      CROSS JOIN tb_corrida
      ORDER BY grupo, tempo_gasto
      )
      ATLETAS
      ORDER BY grupo, nome



      Excluir
    2. Este comentário foi removido pelo autor.

      Excluir
  2. Olá Kelly, seu material me ajudou muito, adaptei e funcionou perfeitamente testando no HeidiSQL, mas colocando o codigo no Excel via VBA, infelizmente com as variáveis não funcionou.
    Voce tem algum conhecimento neste sentido?

    ResponderExcluir
  3. Precisava do terceiro exemplo porem os grupos não poderiam ser de char e sim de varchar, tem como?

    ResponderExcluir
    Respostas
    1. Troque o tipo de dado do campo de char para varchar(n), onde n é o tamanho do campo.
      Neste exemplo utilizei varchar(6), mas você poderá adaptar conforme a sua necessidade.
      Veja o exemplo no link:
      https://drive.google.com/open?id=1smAeL3pgjrFwBXiirfO4BRJuZCw_4m4i

      Excluir
  4. Este comentário foi removido pelo autor.

    ResponderExcluir
  5. Bom dia. adaptei a opção 1 do 3º exemplo, mas não estou conseguindo que o campo posição seja actualizado na BD para utilizar com outras consultas. É possivel uma ajuda? Mvilela

    ResponderExcluir
    Respostas
    1. Boa Noite, desculpa a demora em responder, segue abaixo um exemplo para utilizar o ranqueamento com "JOIN" (outras consultas) para uma melhor visualização veja os links:

      http://sqlfiddle.com/#!9/11dcd5/1
      https://drive.google.com/file/d/1vrVeZxBvKwjHks8S8iB8MSkXsmXvfK70/view?usp=sharing


      /**
      *Cria a tabela "tb_atleta"
      */
      CREATE TABLE tb_atleta
      (
      inscricao integer,
      nome_atleta varchar(255),
      CONSTRAINT pk_inscricao PRIMARY KEY(inscricao)
      );

      /**
      *Cria a tabela "tb_patrocinador"
      */
      CREATE TABLE tb_patrocinador
      (
      patrocinador_id integer,
      patrocinador_nome varchar(255),
      CONSTRAINT pk_patrocinador PRIMARY KEY(patrocinador_id)
      );

      /**
      *Cria a tabela "tb_patrocinador_atleta"
      */
      CREATE TABLE tb_patrocinador_atleta
      (
      id_pat_atleta integer NOT NULL AUTO_INCREMENT,
      patrocinador_id integer,
      inscricao integer,
      CONSTRAINT pk_patr_atleta PRIMARY KEY(id_pat_atleta),
      CONSTRAINT fk_patr_atl_patrocina FOREIGN KEY(patrocinador_id) REFERENCES tb_patrocinador(patrocinador_id),
      CONSTRAINT fk_patr_atl_atleta FOREIGN KEY(inscricao) REFERENCES tb_atleta(inscricao)
      );

      /**
      *Cria a tabela "tb_corrida"
      */
      CREATE TABLE tb_corrida
      (
      id_corrida integer NOT NULL AUTO_INCREMENT,
      grupo char(1),
      inscricao integer,
      tempo_gasto time,
      CONSTRAINT pk_corrida PRIMARY KEY(id_corrida),
      CONSTRAINT fk_corrida_atleta FOREIGN KEY(inscricao) REFERENCES tb_atleta(inscricao)
      );

      /**
      *Inclusão dos registros na tabela "tb_atleta"
      */
      INSERT INTO tb_atleta VALUES
      (2480, 'Mariana Rodrigues'),
      (2481, 'Claudia Santos'),
      (2482, 'Carlos Rodrigues'),
      (2483, 'Rafael Oliveira'),
      (2484, 'Juliana Silva'),
      (2485, 'Patricia Pereira'),
      (2489, 'Ronaldo Gonçalves'),
      (2490, 'Cristiano Ferreira'),
      (2495, 'Roberto Abravanel'),
      (2496, 'Cristina Dias');

      /**
      *Inclusão dos registros na tabela "tb_corrida"
      */
      INSERT INTO tb_corrida(grupo, inscricao, tempo_gasto) VALUES
      ('B', 2480, '03:57:42'),
      ('B', 2481, '00:58:42'),
      ('A', 2482, '00:55:32'),
      ('A', 2483, '02:58:42'),
      ('B', 2484, '01:10:42'),
      ('A', 2485, '00:59:42'),
      ('C', 2489, '01:42:47'),
      ('C', 2490, '02:55:32'),
      ('C', 2495, '00:51:32'),
      ('C', 2496, '01:42:47');

      /**
      *Inclusão dos registros na tabela "tb_patrocinador"
      */
      INSERT INTO tb_patrocinador VALUES
      (1, 'Adidas'),
      (2, 'Nike'),
      (3, 'Mizuno');

      /**
      *Inclusão dos registros na tabela "tb_patrocinador_atleta"
      */
      INSERT INTO tb_patrocinador_atleta(patrocinador_id, inscricao) VALUES
      (1, 2480),
      (2, 2480),
      (3, 2480),
      (2, 2481),
      (1, 2482),
      (1, 2483),
      (3, 2484),
      (2, 2485),
      (1, 2489),
      (2, 2490),
      (3, 2495),
      (3, 2496);

      /*Exibe a classificação dos corredores (rank) na corrida*/

      SELECT
      classificacao.grupo,
      classificacao.inscricao,
      tb_atleta.nome_atleta,
      classificacao.posicao,
      classificacao.tempo_gasto,
      pat_atleta.patrocinador_nome
      FROM
      (
      SELECT
      @posicao:=
      CASE
      WHEN @grupo<>grupo THEN 1
      WHEN CAST(@tempo_gasto AS TIME) = tempo_gasto THEN @posicao
      ELSE @posicao + 1
      END
      AS posicao,
      tb_corrida.inscricao,
      @grupo := grupo AS grupo,
      @tempo_gasto:= CAST(tempo_gasto AS TIME) as tempo_gasto
      FROM
      (SELECT @posicao := 0, @tempo_gasto := CAST(NULL AS time), @grupo:='') x
      CROSS JOIN tb_corrida
      ORDER BY grupo, tempo_gasto
      ) classificacao
      LEFT JOIN tb_atleta ON tb_atleta.inscricao = classificacao.inscricao
      LEFT JOIN
      (
      select
      tb_patrocinador_atleta.inscricao,
      GROUP_CONCAT(patrocinador_nome ORDER BY patrocinador_nome) AS patrocinador_nome
      FROM tb_patrocinador_atleta
      JOIN tb_patrocinador
      ON tb_patrocinador.patrocinador_id = tb_patrocinador_atleta.patrocinador_id
      GROUP BY tb_patrocinador_atleta.inscricao
      ) pat_atleta
      ON classificacao.inscricao = pat_atleta.inscricao
      ORDER BY classificacao.grupo, tempo_gasto;

      Excluir
    2. Ou veja o link
      https://github.com/kellysc2013/dicas_sql_jquery/blob/master/mysql_classificacao_rank_join_tables_20200627_1937.sql

      Excluir
  6. Primeiramente, obrigado pelo seu artigo. Me ajudou demais!
    Tenho uma dúvida, por favor veja se consegue me ajudar.

    Eu quero classificar os registros baseado na soma de 3 campos da tabela.

    Ou seja, os campos são `pontos_jogo_1`, `pontos_jogo_2`, `pontos_jogo_3`... Preciso somar os pontos de cada jogo e classificar por quem fez mais pontos.

    ResponderExcluir
    Respostas
    1. /*
      *Cria a tabela "tb_classificacao"
      */
      CREATE TABLE tb_classificacao
      (
      time_codigo integer not null,
      time_nome varchar(255) not null,
      pontos_jogo_1 integer not null,
      pontos_jogo_2 integer not null,
      pontos_jogo_3 integer not null,
      CONSTRAINT pk_inscricao PRIMARY KEY(time_codigo)
      );

      /*
      *Inclusão dos registros na tabela "tb_classificacao"
      */
      INSERT INTO tb_classificacao VALUES
      (2479, 'A', 0,1,3),
      (2480, 'A', 0,1,3),
      (2481, 'B', 3,0,1),
      (2482, 'C', 0,1,3),
      (2483, 'D', 0,0,1),
      (2484, 'E', 1,1,1),
      (2485, 'F', 3,1,3),
      (2489, 'G', 3,3,3),
      (2490, 'H', 0,0,0),
      (2495, 'I', 0,0,3),
      (2496, 'J', 1,1,3);

      /*
      *EXEMPLO - Exibir a classificação dos times.
      *Observação: use a função "IFNULL" caso uma das colunas permita
      *dado nulo (coluna null).
      *A função IFNULL substitui "nulo" por "0" zero.
      *Se ao somar colunas o valor de pelo menos uma delas for nulo
      *o resultado será nulo e não é esse o resultado esperado.
      */
      SELECT
      @posicao:=
      CASE
      WHEN (@total_pontos = IFNULL(pontos_jogo_1, 0) + IFNULL(pontos_jogo_2, 0) + IFNULL(pontos_jogo_3, 0))
      THEN @posicao
      ELSE @posicao + 1
      END
      AS classificacao,
      time_codigo,
      time_nome,
      @total_pontos:= IFNULL(pontos_jogo_1, 0) + IFNULL(pontos_jogo_2, 0) + IFNULL(pontos_jogo_3, 0)
      AS total_pontos
      FROM
      (SELECT @posicao := 0, @total_pontos) classificacao
      CROSS JOIN tb_classificacao
      ORDER BY
      IFNULL(pontos_jogo_1, 0) + IFNULL(pontos_jogo_2, 0) + IFNULL(pontos_jogo_3, 0) DESC,
      time_nome DESC;

      Excluir
    2. Boa tarde,fico feliz por ter ajudado. Para melhor visualização veja o exemplo que eu colei acima no GitHub:

      https://github.com/kellysc2013/dicas_sql_jquery/blob/master/mysql_57_classificacao_rank_soma_colunas_20200823_1217.sql

      Excluir
    3. Caso use o mysql 8 você pode utilizar a window function "dense_rank". Vou colar a seguir, para melhor visuação abra o link: https://github.com/kellysc2013/dicas_sql_jquery/blob/master/mysql_dense_rank_mysql_80_20200823_2053.sql


      /*
      Este exemplo mostra como exibir o rank de dados(classificação) no mysql 8.0
      Utiliza a window function dense_rank
      */

      /*
      *Cria a tabela "tb_classificacao"
      */
      CREATE TABLE tb_classificacao
      (
      time_codigo integer not null,
      time_nome varchar(255) not null,
      pontos_jogo integer not null
      );

      /*
      *Inclusão dos registros na tabela "tb_classificacao"
      */
      INSERT INTO tb_classificacao VALUES
      (2479, 'A', 0),
      (2480, 'B', 1),
      (2482, 'C', 0),
      (2483, 'D', 0),
      (2484, 'E', 1),
      (2485, 'F', 3),
      (2489, 'G', 3),
      (2490, 'H', 0),
      (2491, 'I', 0),
      (2492, 'J', 3),

      (2479, 'A', 3),
      (2480, 'B', 0),
      (2482, 'C', 1),
      (2483, 'D', 3),
      (2484, 'E', 0),
      (2485, 'F', 1),
      (2489, 'G', 0),
      (2490, 'H', 3),
      (2491, 'I', 1),
      (2492, 'J', 3),


      (2479, 'A', 0),
      (2480, 'B', 1),
      (2482, 'C', 0),
      (2483, 'D', 0),
      (2484, 'E', 1),
      (2485, 'F', 3),
      (2489, 'G', 3),
      (2490, 'H', 0),
      (2491, 'I', 0),
      (2492, 'J', 3);


      /*
      *Exibe registros da tabela "tb_classificacao"
      */
      SELECT * FROM tb_classificacao;



      /*
      *Exibe a classificação dos times de acordo com os pontos (rank)
      */
      SELECT
      DENSE_RANK() OVER(ORDER BY tb_classificacao.total_pontos DESC) AS classificacao,
      tb_classificacao.time_codigo,
      tb_classificacao.time_nome,
      tb_classificacao.total_pontos
      FROM
      (
      SELECT
      time_codigo,
      time_nome,
      SUM(pontos_jogo) AS total_pontos
      FROM tb_classificacao
      GROUP BY
      time_codigo, time_nome
      ) tb_classificacao;
      ORDER BY tb_classificacao.time_nome;

      Excluir
  7. Kelly, Muito obrigado. Me ajudou a resolver parte do problema. Me diz uma coisa... Existe a possibilidade de se fazer uma subconsulta dentro da consulta principal?

    Exemplo... O resultado da consulta seria:

    Pos - id_user - pontos
    1 - 23 - 20
    2 - 5 - 18
    3 - 1 - 14


    Eu preciso consultar a posição e a pontuação apenas do id_user 5.

    Existe essa possibilidade?

    ResponderExcluir
    Respostas
    1. Boa noite, segue o exemplo de duas consultas de rank com filtro. Uma para o mysql 5 e outra para o mysql 8. A criação das tabelas (create table) e inserts estão nas respostas anteriores, utilizei uma subquery (uma consulta dentro de outra consulta) como você havia falado).

      Colei o exemplo, mas segue o link no GitHub para uma melhor visualização:

      https://github.com/kellysc2013/dicas_sql_jquery/blob/master/mysql_rank_com_filtro_202008_2000.sql


      /*Rank com filtro no mysql 8*/
      SELECT * FROM
      (
      SELECT
      DENSE_RANK() OVER(ORDER BY tb_classificacao.total_pontos DESC) AS classificacao,
      tb_classificacao.time_codigo,
      tb_classificacao.time_nome,
      tb_classificacao.total_pontos
      FROM
      (
      SELECT
      time_codigo,
      time_nome,
      SUM(pontos_jogo) AS total_pontos
      FROM tb_classificacao
      GROUP BY
      time_codigo, time_nome
      ) tb_classificacao
      ) filtro
      WHERE filtro.classificacao = 5



      /*Rank com filtro no mysql 5*/
      SELECT * FROM (
      SELECT
      @posicao:=
      CASE
      WHEN (@total_pontos = IFNULL(pontos_jogo_1, 0) + IFNULL(pontos_jogo_2, 0) + IFNULL(pontos_jogo_3, 0))
      THEN @posicao
      ELSE @posicao + 1
      END
      AS classificacao,
      time_codigo,
      time_nome,
      @total_pontos:= IFNULL(pontos_jogo_1, 0) + IFNULL(pontos_jogo_2, 0) + IFNULL(pontos_jogo_3, 0)
      AS total_pontos
      FROM
      (SELECT @posicao := 0, @total_pontos) classificacao
      CROSS JOIN tb_classificacao
      ORDER BY
      IFNULL(pontos_jogo_1, 0) + IFNULL(pontos_jogo_2, 0) + IFNULL(pontos_jogo_3, 0) DESC,
      time_nome DESC
      )rn WHERE rn.classificacao = 5;

      Excluir
    2. No exemplo acima eu estou filtrando o usuário que está na 5ª posição mas poderia ser uma outra coluna utilizada no fitro, com por exemplo o código do time.

      Veja os exemplos a seguir para o mysql 8 e 5:

      /*No mysql 8, filtrar o time com o código = 2480*/
      SELECT * FROM
      (
      SELECT
      DENSE_RANK() OVER(ORDER BY tb_classificacao.total_pontos DESC) AS classificacao,
      tb_classificacao.time_codigo,
      tb_classificacao.time_nome,
      tb_classificacao.total_pontos
      FROM
      (
      SELECT
      time_codigo,
      time_nome,
      SUM(pontos_jogo) AS total_pontos
      FROM tb_classificacao
      GROUP BY
      time_codigo, time_nome
      ) tb_classificacao
      ) filtro
      WHERE filtro.time_codigo = 2480;

      /*No mysql 5, filtrar o time com o código = 2480*/
      /*
      *EXEMPLO - Exibir a classificação dos times.
      */
      SELECT * FROM
      (
      SELECT
      @posicao:=
      CASE
      WHEN (@total_pontos = IFNULL(pontos_jogo_1, 0) + IFNULL(pontos_jogo_2, 0) + IFNULL(pontos_jogo_3, 0))
      THEN @posicao
      ELSE @posicao + 1
      END
      AS classificacao,
      time_codigo,
      time_nome,
      @total_pontos:= IFNULL(pontos_jogo_1, 0) + IFNULL(pontos_jogo_2, 0) + IFNULL(pontos_jogo_3, 0)
      AS total_pontos
      FROM
      (SELECT @posicao := 0, @total_pontos) classificacao
      CROSS JOIN tb_classificacao
      ORDER BY
      IFNULL(pontos_jogo_1, 0) + IFNULL(pontos_jogo_2, 0) + IFNULL(pontos_jogo_3, 0) DESC,
      time_nome DESC
      )rn WHERE rn.time_codigo = 2480;

      Excluir
    3. Link do 2º exemplo no GitHub:
      https://github.com/kellysc2013/dicas_sql_jquery/blob/master/mysql_rank_com_filtro_exemplo_2_202008_2000.sql

      Excluir
  8. Kelly muito obrigado pela ajuda. Funcionou direitinho. Salvou meu dia. Rs. Sucesso!

    ResponderExcluir