mysql - 合并sql中的多列求和

Question

这里我得到的 cr.credit 值是相同值的倍数。但我需要准确的值来计算总值(value)。任何人都可以帮助我得到答案。

SELECT
  gm.id,
  gm.groupid,
  gm.tktno,
  gm.memberid,
  (COALESCE((g.emdue - ae.dividend), 0)) AS a,
  SUM(ra.debit) AS deb,
  SUM(DISTINCT ra.credit) AS cr,
  COALESCE((SUM(ra.debit) - SUM(ra.credit)), 0) AS b,
  COALESCE(SUM(cr.credit_value), 0) AS c,
  COALESCE((COALESCE((g.emdue - ae.dividend), 0) - COALESCE((SUM(ra.debit) - SUM(ra.credit)), 0)
  - COALESCE(SUM(cr.credit_value), 0)), 0) AS total
FROM groupmember gm
LEFT JOIN runningaccount ra
  ON ra.group_id = gm.groupid
  AND ra.ticket_no = gm.tktno
  AND gm.memberid = ra.member_id
  AND gm.id = ra.group_member_id
LEFT JOIN chitreceipt cr
  ON gm.groupid = cr.groupid
  AND gm.memberid = cr.memberid
  AND gm.tktno = cr.tktno
LEFT JOIN groupnew g
  ON g.id = gm.groupid
LEFT JOIN auctionentry ae
  ON ae.groupid = gm.groupid
GROUP BY gm.memberid,
         gm.groupid,
         gm.tktno

Answer 1

您可以对该列执行不同操作，然后求和，以便获得不同值的总和。就像

sum(distinct COALESCE(cr.credit_value,0)) 

无论你在哪里使用它，都像上面一样使用它。