how to get an element from a table

Question

how to get an element from a table

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

Speaking of variables that can be declared inside begin, I've seen that it has Declare and set only that I do not know when to use them. I have set x, set x, set x = x + 1, set x: = x + 1; they all confuse me and I do not know what they are for.

I have this code that is to get the product code that each customer bought the most. I wanted him to take the first customer of each code and to show it, because I ordered it down.

It's coming out like this: ProductName, ProductID, CustomerID, PurchasedTit;

ABAJUR 15 JOSE TAVARES DE OLUVEIRA 2

LAPISEIRA 1.2 10 JOSE TAVARES DE OLUVEIRA 1

CAIXA DE SOM 16 JOSE TAVARES DE OLUVEIRA 1

CANETA VERMELHA 3 MANOEL JOAQUIM PURTUGA 2

LAPISEIRA 0.9 9 MANOEL JOAQUIM PURTUGA 1

I wanted to show the first line of each client.

DROP PROCEDURE IF EXISTS uspProdutoMaisCompradoCliente $$
CREATE PROCEDURE uspProdutoMaisCompradoCliente ()

BEGIN

DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE vNomeProd VARCHAR(250);
DECLARE vNomeCli VARCHAR(250);
DECLARE vQtdComprProd INT(11);
DECLARE contador int;

DECLARE cursor_a CURSOR FOR (
select produto.nome as nomeProd, cliente.nome as cliente,
count(produto.codigo) as qtdComprProd
from cliente
inner join nota_fiscal
on cliente.codigo = nota_fiscal.cod_cliente
inner join item_nota_fiscal
on item_nota_fiscal.numero_nf = nota_fiscal.numero_nf
inner join produto
on produto.codigo = item_nota_fiscal.cod_produto
group by produto.nome , produto.codigo , cliente.nome , cliente.codigo
order by cliente.nome, qtdComprProd desc
);

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;

DROP TEMPORARY TABLE IF EXISTS tempTabela;
CREATE TEMPORARY TABLE tempTabela (
   tNomeProd VARCHAR(250),
  tNomeCli VARCHAR(250),
   tQtdComprProd INT(11)
);

OPEN cursor_a;

REPEAT
FETCH  cursor_a INTO vNomeProd,vNomeCli, vQtdComprProd;

IF NOT done THEN

     INSERT INTO tempTabela VALUES (vNomeProd, vNomeCli, vQtdComprProd);

END IF;

UNTIL done END REPEAT;
close cursor_a;
SELECT * FROM tempTabela;
END $$
DELIMITER ;

Tables:

CREATE TABLE ESTADO (
  ESTADO VARCHAR(02) NOT NULL,
  NOME VARCHAR(100) NOT NULL,
  PRIMARY KEY (ESTADO)
) ;

CREATE TABLE CIDADE (
  CODIGO INTEGER NOT NULL,
  NOME VARCHAR(250) NOT NULL,
  ESTADO VARCHAR(02),
  PRIMARY KEY (CODIGO),
  FOREIGN KEY (ESTADO) REFERENCES ESTADO (ESTADO)
) ;

CREATE TABLE CLIENTE (
  CODIGO INT NOT NULL AUTO_INCREMENT,
  NOME VARCHAR(250) NOT NULL,
  RUA VARCHAR(250),
  NUMERO VARCHAR(10),
  COD_CIDADE INTEGER,
  PRIMARY KEY (CODIGO),
  FOREIGN KEY (COD_CIDADE) REFERENCES CIDADE (CODIGO)
);

CREATE TABLE FORNECEDOR (
  CODIGO INTEGER NOT NULL,
  NOME VARCHAR(250) NOT NULL,
  RUA VARCHAR(250),
  NUMERO VARCHAR(10),
  COD_CIDADE INTEGER,
  PRIMARY KEY (CODIGO),
  FOREIGN KEY (COD_CIDADE) REFERENCES CIDADE (CODIGO)
);

