时间戳

首页 > 解决方案 > SQL Sum, Subtraction 将 4 个表与 where 连接起来

mysql - SQL Sum, Subtraction 将 4 个表与 where 连接起来

问题描述

我想要以下 4 个表格中每个卷号的到期（应收金额总和 - 收到金额总和）..

table: Students
-------------------------------------------------
roll  batch name   trade  code floor  status
--------------------------------------------------
1120    10  KAHHAR  TEXT    111  11    1
1121    10  KAHHAR  TEXT    111  11    1
1122    10  KAHHAR  TEXT    111  11    1
1123    10  KAHHAR  TEXT    111  11    1
1124    10  KAHHAR  TEXT    111  11    1
1125    10  KAHHAR  TEXT    112  1     1
1126    10  Kahhar  text    112  1     1

table: Receivable
-----------------------
roll    month   amount
------------------------
1120    jan-19  5000
1121    jan-19  5000
1122    jan-19  5000
1123    jan-19  5000
1124    jan-19  5000
1125    jan-19  5000
1126    jan-19  5000

table: Receive
-----------------------
roll    month   amount
------------------------
1120    jan-19  1000
1121    jan-19  1000
1122    jan-19  1000
1123    jan-19  1000
1124    jan-19  1000
1125    jan-19  1000
1120    jan-19  1000
1121    jan-19  1000
1122    jan-19  1000
1123    jan-19  1000
1124    jan-19  1000
1125    jan-19  1000
1126    jan-19  0
1126    jan-19  500

table: Residence
-----------------------
code  residence_name
------------------------
111     AAAA
112     BBBB

我要总结（期望的结果）喜欢

---------------------------------------------------
roll    name    trade  batch  residence floor   due
---------------------------------------------------
1120    KAHHAR  TEXT    10  AAAA    11  3000
1121    KAHHAR  TEXT    10  AAAA    11  3000
1122    KAHHAR  TEXT    10  AAAA    11  3000
1123    KAHHAR  TEXT    10  AAAA    11  3000
1124    KAHHAR  TEXT    10  AAAA    11  3000
1125    KAHHAR  TEXT    10  BBBB    1   3000
1126    Kahhar  text    10  BBBB    1   4500

这是我的查询：

SELECT students.roll, students.name, students.trade, 
        students.batch, residence.residence, students.floor,
        sum(receivable.amount) - sum(receive.amount) AS due
FROM receive
    LEFT JOIN receivable ON receivable.roll = receive.roll
    LEFT JOIN students ON receivable.roll = students.roll
    LEFT JOIN residence ON residence.code = students.code
WHERE receivable.roll = receive.roll
GROUP BY roll ORDER BY residence DESC, floor DESC

它给出了以下结果

roll   name    trade batch   residence  floor   due
1120    KAHHAR  TEXT    10  AAAA    11  8000
1121    KAHHAR  TEXT    10  AAAA    11  8000
1122    KAHHAR  TEXT    10  AAAA    11  8000
1123    KAHHAR  TEXT    10  AAAA    11  8000
1124    KAHHAR  TEXT    10  AAAA    11  8000
1125    KAHHAR  TEXT    10  BBBB    1   8000
1126    Kahhar  text    10  BBBB    1   9500

我不知道我的查询有什么问题。我的查询将应收金额（总和（receivable.amount）添加了两次。请帮助我是编码新手

标签： mysqlsql

解决方案

由于具有多个匹配行的多个（两个以上）表之间的连接可能导致行重复、重复等。解决此问题的一种方法是使用相关子查询单独计算 SUM，然后计算差异：

SELECT s.roll, s.name, s.trade, s.batch, rsd.residence, s.floor, 
       (
         COALESCE((SELECT SUM(r.amount) FROM receivable r 
                   WHERE r.roll = s.roll), 0) 
           -  
         COALESCE((SELECT SUM(r.amount) FROM receive r 
                   WHERE r.roll = s.roll), 0)
       ) AS due 
FROM students AS s   
LEFT JOIN residence AS rsd ON rsd.code = s.code
ORDER BY rsd.residence DESC, s.floor DESC

推荐阅读