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.
