MySQL - Utilizando variáveis do MySQL com subqueries

Um assunto que acredito ser pouco discutido, ou que pelo menos eu vejo muito pouco, são as variáveis em SQL. Estas funcionam de maneira semelhante a variáveis das linguagens de programação, ou seja guardam um determinado valor, apenas a implementação delas é ligeiramente diferente.

Uma utilização interessante das variáveis é o incremento de linha, ou seja, criar uma lista ordenada numerada 'artificialmente'. Talvez eu tenha sido pouco claro na explicação acima, mas os exemplos dirão por si próprios.

Para começarmos vamos adotar a nossa tabela de teste

id nome idade estado
1 Pedro 50 SP
2 Antonio 30 RJ
3 Amélia 20 SP
4 Marta 15 SP
5 Maria 80 SP

Vamos ordenar a tabela por nome, por exemplo.

SELECT * FROM teste ORDER BY nome

Temos a tabela ordenada da seguinte forma

id nome idade estado
3 Amélia 20 SP
2 Antonio 30 RJ
5 Maria 80 SP
4 Marta 15 SP
1 Pedro 50 SP

O problema, se é que podemos dizer isto, é que o campo id fica desordenado, exatamente porque este campo é um AUTO INCREMENT. Mas se quisermos uma numeração em sequência teremos de utilizar as variáveis do SQL.

A primeira maneira de a utilizarmos é através do comando SET:

SET @var := 0;
SELECT *,@var := @var + 1 AS sequencia FROM teste t1 ORDER BY nome;

Temos como resultado:

id nome idade estado sequencia
3 Amélia 20 SP 1
2 Antonio 30 RJ 2
5 Maria 80 SP 3
4 Marta 15 SP 4
1 Pedro 50 SP 5

Acredito que fica bem claro como a variável @var funciona aqui mas vou explicar rapidamente.

Para atribuir um valor a uma variável devemos utilizar a sintaxe:
@var := valor_a_ser_atribuido
Para declarar a variável devemos utilizar o comando SET.

SET @var := valor_a_ser_atribuido;

@var := @var + 1 incrementa o valor numérico a cada registro retornado pelo select. É por isto que conseguimos uma sequência conforme a ordenação, não importando o campo id.

Este método é mais adequando para functions e procedures. Para um select unico e direto podemos utilizar o comando abaixo:

SELECT *,@var := @var + 1 AS sequencia FROM teste, (SELECT @var := 0) t1 ORDER BY nome

id nome idade estado @var := 0 sequencia
3 Amélia 20 SP 0 1
2 Antonio 30 RJ 0 2
5 Maria 80 SP 0 3
4 Marta 15 SP 0 4
1 Pedro 50 SP 0 5

Vamos entender um pouco como functiona o subselect. (SELECT @var := 0) t1 é a primeira query efetuada. A partir dela a segunda query é efetuada como se a primeira fossem uma tabela, no caso a tabela t1.

Para melhorarmos o retorno vamos exibir todos os resultados menos os campos @var := 0 e id:

SELECT nome,idade,estado,@var := @var + 1 AS sequencia FROM teste, (SELECT @var := 0) t1 ORDER BY nome

nome idade estado sequencia
Amélia 20 SP 1
Antonio 30 RJ 2
Maria 80 SP 3
Marta 15 SP 4
Pedro 50 SP 5

Podemos utilizar as variáveis de outra forma, por exemplo, listando todos os registros da maior idade da tabela.

SELECT * FROM teste, (SELECT @var := MAX(idade) FROM teste) t1 WHERE idade = @var

id nome idade estado @var := MAX(idade)
5 Maria 80 SP 80

E melhorando a query:

SELECT id,nome,idade, estado FROM teste, (SELECT @var := MAX(idade) FROM teste) t1 WHERE idade = @var

id nome idade estado
5 Maria 80 SP

Podemos ver por estes exemplos como as variáveis SQL são uteis, nos permitindo resolver diretamente no banco de dados problemas que de outra forma terianmos de resolver no código do programa.

Abraços