我有3张桌子。 practicesadjustmentsclaims
我需要选择所有带有附加列adjustments.amount + count(claims.id)的实践

这是我的查询。

SELECT
 practices.*,
 (
   SUM(adjustments.amount) + count(claims.id)
 ) as accurred_fees
  INNER JOIN adjustments ON adjustments.practice_id = practices.id
  INNER JOIN claims ON claims.practice_id = practices.id
 GROUP BY practices.id


在我的数据库中,我有1次练习,20次索赔和1次调整,金额等于2。查询中的SUM(eligible_fee_adjustments.amount)总是返回40,我想它是2 * count(claims.id),我不知道为什么会这样。您能帮我找到解决方法吗?提前致谢

最佳答案

您在沿着两个不同的维度进行连接时遇到问题,因此行正在相乘。您可以通过预先汇总数据来解决此问题。以下将解决此特定问题:

SELECT p.*,
       (SUM(a.amount) + c.cnt) as accurred_fees
FROM practices p INNER JOIN
     adjustments a
     ON a.practice_id = p.id INNER JOIN
     (SELECT practice_id, count(*) as cnt
      FROM claims c
      GROUP BY practice_id
     ) c
     ON c.practice_id = p.id
GROUP BY p.id;


您可能还应该预先汇总调整:

SELECT p.*,
       (a.amount + c.cnt) as accurred_fees
FROM practices p INNER JOIN
     (SELECT practice_id, SUM(amount) as amount
      FROM adjustments a
      GROUP BY practice_id
     ) a
     ON a.practice_id = p.id INNER JOIN
     (SELECT practice_id, count(*) as cnt
      FROM claims c
      GROUP BY practice_id
     ) c
     ON c.practice_id = p.id
GROUP BY p.id;


并且您可能想考虑使用LEFT OUTER JOIN而不是INNER JOIN,以便获得所有实践,即使是那些可能缺少调整或主张的实践。

关于mysql - 联接表的唯一行的总和,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23170540/

10-08 20:48