how to take an element from a table

Asked

Viewed 319 times

3

Speaking of variables that can be declared within Begin, I saw that it has Declare and set only that I don’t know when to use them. I saw that you have set @x, set x, set x = x+1, set x := x+1; they all confuse me and I don’t know what they’re right for.

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

You’re leaving as follows: product name, code Product, client, qtdComprada;

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 customer.

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)
) ;

I saw it again, but I made a mistake: 

   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

  • DECLARE is when you declare a variable and its type... (at the beginning of its creation), SET is when you want to change its behavior, and "arrow" some value for this declared variable.

  • Redo wrong. Items is a variable, which contains a string, if you do not put the quotes, you will be breaking everything.

  • In the answer I did not do so.

2 answers

1

Usa 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: 16/10/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 don’t have mysql to test, but should bring as a result what you requested.

The defect of the method is that it will bring only 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
...

  • I don’t understand?????

  • I edited the answer.

  • the error that gives this: 'INF.CODE' in 'group statement' if I change inf.code to inf.cod_product or . numero_nf appears but shows all results for a person or is shows all of the people.does not only take the product that Cad person most bought

  • And my intention is not to get solutions, I tried to do, only I’m having doubts and asking for the help of you!!!! I place this code on a single cursor, correct?

  • The idea was to give a functional example of how to use the subquerys, you will have to understand and adjust to your problem.

  • I’m sorry if I misread you, but I didn’t mean to criticize you on your last remark. Good luck with your problem if you need to question.

  • I’m new here on the site, I’ve been reading about questions and answers and the proposal is to answer simply and objectively, so I edited my answer to fit the goal of Stackoverflow.

Show 2 more comments

1

What you got confused is actually the use of the variable in the case set x := x+1 means a simple sum of +1, that is to say, x is getting its current value more 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', means that you are storing a 'Value' to the variable, which will be used at another time or when it is called.

To show only the first line, simply remove the other names in your select, and create a new product name_name, for each item you wish to display, you take only one in the list and create a new cursor, in case I am using the variable @nomeProd to list your query items:

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 ;

  • Let me get this straight. I can for example declare car: DECLARE carro int then I could use which way?: set carro = 3 set @carro = 3 set carro := 3 set @carro := 3 ..... , and these lines give error: set @itens :="produto.nome as nomeProd";

  • DECLARE car int(11); set @carro:=3;

  • I believe it is only to declare the variable items: DECLARE itens VARCHAR(255); You have to do as a string, using quotes, can not do: set @itens :=produto.nome as nomeProd, cliente.nome as cliente, count(produto.codigo) as qtdComprProd;

  • See if this helps you: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/static.htm#BABHEBAI

  • now I understand the use of variables. thank you very much! But when I put the code you made in the error process. what can be?

  • I edited, the cursor name was wrong. see if it’s working.

  • Always publish the error, so we can analyze. We literally cannot predict any behavior associated with the error without an on-screen exception.

  • but should I play this code directly in the Precedent? need to put between BEGIN and end? DROP PROCEDURE IF EXISTS uspProdutoMaisCompradoCliente $$
CREATE PROCEDURE uspProdutoMaisCompradoCliente ()

BEGIN

  • I created it as a new project, for you.

  • From BEGIN variables are marked with error. This time I put it right, but the error is pointed at the first variable after Begin

Show 5 more comments

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.