MySQL Rollup

O banco de dados MySQL contém muitas tabelas relacionadas para armazenar dados. Às vezes, é necessário obter o resumo dos dados usando uma ou mais tabelas. O recurso MySQL Rollup é usado para gerar esse tipo de dados que podem conter o total ou subtotal de um ou mais campos da tabela. O subtotal dos dados pode ser calculado facilmente usando uma instrução simples de rollup sem usar a união na consulta. Além disso, linhas totais e subtotal podem ser adicionadas ao conteúdo da tabela usando esta instrução. Diferentes usos de Rollup na consulta foram descritos neste tutorial.

Sintaxe:

O Rollup é usado para criar um grupo de colunas. Portanto, qualquer função agregada de MySQL e Grupo por cláusula é usada na consulta selecionada onde o rollup é usado. A sintaxe do rollup é mostrada abaixo. Os nomes de campo da tabela que serão usados ​​após a cláusula selecionada serão usados ​​com o grupo por cláusula para agrupamento.

Selecione Field1, Field2 AgregateFunction (Field3)
Do tableName
Grupo por Field1, arquivado2 com Rollup;

Usos da declaração de rollup em consulta selecionada:

Diferentes usos da declaração de rollup com o grupo por cláusula na consulta selecionada foram mostrados neste tutorial.

Pré-requisitos:

Você precisa criar uma tabela de banco de dados com dados em um banco de dados MySQL para verificar o uso da declaração de rollup com o grupo por cláusula em MySQL. Abra o terminal e conecte -se ao servidor MySQL executando o seguinte comando.

$ sudo mysql -u root

Execute o seguinte comando para criar um banco de dados nomeado test_db.

Criar banco de dados test_db;

Execute o seguinte comando para selecionar o banco de dados.

Use test_db;

Execute a seguinte consulta para criar uma tabela chamada Sales_persons com quatro campos.

Crie tabela sales_persons (
ID int Auto_increment Chave primária,
Nome Varchar (30) não nulo,
Email Varchar (50),
contact_no varchar (30));

Execute a seguinte consulta para inserir três registros no Sales_person mesa.

Inserir em 'sales_persons' ('id', 'nome', 'email', 'contact_no') valores (null, 'kamal hasan', '[email protected] ',' 0191275634 '),
(Null, 'nila hossain', '[email protected] ',' 01855342357 '),
(Null, 'Abir Hossain', '[email protected] ',' 01634235698 ');

Execute a seguinte consulta para criar uma tabela chamada vendas com quatro campos que contém uma chave estrangeira que cria um relacionamento um para muitos Sales_persons Tabela para o vendas mesa.

Crie vendas de tabela (
Id int não é uma chave primária nula,
Sales_date Data não nula, valor int,
sp_id int,
Restrição fk_sp chave estrangeira (sp_id)
Referências Sales_persons (ID)
Em excluir cascata na atualização em cascata);

Execute a seguinte consulta para inserir quatro registros no vendas mesa.

Inserir em 'vendas' ('id', 'sales_date', 'valor', 'sp_id') valores
(90, '2021-11-09', 800000, 1),
(34, '2020-12-15', 563400, 3),
(67, '2021-12-23', 900000, 1),
(56, '2020-12-31', 6700000, 1);

Exemplo 1: Crie relatório de resumo sem usar a instrução ROLLP

A consulta selecionada a seguir calculará o relatório resumido do valor total das vendas de cada vendedor e o valor geral das vendas de todas as vendas usando a Union todos com duas consultas selecionadas. Uma consulta selecionada recuperará o nome do vendedor e as vendas totais desse vendedor. Outra consulta calculará as vendas de todos os vendedores. Execute a seguinte consulta do prompt MySQL.

Selecione o nome, soma (quantidade) como total
De vendas, Sales_persons
Onde vendas.sp_id = sales_persons.eu ia
Grupo por sp_id
Union All
Selecione NULL, SUM (VENDAS.valor) como total
De vendas;

Saída:

A saída a seguir aparecerá após a execução da consulta acima. Há um registro para o vendedor chamado 'Abir Hossain' com o valor de identificação 3 no vendas Tabela e o valor é 563400. Existem três registros para o vendedor chamado 'Kamal Hasan' com o valor de identificação, 1 na tabela de vendas, e o valor total das vendas é 8400000. A soma de todo o valor das vendas é 563400+8400000 = 8963400.

Saída:

A saída a seguir aparecerá após a execução da consulta acima. O campo de nome mostrará o valor nulo da linha que contém o valor total.

Exemplo-2: Uso de Rollup com uma função de campo e soma ()

A saída gerada no exemplo anterior pode ser gerada facilmente usando a declaração de rollup que foi mostrada neste exemplo. A consulta selecionada que foi usada para contar o valor total das vendas de todos os vendedores no exemplo anterior não é necessário na consulta a seguir por causa do uso da instrução ROLLP. O campo de nome do vendas A tabela tem sido usada com a função agregada na consulta selecionada. O grupo por cláusula contém o campo de nome com a declaração de rollup.

Selecione o nome, soma (quantidade) como total
De vendas, Sales_persons
Onde vendas.sp_id = sales_persons.eu ia
Grupo por nome com rollup;

Saída:

A saída a seguir aparecerá após a execução da consulta acima. Como o exemplo anterior, o campo de nome mostrará o valor nulo da linha que contém o valor total.

Exemplo 3: Uso de Rollup With Year () Função ao lado da função SUM ()

O uso de qualquer função interna do MySQL com a função agregada para usar a instrução ROLLP foi mostrada neste exemplo. A consulta selecionada a seguir mostrará o relatório de resumo do valor das vendas com base no ano de vendas. A função do ano () foi usada na consulta para descobrir o valor do ano a partir da data de vendas. De acordo com os dados inseridos da tabela de vendas, existem duas entradas para 2020 e duas entradas para o ano 2021.

Selecione Ano (Sales_date), Sum (quantia) como total
De vendas
Grupo por ano (Sales_date) com Rollup;

Saída:

A saída a seguir aparecerá após a execução da consulta acima. O valor total das vendas para o ano 2020 é 563400+6700000 ou 7263400. O valor total das vendas para o ano 2021 é 800000+900000 ou 1700000. O total de 7263400+1700000 é 8963400.

Conclusão:

Muitas consultas complexas podem ser facilmente implementadas usando a instrução ROLLP. Os usos desta declaração foram mostrados usando várias tabelas neste tutorial para ajudar os novos usuários do MySQL.