CREATE TABLE PRODUTO (
  CODIGO INTEGER NOT NULL,
  NOME VARCHAR(250) NOT NULL,
  PCO_ATUAL_CPA DECIMAL(10,2),
  PCO_ATUAL_VDA DECIMAL(10,2),
  QTD_ESTOQUE INTEGER,
  QTD_EST_MIN INTEGER,
  QTD_EST_MAX INTEGER,
  QTD_PTO_CPA INTEGER,
  IDF_ATIVO_SN VARCHAR(01) DEFAULT 'S',
  COD_FORNECEDOR INTEGER,
  PRIMARY KEY (CODIGO),
  FOREIGN KEY (COD_FORNECEDOR) REFERENCES FORNECEDOR (CODIGO)
) ;

CREATE TABLE NOTA_FISCAL (
  NUMERO_NF INTEGER NOT NULL,
  DTA_VENDA DATE NOT NULL,
  COD_CLIENTE INTEGER,
  VALOR_TOTAL DECIMAL(10,2),
  PRIMARY KEY (NUMERO_NF),
  FOREIGN KEY (COD_CLIENTE) REFERENCES CLIENTE (CODIGO)
) ;

CREATE TABLE ITEM_NOTA_FISCAL (
  NUMERO_NF INTEGER NOT NULL,
  COD_PRODUTO INTEGER NOT NULL,
  QTD_VEDIDA INTEGER,
  PCO_RECEBIDO DECIMAL(10,2),
  PRIMARY KEY (NUMERO_NF, COD_PRODUTO),
  FOREIGN KEY (NUMERO_NF) REFERENCES NOTA_FISCAL (NUMERO_NF),
  FOREIGN KEY (COD_PRODUTO) REFERENCES PRODUTO (CODIGO)
) ;

REfiz as I understood it but from error:

   delimiter $$
        DROP PROCEDURE IF EXISTS uspProdutoMaisCompradoCliente $$
        CREATE PROCEDURE uspProdutoMaisCompradoCliente ()

        BEGIN

            DECLARE done BOOLEAN DEFAULT FALSE;
            DECLARE vNomeProd VARCHAR(250);
            DECLARE vNomeCli VARCHAR(250);
            DECLARE vQtdComprProd INT(11);
            DECLARE contador int;

        DECLARE cursor_a CURSOR FOR (
            select produto.nome as nomeProd, cliente.nome as cliente,
            count(produto.codigo) as qtdComprProd
            from cliente
            inner join nota_fiscal
            on cliente.codigo = nota_fiscal.cod_cliente
            inner join item_nota_fiscal
            on item_nota_fiscal.numero_nf = nota_fiscal.numero_nf
            inner join produto
            on produto.codigo = item_nota_fiscal.cod_produto
            group by produto.nome , produto.codigo , cliente.nome , cliente.codigo
            order by cliente.nome, qtdComprProd desc
        );

        set @itens :=produto.nome as nomeProd, cliente.nome as cliente, count(produto.codigo) as qtdComprProd;


        DECLARE cursor_b CURSOR FOR (
              select @itens from cliente
              inner join nota_fiscal
              on cliente.codigo = nota_fiscal.cod_cliente
              inner join item_nota_fiscal
              on item_nota_fiscal.numero_nf = nota_fiscal.numero_nf
              inner join produto
              on produto.codigo = item_nota_fiscal.cod_produto
              group by produto.nome , produto.codigo , cliente.nome , cliente.codigo
              order by cliente.nome, qtdComprProd desc
        );

        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;

        DROP TEMPORARY TABLE IF EXISTS tempTabela;
        CREATE TEMPORARY TABLE tempTabela (
           tNomeProd VARCHAR(250),
          tNomeCli VARCHAR(250),
           tQtdComprProd INT(11)
        );

        OPEN cursor_a;
        OPEN cursor_b;

        REPEAT
        FETCH  cursor_a INTO vNomeProd,vNomeCli, vQtdComprProd;

        IF NOT done THEN

             INSERT INTO tempTabela VALUES (vNomeProd, vNomeCli, vQtdComprProd);

        END IF;

        UNTIL done END REPEAT;
        close cursor_a;
        SELECT * FROM tempTabela;
        END $$
        DELIMITER ;

