MySQL ROW_NUMBER, é assim que você o emula

Resumo : neste tutorial você aprenderá como emular a row_number()função no MySQL. Mostraremos como adicionar um número inteiro sequencial a cada linha ou grupo de linhas no conjunto de resultados.

Observe que o MySQL suporta ROW_NUMBER()desde a versão 8.0. Se você usa MySQL 8.0 ou posterior, consulte o ROW_NUMBER()tutorial da função. Caso contrário, você pode continuar com o tutorial para aprender como emular a ROW_NUMBER()função.

Introdução à função ROW_NUMBER()

É  ROW_NUMBER()uma função de janela que retorna um número sequencial para cada linha, começando em 1 para a primeira linha.

Antes da versão 8.0, o MySQL não suportava ROW_NUMBER()funções como Microsoft SQL Server , Oracle ou PostgreSQL . Felizmente, o MySQL fornece variáveis ​​de sessão que você pode usar para emular a  ROW_NUMBER()função.

MySQL ROW_NUMBER – adicionando um número de linha para cada linha

Para emular a  ROW_NUMBER()função, você deve usar variáveis ​​de sessão na consulta.

As instruções a seguir retornam cinco funcionários da  employeestabela e adicionam um número de linha a cada linha, começando em 1.

SET @row_number = 0; 

SELECT 
    (@row_number:=@row_number + 1) AS num, 
    firstName, 
    lastName
FROM
    employees
ORDER BY firstName, lastName    
LIMIT 5;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo:

• Primeiro, defina uma variável nomeada  @row_numbere defina seu valor como 0. @row_numberÉ uma variável de sessão indicada pelo @prefixo.
• Em seguida, selecione os dados da tabela employeese aumente o valor da  @row_numbervariável em um para cada linha. Usamos a LIMITcláusula para restringir um número de linhas retornadas a cinco.

Outra técnica é usar uma variável de sessão como uma tabela derivada e juntá -la com a tabela principal. Veja a seguinte consulta:

SELECT 
    (@row_number:=@row_number + 1) AS num, 
    firstName, 
    lastName
FROM
    employees,
    (SELECT @row_number:=0) AS t
ORDER BY 
    firstName, 
    lastName    
LIMIT 5;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Observe que a tabela derivada deve ter seu próprio alias para tornar a consulta sintaticamente correta.

MySQL ROW_NUMBER – adicionando um número de linha a cada grupo

E quanto à  ROW_NUMBER() OVER(PARITION BY ... )funcionalidade? Por exemplo, e se você quiser adicionar um número de linha a cada grupo e ele for redefinido para cada novo grupo?

Vamos dar uma olhada na paymentstabela do banco de dados de exemplo :

SELECT
    customerNumber, 
    paymentDate, 
    amount
FROM
    payments
ORDER BY 
   customerNumber;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Suponha que para cada cliente você queira adicionar um número de linha e que o número da linha seja redefinido sempre que o número do cliente for alterado.

Para conseguir isso, você deve usar duas variáveis ​​de sessão, uma para o número da linha e outra para armazenar o número do cliente antigo para compará-lo com o atual como a seguinte consulta:

SET @row_number := 0;

SELECT 
    @row_number:=CASE
        WHEN @customer_no = customerNumber 
			THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=customerNumber customerNumber,
    paymentDate,
    amount
FROM
    payments
ORDER BY customerNumber;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo, usamos a CASEexpressão na consulta. Se o número do cliente permanecer o mesmo, aumentamos a  @row_numbervariável, caso contrário, zeramos para um.

Esta consulta usa uma tabela derivada e a junção cruzada para produzir o mesmo resultado.

SELECT 
    @row_number:=CASE
        WHEN @customer_no = customerNumber 
          THEN 
              @row_number + 1
          ELSE 
               1
        END AS num,
    @customer_no:=customerNumber CustomerNumber,
    paymentDate,
    amount
FROM
    payments,
    (SELECT @customer_no:=0,@row_number:=0) as t
ORDER BY 
    customerNumber;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste tutorial, você aprendeu duas maneiras de emular a row_numberfunção de janela no MySQL.