Ajuda com SQL, sou iniciante....

[rcamu 3]

amigos sou inciante e montei uma consulta sql para soltar um relatório em excel mas ela está muito lenta meu banco atualmente tem 9000 linhas e para executar essa consulta demora em media de 15 minutos.

 

Alguém poderia me ajudar nesse sql?

 

SELECT cat, id, nome, varejo, atacado, (

SELECT IFNULL( SUM( total ) , 0 )

FROM lc_movimento a

WHERE tipo =1

AND a.cat = b.cat

)tipo_1, (

SELECT IFNULL( SUM( qtd ) , 0 )

FROM lc_movimento a

WHERE tipo =1

AND a.cat = b.cat

)tipo1, (

SELECT IFNULL( SUM( total ) , 0 )

FROM lc_movimento a

WHERE tipo =2

AND a.cat = b.cat

)tipo_2, (

SELECT IFNULL( SUM( qtd ) , 0 )

FROM lc_movimento a

WHERE tipo =2

AND a.cat = b.cat

)tipo2, (

SELECT IFNULL( SUM( total ) , 0 )

FROM lc_movimento a

WHERE tipo =0

AND a.cat = b.cat

)tipo_0, (

SELECT IFNULL( SUM( qtd ) , 0 )

FROM lc_movimento a

WHERE tipo =0

AND a.cat = b.cat

)tipo0, (

SELECT IFNULL( SUM( total ) , 0 )

FROM lc_movimento a

WHERE tipo =0

AND a.cat = b.cat

)vendas, (

SELECT IFNULL( SUM( total ) , 0 )

FROM lc_movimento a

WHERE tipo =1

AND a.cat = b.cat

)compras, (

SELECT IFNULL ( SUM( tipo_1 / tipo1 ) , 0 )

)precomedio, (

SELECT Concat('R$ ',

Replace

(Replace

(Replace

(format( precomedio,2 ), '.', '|'), ',', '.'), '|', ','))

)precomedio2, (

SELECT Concat('R$ ',

Replace

(Replace

(Replace

(format( varejo,2 ), '.', '|'), ',', '.'), '|', ','))

)varejo, (

SELECT Concat('R$ ',

Replace

(Replace

(Replace

(format( atacado,2 ), '.', '|'), ',', '.'), '|', ','))

)atacado, (

SELECT Concat('R$ ',

Replace

(Replace

(Replace

(format( compras,2 ), '.', '|'), ',', '.'), '|', ','))

)compras, (

SELECT Concat('R$ ',

Replace

(Replace

(Replace

(format( vendas,2 ), '.', '|'), ',', '.'), '|', ','))

)vendas, (

SELECT SUM( tipo_0 - tipo_2 - tipo_1 )

)TOTAL, (

SELECT Concat('R$ ',

Replace

(Replace

(Replace

(format( TOTAL,2 ), '.', '|'), ',', '.'), '|', ','))

)TOTAL, (

SELECT IFNULL ( SUM( precomedio / 0.51 ) , 0 )

)minimo, (

SELECT Concat('R$ ',

Replace

(Replace

(Replace

(format( minimo,2 ), '.', '|'), ',', '.'), '|', ','))

)minimo, (

SELECT SUM( tipo1 - tipo2 - tipo0 )

)saldo, (

SELECT IFNULL ( SUM( precomedio * saldo ) , 0 )

)estoque, (

SELECT Concat('R$ ',

Replace

(Replace

(Replace

(format( estoque,2 ), '.', '|'), ',', '.'), '|', ','))

)estoque2

FROM (

SELECT DISTINCT x.cat, y.id, y.nome, y.varejo, y.atacado

FROM lc_movimento x, lc_cat y

WHERE y.id = x.cat

GROUP BY y.nome ASC

)b

[Marcos Xavier 189]

Díficil até de entender essa query hehe. Explique o que quer fazer que será melhor.

Descreva detalhadamente nome das tabelas,campos etc.

[rcamu 3]

kkkkkkk...ela relamente ta maluquinha, com ela desse jeito eu consegui extrair o excel mas o problema é a performance que está muito lenta e as vezes até trava o banco....

Vou tentar detalhar um pouco as tabelas e o que é feito com os dados.

Tenho duas tabelas conforme abaixo:

lc_cat - Onde eu cadastro os itens em varios campos, mas nesse sql vou usar apenas os campos id e nome.

lc_movimento - Nessa tabela fica registrada toda a movimentação do item onde o tipo=0 é entrada o tipo=1 é a saída e o tipo=2 é baixa de item com defeito.

Nessa tabela vou usar os campos tipo, id (com o id eu recupero o nome do produto na tabela lc_cat), total, quantidade.

Com esses dados eu monto o relatorio por produto onde ele me tras os totais conforme abaixo:

Quantidade comprada, quantidade vendida, quantidade com defeito, valor total de compras, valor total de vendas.

Com essas informações eu calculo o saldo de estoque onde pego a quantidade de comprada e subtraio a quantidade vendida e a quantidade com defeito, calculo o preço médio onde pego a quantidade comprada e divido pelo valor total de compra, calculo o preço minimo de venda pelo preço medio calculado (preço médio /0.51) e como ele tras tudo com a formatação do banco ou pego os valores e uso a função concat + replace

Acho que é isso...kkkk

[Motta 645]

Veja se ajuda

[rcamu 3]