mysql sql

asked by anonymous 12.10.2015 / 21:28

2 answers

ggplot data.frame Wordpress image cropping and deletion of the original image

score 1 · Answer 1

What you really got confused about is the use of the variable, in the case set x := x+1 means a simple sum of +1 , that is, x is getting your current value plus 1 .

DECLARE is used to create a variable and define its type (string, number, boolean, etc.).

It is not mandatory to declare variables, but it is good practice.

SET is used to change the behavior of a variable, each time you make this change using: set @variavel:='Valor' , it means you are storing a 'Value' variable, will be used at another time or when it is called.

To show only the first line, just remove the other names in your select, and create a new product_name_name , for each item you want to display, you only get one in the list and create a new cursor , in case I am using the @nameProd variable to list the items of your query:

delimiter $$
    DROP PROCEDURE IF EXISTS uspProdutoMaisCompradoClienteConsulta $$
    CREATE PROCEDURE uspProdutoMaisCompradoClienteConsulta ()

    BEGIN
      DECLARE @vNomeProd VARCHAR(250);
      DECLARE @vNomeCli VARCHAR(250);
      DECLARE @vQtdComprProd INT(11);

        /* produto */
        DECLARE produto_nome_cursor CURSOR FOR (
            select produto.nome as nomeProd
                  from cliente
                  inner join nota_fiscal
                  on cliente.codigo = nota_fiscal.cod_cliente
                  inner join item_nota_fiscal
                  on item_nota_fiscal.numero_nf = nota_fiscal.numero_nf
                  inner join produto
                  on produto.codigo = item_nota_fiscal.cod_produto
                  group by produto.nome, produto.codigo, cliente.nome, cliente.codigo
        ); 

        /* abre e faz a listagem de produtos */
        OPEN produto_nome_cursor;
        FETCH NEXT FROM produto_nome_cursor INTO @vNomeProd;
        WHILE @@FETCH_STATUS = 0  
        BEGIN  
           FETCH NEXT FROM produto_nome_cursor INTO @vNomeProd;
        END;
        CLOSE produto_nome_cursor;
        DEALLOCATE produto_nome_cursor;

        /* cliente */ 
        DECLARE cliente_cursor CURSOR FOR (
            select cliente.nome as cliente
                   from cliente
                   inner join nota_fiscal
                   on cliente.codigo = nota_fiscal.cod_cliente
                   inner join item_nota_fiscal
                   on item_nota_fiscal.numero_nf = nota_fiscal.numero_nf
                   inner join produto
                   on produto.codigo = item_nota_fiscal.cod_produto
                   group by produto.nome, produto.codigo, cliente.nome, cliente.codigo
            ); 

        /* abre e faz a listagem de clientes */
        OPEN cliente_cursor;
        FETCH NEXT FROM cliente_cursor INTO @vNomeCli;
        WHILE @@FETCH_STATUS = 0  
        BEGIN  
           FETCH NEXT FROM cliente_cursor INTO @vNomeCli;
        END;
        CLOSE cliente_cursor;
        DEALLOCATE cliente_cursor;

        /* quantidade de compras */ 
        DECLARE qtde_cursor CURSOR FOR (
            select count(produto.codigo) as qtdComprProd
                   from cliente
                   inner join nota_fiscal
                   on cliente.codigo = nota_fiscal.cod_cliente
                   inner join item_nota_fiscal
                   on item_nota_fiscal.numero_nf = nota_fiscal.numero_nf
                   inner join produto
                   on produto.codigo = item_nota_fiscal.cod_produto
                   group by produto.nome, produto.codigo, cliente.nome, cliente.codigo
            ); 

        /* abre e faz a listagem de qtdes */
        OPEN qtde_cursor;
        FETCH NEXT FROM qtde_cursor INTO @vNomeCli;
        WHILE @@FETCH_STATUS = 0  
        BEGIN  
           FETCH NEXT FROM qtde_cursor INTO @vNomeCli;
        END;
        CLOSE qtde_cursor;
        DEALLOCATE qtde_cursor;

         /* todos os dados do cliente */ 
        DECLARE dados_cliente_cursor CURSOR FOR (
            select produto.nome as nomeProd,
                   cliente.nome as cliente,
                   count(produto.codigo) as qtdComprProd
                   from cliente
                   inner join nota_fiscal
                   on cliente.codigo = nota_fiscal.cod_cliente
                   inner join item_nota_fiscal
                   on item_nota_fiscal.numero_nf = nota_fiscal.numero_nf
                   inner join produto
                   on produto.codigo = item_nota_fiscal.cod_produto
                   group by produto.nome, produto.codigo, cliente.nome, cliente.codigo
            ); 

        /* abre e faz a listagem de qtdes */
        OPEN dados_cliente_cursor;
        FETCH NEXT FROM dados_cliente_cursor INTO @vNomeCli;
        WHILE @@FETCH_STATUS = 0  
        BEGIN  
           FETCH NEXT FROM dados_cliente_cursor INTO @vNomeCli;
        END;
        CLOSE dados_cliente_cursor;
        DEALLOCATE dados_cliente_cursor;
    END $$
