php - 求和函数返回错误值

Question

我在 MySQL 中有 4 个这样的表

第一张表:学生

id    Name       roll      final
1     vivek      22222     65000
2     abc        33333     50000

第二张表:打折

id    gst_amount   roll 
1     5000         22222
2     6500         22222
3     15000        22222
4     6000         33333

第三张表:费用

id    amount       roll 
1     15000        22222
2     5000         22222
3     5000         33333

第四张表:pdc

id    amount       roll 
1     9250         22222
2     9250         22222

我想加入所有表，并希望将 pdc 和 fee 表中的金额总和以及 discount 中的 gst_amount 总和 表和最终列值。

我试过这个查询。

SELECT student.roll, final, SUM(discount.gst_amount) AS total, SUM(fee.amount) AS fee, SUM(pdc.amount) AS pdc  
FROM student 
LEFT JOIN discount on student.roll = discount.roll 
LEFT JOIN fee on fee.roll = student.roll 
LEFT JOIN pdc on pdc.roll = student.roll 
GROUP BY student.roll

它给我这个输出。

roll      final     total     fee       pdc
22222     65000     106000    120000    110000
33333     50000     6000      5000      NULL

我想要这个输出。

roll      final     total     fee       pdc
22222     65000     26000     20000     18500
33333     50000     6000      5000      NULL

Answer 1

您的原始查询正在计算重复的总和值。
我认为您可以理解将 count(1) 列添加到您的原始查询中。
以下是我的输出。

mysql> SELECT student.roll, count(1) as count
    -> FROM student
    -> LEFT JOIN discount on student.roll = discount.roll
    -> LEFT JOIN fee on fee.roll = student.roll
    -> LEFT JOIN pdc on pdc.roll = student.roll
    -> GROUP BY student.roll
    -> ;
+-------+-------+
| roll  | count |
+-------+-------+
| 22222 |    12 |
| 33333 |     1 |
+-------+-------+
2 rows in set (0.00 sec)

基于这些，请运行如下sql。
我认为这个 sql 有改进的地方，但我现在已经得到了你预期的结果。
另外，您想要的输出总列中的 26000 是错误的，我认为 26500 是正确的。

SELECT 
    student.roll,
    final,
    SUM(discount.gst_amount) AS total,
    SUM(fee.amount) AS fee,
    SUM(pdc.amount) AS pdc
FROM
    student
LEFT JOIN
    (SELECT 
        roll, SUM(gst_amount) AS gst_amount
    FROM
        discount
    GROUP BY roll) AS discount ON discount.roll = student.roll
LEFT JOIN
    (SELECT 
        roll, SUM(amount) AS amount
    FROM
        fee
    GROUP BY roll) AS fee ON fee.roll = student.roll
LEFT JOIN
    (SELECT 
        roll, SUM(amount) AS amount
    FROM
        pdc
    GROUP BY roll) AS pdc ON pdc.roll = student.roll
GROUP BY student.roll