在连接表中按条件获取sum
提问于 2012-10-25 16:44:05
我的数据库结构如下：
‘'Movimientos’表记录了我的系统的所有费用和付款。“‘Cargos”表保存了“Movimientos”上每笔费用的一些元数据，“Abonos”表保存了“Movimientos”上每笔付款的一些元数据。
如何编写一个查询，对'Movimientos‘中'Abonos.tipo_abono’和'Cargos.tipo_cargo‘有一定条件的所有记录求和？
我有一个已经对'Movimientos‘求和的查询，但是我不能添加'Abonos’和'Cargos‘表添加更多条件的部分。
SELECT SUM(M.monto) as monto, SUM(M.interes) as interes, SUM(M.iva) as iva, SUM(M.capital) as capital
FROM movimientos AS M
JOIN acreditados AS A ON A.id_acreditado = M.id_acreditado
JOIN creditos AS C ON C.id_credito = A.id_credito
WHERE C.id_credito = 29假设我想修改最后一个查询，使其只对'Abono.tipo_abono'=1和‘Cargo.tipo_cargo’=1的记录求和，我该怎么做？
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-10-25 16:49:44
如果您的关系是一对一的，则只需将表加入并根据您的条件进行过滤即可。如果相关记录始终存在，则将其设置为内部联接。如果相关的表可能没有记录，那么它应该是一个符合JOIN中指定条件的LEFT OUTER JOIN，而不是WHERE。
SELECT SUM(M.monto) as monto,
SUM(M.interes) as interes,
SUM(M.iva) as iva,
SUM(M.capital) as capital
FROM movimientos AS M
JOIN acreditados AS A ON A.id_acreditado = M.id_acreditado
JOIN creditos AS C ON C.id_credito = A.id_credito
LEFT OUTER JOIN Abonos AS AB on AB.id_movimiento = M.id_movimiento AND AB.tipo_abono = 1
LEFT OUTER JOIN Cargos AS CG on CG.id_movimiento = M.id_movimiento AND CG.tipo_cargo = 1
WHERE C.id_credito = 29但是，如果您的关系是多对一的，这将通过包括多个movimientos行来影响总和。改用EXISTS子句：
SELECT SUM(M.monto) as monto,
SUM(M.interes) as interes,
SUM(M.iva) as iva,
SUM(M.capital) as capital
FROM movimientos AS M
JOIN acreditados AS A ON A.id_acreditado = M.id_acreditado
JOIN creditos AS C ON C.id_credito = A.id_credito
WHERE C.id_credito = 29
AND EXISTS (SELECT 1
FROM Abonos AB
WHERE AB.id_movimiento = M.id_movimiento
AND tipo_abono = 1
)
AND EXISTS (SELECT 1
FROM Cargos CG
WHERE CG.id_movimiento = M.id_movimiento
AND tipo_cargo = 1
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13073322
复制相关文章
腾讯云开发者