DELIMITER ;

score 1 · Answer 2

Use subquerys,

SELECT nomeProduto,
        codigoProd,
        (subquery que busca o cliente que mais comprou) AS cliente,        
        (subquery que busca a quantidade vendida) AS qtdComprada;
FROM PRODUTO ...

The subquery is another select with SELECT FROM WHERE, but the validation is done by the external tables

SELECT tabela1.codigo
        (SELECT tabela2.nome FROM tabela2 WHERE tabela2.cliente = tabela1.cliente) as nome_cliente
FROM tabela1

Edited: 10/16/2015

SELECT CLI.NOME,
        (SELECT PRO.NOME
          FROM PRODUTO PRO
            INNER JOIN ITEM_NOTA_FISCAL INF ON PRO.CODIGO = INF.COD_PRODUTO
            INNER JOIN NOTA_FISCAL NOF ON INF.NUMERO_NF = NOF.NUMERO_NF
          WHERE NOF.COD_CLIENTE = CLI.CODIGO
          GROUP BY PRO.CODIGO, PRO.NOME
          ORDER BY COUNT(PRO.CODIGO) desc
          LIMIT 1) AS NOME,
        (SELECT PRO.CODIGO
          FROM PRODUTO PRO
            INNER JOIN ITEM_NOTA_FISCAL INF ON PRO.CODIGO = INF.COD_PRODUTO
            INNER JOIN NOTA_FISCAL NOF ON INF.NUMERO_NF = NOF.NUMERO_NF
          WHERE NOF.COD_CLIENTE = CLI.CODIGO
          GROUP BY PRO.CODIGO
          ORDER BY COUNT(PRO.CODIGO) desc
          LIMIT 1) AS CODIGO,
        (SELECT COUNT(PRO.CODIGO)
          FROM PRODUTO PRO
            INNER JOIN ITEM_NOTA_FISCAL INF ON PRO.CODIGO = INF.COD_PRODUTO
            INNER JOIN NOTA_FISCAL NOF ON INF.NUMERO_NF = NOF.NUMERO_NF
          WHERE NOF.COD_CLIENTE = CLI.CODIGO
          GROUP BY PRO.CODIGO
          ORDER BY COUNT(PRO.CODIGO) desc
          LIMIT 1) AS QUANT
FROM CLIENTE CLI

Look at the example above, I do not have mysql to test, but should bring the result you requested.

The default of the method is that it will only bring one line per product, so if you have two customers with the same number of purchases, it will only come in the result, this can be solved with a subquery after FROM ...

SELECT pro.nome
        pro.codigo
        aux.nome
        aux.quant
 FROM PRODUTO pro
   INNER JOIN (select pro_codigo, nome, count(alguma coisa) as quant from relações de tabelas) as aux ON aux.pro_codigo = pro.codigo
...