SAOliveira Posted August 13, 2014 Report Share Posted August 13, 2014 SELECT cli.registro AS registro ,cli.razao AS razao ,MIN(cfs.emissao) AS primeira ,MAX(cfs.emissao) AS ultima ,SUM(cfs.nota_total) AS compra FROM cfs_cabec AS cfs, cli_cadastro AS cli WHERE cfs.status='0' AND cfs.vda_recebimento='3' AND cfs.dest_codigo=cli.registro GROUP BY cli.registro ORDER BY cli.razao Incluir esta string: SELECT SUM(valor_credito) AS pago FROM cli_mensal_extrato WHERE id_cliente=cli.registro para pegar a somatoria de pagamento na tabela cli_mensal_extrato. Não esou sabendo fazer o LEFT JOIN disto. Quote Link to comment Share on other sites More sharing options...
evandro Posted August 13, 2014 Report Share Posted August 13, 2014 Olá, Uma alteranativa seria o código abaixo. CREATE TEMPORARY TABLE SELECT SUM(valor_credito) AS pago FROM cli_mensal_extrato GROUP BY id_cliente; SELECT cli.registro AS registro, cli.razao AS razao, MIN(cfs.emissao) AS primeira, MAX(cfs.emissao) AS ultima, SUM(cfs.nota_total) AS compra, clm.pago FROM cfs_cabec AS cfs, cli_cadastro AS cli LEFT JOIN cli_mensal_extrato AS clm ON clm.id_cliente=cli.registro WHERE cfs.status='0' AND cfs.vda_recebimento='3' AND cfs.dest_codigo=cli.registro GROUP BY cli.registro ORDER BY cli.razao; Quote Link to comment Share on other sites More sharing options...
SAOliveira Posted August 13, 2014 Author Report Share Posted August 13, 2014 Evandro, isto eu faço com View, é que vi num google da vida da forma que postei, fiquei interessado em aprender. Tipo LEFT JOIN SELECT(SUM(....)) Quote Link to comment Share on other sites More sharing options...
SAOliveira Posted August 13, 2014 Author Report Share Posted August 13, 2014 Evandro, o que esta errado. não faz DROP TABLE IF EXISTS t1 CREATE TEMPORARY TABLE t1 SELECT SUM(valor_credito) AS pago FROM cli_mensal_extrato GROUP BY id_cliente faz SELECT id_cliente,data_movto,SUM(valor_credito) AS pago FROM cli_mensal_extrato GROUP BY id_cliente Quote Link to comment Share on other sites More sharing options...
evandro Posted August 13, 2014 Report Share Posted August 13, 2014 Olá, drop table if exists t1; CREATE TEMPORARY TABLE t1 SELECT SUM(valor_credito) AS pago FROM cli_mensal_extrato GROUP BY id_cliente; Quote Link to comment Share on other sites More sharing options...
SAOliveira Posted August 13, 2014 Author Report Share Posted August 13, 2014 Deve ter algo errado no meu Bd. A consulta com Select, retorna todos os registros. Já o Create Table Temporary, retorna vazio. Quote Link to comment Share on other sites More sharing options...
SAOliveira Posted August 13, 2014 Author Report Share Posted August 13, 2014 Evandro, era realmente alguma coisa no bd, troquei o acesso de servidor e resolveu. grato Quote Link to comment Share on other sites More sharing options...
miragerr Posted August 14, 2014 Report Share Posted August 14, 2014 Ola Sergio Pode ser a versão do MYSQL instalada. Quote Link to comment Share on other sites More sharing options...
SAOliveira Posted August 14, 2014 Author Report Share Posted August 14, 2014 Como disse anteriormente, queria testar este lanca do LEFT JOIN que vi na net. SELECT cli.registro AS registro ,cli.razao AS razao ,MIN(cfs.emissao) AS primeira ,MAX(cfs.emissao) AS ultima ,SUM(cfs.nota_total) AS compra FROM cfs_cabec AS cfs, cli_cadastro AS cli LEFT JOIN cli_mensal_extrato AS ext ON SELECT SUM(cli_mensal_extrato.valor_credito) AS pago WHERE cli_mensal_extrato.id_cliente='205' WHERE cfs.status='0' AND cfs.vda_recebimento='3' AND cfs.dest_codigo=cli.registro GROUP BY cli.registro ORDER BY cli.razao Quote Link to comment Share on other sites More sharing options...
SAOliveira Posted August 14, 2014 Author Report Share Posted August 14, 2014 SELECT cfs.dest_codigo,cli.razao,MIN(cfs.emissao) AS pri_compra,MAX(cfs.emissao) AS ult_compra,SUM(cfs.nota_total) AS tot_compra,tmp.pri_pagto,tmp.ult_pagto,tmp.tot_pago,0.00 AS saldo FROM cfs_cabec AS cfs, cli_cadastro AS cli LEFT JOIN (SELECT id_cliente,MIN(data_movto) AS pri_pagto,MAX(data_movto) AS ult_pagto,SUM(valor_credito)AS tot_pago FROM cli_mensal_extrato GROUP BY id_cliente) AS tmp ON cli.registro=tmp.id_cliente WHERE cfs.status='0' AND cfs.vda_recebimento='3' AND cfs.dest_codigo=cli.registro GROUP BY cli.registro ORDER BY cli.razao; Como disse queria testar ou melhor aprender. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.