我在下面的SQL中遇到问题:
-
字段
mdoc_valor
不能复制或添加,因为它是单个值并且不是聚合的一部分,因此只有mprd_valor
字段应按子表中的原样添加。 也就是说,modc_valor
字段位于父表中,mprd_valor
字段位于子表中。 -
正在发生的事情是
mdoc_valor
字段正在根据聚合添加值,我不希望更改此字段。
select dpto_codigo,
dpto_descricao,
sum(mdoc_valor) as Liquido,
sum(mprd_valor) as Compras_Bruta
from movdctos
inner join movprodd1017 on (mdoc_transacao=mprd_transacao)
inner join produtos on (prod_codigo=mprd_prod_codigo)
inner join departamentos on (prod_dpto_codigo=dpto_codigo)
where mdoc_datamvto>=CAST('2017-10-31' AS DATE) and
mdoc_datamvto<=CAST('2017-10-31' AS DATE) and
mdoc_dcto_tipo in ('EAQ')
group by
dpto_codigo,
dpto_descricao
根据表名和您的问题,您似乎不需要对mdoc_valor
字段求和。我从此字段中删除了聚合,将其添加到 GROUP BY,还添加了表的别名以提高可读性。
select dpto.dpto_codigo,
dpto.dpto_descricao,
mdoc.mdoc_valor as Liquido,
sum(mprd.mprd_valor) as Compras_Bruta
from movdctos mdoc
inner join movprodd1017 mprd on (mdoc.mdoc_transacao=mprd.mprd_transacao)
inner join produtos prod on (prod.prod_codigo=mprd.mprd_prod_codigo)
inner join departamentos dpto on (prod.prod_dpto_codigo=dpto.dpto_codigo)
where mdoc.mdoc_datamvto>=CAST('2017-10-31' AS DATE) and
mdoc.mdoc_datamvto<=CAST('2017-10-31' AS DATE) and
mdoc.mdoc_dcto_tipo in ('EAQ')
group by
dpto.dpto_codigo,
dpto.dpto_descricao,
mdoc.mdoc_valor;
这应该为您提供dpto_codigo
、dpto_descricao
和mdoc_valor
(重命名为 Liquido(的每个组合SUM(mprd_valor)
。