MERCADO - Código do Artigo com o prof° Johnes, Rafael e Alan;

bd.sql

CREATE DATABASE mercado;
USE mercado;

CREATE TABLE produtos (
    idproduto INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
    nome VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE vendas (
    idvenda INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
    data_venda DATE NOT NULL
) ENGINE=InnoDB;

CREATE TABLE venda_produtos (
    idvenda_produto INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
    idvenda INT(11) NOT NULL,
    idproduto INT(11) NOT NULL,
    quantidade INT(11),
    valor_unitario DOUBLE PRECISION(10, 2),
    CONSTRAINT fk_venda_produtos_produto FOREIGN KEY (idproduto)
        REFERENCES produtos (idproduto),
    CONSTRAINT fk_venda_produtos_venda FOREIGN KEY (idvenda)
        REFERENCES vendas (idvenda)
) ENGINE=InnoDB;

INSERT INTO produtos (nome) VALUES ('Leite');
INSERT INTO produtos (nome) VALUES ('Café');
INSERT INTO produtos (nome) VALUES ('Cerveja');
INSERT INTO produtos (nome) VALUES ('Pão');
INSERT INTO produtos (nome) VALUES ('Manteiga');
INSERT INTO produtos (nome) VALUES ('Arroz');
INSERT INTO produtos (nome) VALUES ('Feijão');

INSERT INTO vendas (data_venda) VALUES ('2015-04-03');
INSERT INTO vendas (data_venda) VALUES ('2015-04-09');
INSERT INTO vendas (data_venda) VALUES ('2015-04-15');
INSERT INTO vendas (data_venda) VALUES ('2015-04-18');
INSERT INTO vendas (data_venda) VALUES ('2015-04-18');
INSERT INTO vendas (data_venda) VALUES ('2015-04-20');
INSERT INTO vendas (data_venda) VALUES ('2015-04-20');
INSERT INTO vendas (data_venda) VALUES ('2015-04-22');
INSERT INTO vendas (data_venda) VALUES ('2015-04-23');
INSERT INTO vendas (data_venda) VALUES ('2015-04-27');
 
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (1, 2, 1, 5.5);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (1, 4, 2, 5.5);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (1, 5, 1, 3);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (2, 1, 1, 1.7);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (2, 3, 1, 4);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (2, 4, 3, 7);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (2, 5, 1, 1);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (3, 2, 1, 5);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (3, 4, 5, 3);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (3, 5, 4, 10);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (4, 1, 7, 1);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (4, 2, 10, 6);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (4, 4, 1, 7);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (4, 5, 1, 8);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (5, 3, 8, 9);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (6, 5, 1, 15);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (7, 4, 4, 10);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (8, 4, 7, 15);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (9, 4, 1, 8);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (9, 4, 3, 10);
INSERT INTO venda_produtos (idvenda, idproduto, quantidade, valor_unitario) VALUES (10, 4, 8, 5);

query.sql

SET @sql = NULL;
SELECT 
    GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(p.idproduto = ', idproduto, ', "Sim", "Não")) AS ', nome))
INTO @sql FROM produtos;

SET @sql = CONCAT('SELECT ',
			@sql,
		 ' FROM
		   	venda_produtos vp
				JOIN
			produtos p ON p.idproduto = vp.idproduto
		  GROUP BY vp.idvenda
		  ORDER BY vp.idvenda;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Setando variável chamada @sql como NULL; Ela será responsável por armazenar o código gerado dinâmicamente pela seleção 
-- dos produtos mais abaixo.
SET @sql = NULL;
SELECT
    /* 
    Para que possamos transformar as linhas (cada produto) em colunas. Necessitamos percorrer todos os dados da tabela de produtos e adequá-los 
    ao nosso cenário em questão, que é mostrar se X produto foi vendido em cada venda ou não.
    O GROUP_CONCAT é uma função do MySQL padrão, que realiza concatenção de valores em um único registro ou em uma linha por assim dizer.all
    Neste momento precisamos concatenar uma espécie de código SQL que irá verificar se o id do produto atual dentro do loop é igual ao repassado.
    Caso a resposta seja verdadeira o resultado aparente será Sim, ou Não. 
    */
    GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(p.idproduto = ', idproduto, ', "Sim", "Não")) AS ', nome))    
    -- O resultado do SELECT será armazenado na variável anteriormente declarada.
INTO @sql FROM produtos;

/*
Assim como antes, iremos armazenar o código a ser executado em uma variável. 
Podemos usar a mesma, pois em tempo de execução o valor anteriormente gerado não será sobreescrito,
pois o interpretador irá executar o código após o sinal de igual antes.

Abaixo então, o id da venda mais todo o conjunto de IF's está sendo embutido dentro da declaração das colunas (produtos) 
que serão exibidas no resultado. O conjunto de sentenças que segue, são apenas complementos onde traremos os dados da tabela
de produtos relacionados com a tabela de venda de produtos. Após isto, o agrupamento por venda e ordenado por venda mais antiga.
*/
SET @sql = CONCAT('SELECT  
            vp.idvenda, ',
            @sql,
         ' FROM
            venda_produtos vp
                JOIN
            produtos p ON p.idproduto = vp.idproduto
          GROUP BY vp.idvenda
          ORDER BY vp.idvenda;');

-- A linha do PREPARE "prepara" o código que construímos acima, que não difere em nada de sentenças normais apenas variam no dinamismo dos parâmetros.
PREPARE stmt FROM @sql;
-- Executa o Prepared Statement criado anteriormente.
EXECUTE stmt;
-- Tira o statement alocado da memória do SGDB.
DEALLOCATE PREPARE stmt;