mysql - mysql 两个表中两列的总和

Question

我正在为一家机构开发 LMS 系统我正在努力在月底制定一份恢复报告该报告包含学生姓名总费用包、已收到的总额、应收到的总额、当月待付款

这是一个学生的分期付款数据及其准考证

这是分类账数据，我可以从中选择费用包和可收取的总费用

我正在使用此查询来获取恢复报告

SELECT 
SUM(l.dr)-SUM(l.cr) as sum_remaining, 
f.dr as fee_package, 
SUM(i.payment) as this_month_install,
a.reg_id, s.fname
FROM
ledger l, ledger f, student_data s, 
admissions a LEFT OUTER JOIN installments i ON a.admissionid = i.admissionid
WHERE 
a.admissionid = '58ac4b5421488' AND
a.reg_id = s.reg_id AND
l.reference = '58ac4b5421488' AND
l.details <> 'registration fee' AND
f.reference = '58ac4b5421488' AND
f.details = 'Fee Package' AND  
i.install_no <> '1' AND 
MONTH(i.pay_date) = '04' AND 
YEAR(i.pay_date) = '2017'
GROUP BY a.admissionid

它给出的结果是这样的

但结果应该是这样的

剩余总和 = 10000this_month_install = 10000请帮我解决这个问题提前致谢

Answer 1

您应该从入场开始，并对其他表使用内连接(分期付款时使用左连接)

  SELECT 
    SUM(l.dr)-SUM(l.cr) as sum_remaining, 
    f.dr as fee_package, 
    SUM(i.payment) as this_month_install,
    a.reg_id, 
    s.fname
  FROM admissions a
  Inner JOIN ledger f ON f.reference = a.admissionid AND f.details = 'Fee Package' 
  INNER JOIN ledger l  ON l.reference = a.admissionid AND l.details <> 'registration fee'  
  INNER JOIN student_data s ON a.reg_id = s.reg_id
  LEFT  JOIN installments i ON a.admissionid = i.admissionid   AND i.install_no <> '1' 
  WHERE a.admissionid = '58ac4b5421488' 
  AND  MONTH(i.pay_date) = '04' 
  AND  YEAR(i.pay_date) = '2017'
  GROUP BY a.admissionid

分期付款表中有两行匹配..尝试仅过滤一个

SELECT 
SUM(l.dr)-SUM(l.cr) as sum_remaining, 
f.dr as fee_package, 
SUM(i.payment) as this_month_install,
a.reg_id, 
s.fname
FROM admissions a
Inner JOIN ledger f ON f.reference = a.admissionid AND f.details = 'Fee Package' 
INNER JOIN ledger l  ON l.reference = a.admissionid AND l.details <> 'registration fee'  
INNER JOIN student_data s ON a.reg_id = s.reg_id
LEFT  JOIN installments i ON a.admissionid = i.admissionid 
                        AND i.install_no not in ( '1', '2') 
WHERE a.admissionid = '58ac4b5421488' 
 AND  MONTH(i.pay_date) = '04' 
AND  YEAR(i.pay_date) = '2017'
GROUP BY a.admissionid