Motta...li o topico e fui atras de como fazer....e não consegui...pode ajudar?

[lokaodomau 61]

Cara, ainda não consegui entender...

 

Por que faz SELECT dentro de outro SELECT? Digo isso porque são SELECT na mesma tabela.

[Motta 645]

Primeiro não sei se entendi bem seu sql , mas ele tem n subselects na mesma tabela, e , em tese , poderia ser trocado por um sql apenas com um CASE ou IIF tratado as condições.

[lokaodomau 61]

É exatamente o que o Motta citou, só é preciso entender melhor qual seria o objetivo, em qual tabela cada informação está guardada, qual relacionamento (quando houver), e por aí vai...

[Motta 645]

Faz um "chinês" , com um exemplo básico , como estão os dados , como vc quer que saia.

[rcamu 3]

primeiramente obrigado pela ajudas...

 

mas fazer por parte acho que vai ser mais facil.

 

Tenho duas tabelas conforme abaixo:

lc_cat - Onde eu cadastro os itens.

lc_movimento - Nessa tabela fica registrada toda a movimentação do item onde o tipo=0 é entrada o tipo=1 é a saída e o tipo=2 é baixa de item com defeito.

Nessa tabela eu registro toda movimentação do item, qtd, valor de venda, etc

eu preciso puxar da tabela lc_movimento os totais do tipo=1 do tipo=2 e do tipo=0

exemplo:

Produto Entrada (tipo=0) Saída (tipo=1) Defeito (tipo=0) Compras R$ (tipo=0) Vendas R$ (tipo=1)

ACO-001 COMPRESSOR DE AR 16 1 0 16,00 1,00

 

Eu consegui fazendo com vários select o que deixa a consulta lenta mas não consigo fazer trazer esses campos fazendo apenas um select

 

segue apenas um select apenas do tipo=1 referente ao campo total, preciso que esse mesmo select traga os tipo 1, tipo 2 e tipo 0 a soma do campo total e a soma do campo qtd

 

SELECT cat, id, nome, (

SELECT IFNULL( SUM( total ) , 0 )

FROM lc_movimento a

WHERE tipo =1

AND a.cat = b.cat

)tipo_1

FROM (

SELECT DISTINCT x.cat, y.id, y.nome

FROM lc_movimento x, lc_cat y

WHERE y.id = x.cat

GROUP BY y.nome ASC

)b

 

cat id nome tipo_1

533 533 ACO-001 COMPRESSOR DE AR 999.20

[lokaodomau 61]

Vamos pensar que quer agrupar por item, exibindo a soma dos valores para cada tipo de ação.

 

Quer dizer que precisa de um GROUP BY e suas funções agregadas, desta forma agrupa-se por item e realiza a soma. O problema maior pra um iniciante, pode ser o condicionamento das somas. Mas pode ser feito com funções de controle de fluxo.

 

A SQL pode ser feita de duas formas, a primeira é do jeito que tu quer.

SELECT
  cat.id,
  cat.nome,
  SUM( IF( mov.tipo = 0, mov.qtd, NULL ) ) AS qtd_tipo_0,
  SUM( IF( mov.tipo = 0, mov.total, NULL ) ) AS total_tipo_0,
  SUM( IF( mov.tipo = 1, mov.qtd, NULL ) ) AS qtd_tipo_1,
  SUM( IF( mov.tipo = 1, mov.total, NULL ) ) AS total_tipo_1,
  SUM( IF( mov.tipo = 2, mov.qtd, NULL ) ) AS qtd_tipo_2,
  SUM( IF( mov.tipo = 2, mov.total, NULL ) ) AS total_tipo_2
FROM lc_movimento AS mov
INNER JOIN lc_cat AS cat
  ON cat.id = mov.cat
GROUP BY cat.nome ASC

Mas a segunda é muito melhor, e muito mais "bonita", rs.

SELECT
  cat.id,
  cat.nome,
  mov.tipo,
  SUM( mov.qtd ) AS qtd,
  SUM( mov.total ) AS total
FROM lc_movimento AS mov
INNER JOIN lc_cat AS cat
  ON cat.id = mov.cat
GROUP BY cat.nome ASC, mov.tipo ASC

Você vai notar também que o relacionamento eu fiz diferente, eu não sei qual é mais eficaz. A princípio ajuda, mas sempre gosto de relembrar o problema do N+1, hehe.

[rcamu 3]

Lokaodomau, valeu, era isso mesmo que preciso....valeu pelos link´s vou dar uma estudada também.

 

realmente a segunda é bem mais rápida que a 1o. só tem um problema se o item tem movimento nos 3 tipos ele cria 3 linhas uma para cada tipo.

 

como vou exportar isso para um excel teria que ser por colunas o que a primeira faz mas fica mais lenta.

 

tem como colocar a segunda em colunas e não em linhas?

 

E uma duvida como calculo o saldo, qtd_tipo_1 - qtd_tipo_2 - qtd_tipo_0 = XPTO ?

[lokaodomau 61]

O MySQL não tem uma função que faça isso, por isso eu fiz aquela primeira SQL.

 

Mas eu também tinha de fazer relatórios e fazer apresentações, não sei se ajudaria, mas pode utilizar as informações como base de uma tabela dinâmica, e na tabela dinâmica já ficava tudo do modo que eu queria.

 

Enfim, mas se foi resolvido, não esquece de marcar como resolvido.