mysql - 如何在第二个查询中没有记录时减去两个表的列值

Question

在我的学校申请中，我想知道学生的应有数量所以在我的

fee_class_mapping 表我有分配给学生类(class)的费用如下

+----------------------+------------+----------+------------+
| fee_class_mapping_id | fee_cat_id | class_id | fee_amount |
+----------------------+------------+----------+------------+
|                    1 |          2 |        1 | 10000      |
|                    2 |          2 |        2 | 15000      |
|                    3 |          3 |        1 | 2000       |
+----------------------+------------+----------+------------+

在 student_present_class_details 表中我会有学生类(class)信息

+----------------------+----------+
| student_admission_id | class_id |
+----------------------+----------+
|                    1 | 1        |
|                    2 | 2        |
|                    3 | 1        |
|                    4 | 1        |
+----------------------+----------+

通过映射 student_present_class_details class_id 和 fee_class_mapping class_id 我会得到分配给学生的费用

还有另一个表包含fee_category 详细信息

+------------+--------------+-----------+
| fee_cat_id | fee_cat_name | parent_id |
+------------+--------------+-----------+
|          1 | School Fee   |         0 |
|          2 | School Fee   |         1 |
|          3 | books        |         1 |
|          4 | Dhobi        |         0 |
+------------+--------------+-----------+

我会通过以下查询获得特定学生总金额的 fee_category 金额

select f.fee_cat_name, sum(fcm.fee_amount) from  fee_class_mapping fcm ,student_present_class_details pd ,fee_category f 

where pd.class_id=fcm.class_id and f.fee_cat_id=fcm.fee_cat_id and pd.student_admission_id=4 group by fcm.fee_cat_id;

输出

+--------------+---------------------+
| fee_cat_name | sum(fcm.fee_amount) |
+--------------+---------------------+
| School Fee   |               10000 |
| books        |                2000 |
+--------------+---------------------+

我有另一个名为 fee_transactions 的表，其中包含学生费用类别的交易详细信息，表结构为

+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| transaction_id      | int(11)          | NO   | PRI | NULL    | auto_increment |
| student_id          | int(11)          | NO   | MUL | NULL    |                |
| fee_cat_id          | int(11)          | NO   | MUL | NULL    |                |
| amount_paid         | varchar(255)     | YES  |     | NULL    |                |
| paid_on             | date             | YES  |     | NULL    |                |
| trans_receipt_no    | varchar(255)     | YES  |     | NULL    |                |
| is_concession_given | enum('Yes','No') | YES  |     | NULL    |                |
| payment_mode        | varchar(45)      | YES  |     | NULL    |                |
| refrence_number     | varchar(45)      | YES  |     | NULL    |                |
| tdm_id              | int(11)          | YES  |     | NULL    |                |
+---------------------+------------------+------+-----+---------+----------------+

从上表中我会得到支付费用类别的总和如下

select sum(ft.amount_paid) from fee_transactions ft where ft.student_id=4 group by ft.fee_cat_id;

输出

+---------------------+
| sum(ft.amount_paid) |
+---------------------+
|                3000 | //school fee
+---------------------+

我要减去总金额 - 支付的金额

所以我写了下面的查询来这样做

select f.fee_cat_name, sum(fcm.fee_amount)-(select sum(ft.amount_paid) from fee_transactions ft where ft.student_id=4 and ft.fee_cat_id=fcm.fee_cat_id group by ft.fee_cat_id) from  fee_class_mapping fcm ,student_present_class_details pd ,fee_category f

where pd.class_id=fcm.class_id and f.fee_cat_id=fcm.fee_cat_id and pd.student_admission_id=4 group by fcm.fee_cat_id;

输出输出

+--------------+------------+
| fee_cat_name | paidAmount |
+--------------+------------+
| School Fee   |       7000 |
| books        |       NULL |
+--------------+------------+

在 fee_transaction 表中我没有书费条目，它有学费条目

学费总额=10000 - 缴纳金额3000 = 7000正确

对于书费，我在 fee_transaction 表中没有记录所以它没有返回值

我想要的是

 +--------------+------------+
    | fee_cat_name | paidAmount |
    +--------------+------------+
    | School Fee   |       7000 |
    | books        |       NULL | //2000 instead of NULL
    +--------------+------------+

我将如何实现它。

请帮帮我。谢谢!!

Answer 1

使用coalesce()在子查询的结果中用 0 替换 NULL。

SELECT f.fee_cat_name,
       sum(fcm.fee_amount) - coalesce((SELECT sum(ft.amount_paid)
                                              FROM fee_transactions ft
                                              WHERE ft.student_id = 4
                                                    AND ft.fee_cat_id = fcm.fee_cat_id
                                              GROUP BY ft.fee_cat_id), 0)
       FROM fee_class_mapping fcm
            INNER JOIN student_present_class_details pd
                       ON pd.class_id = fcm.class_id
            INNER JOIN fee_category f
                       ON f.fee_cat_id = fcm.fee_cat_id
       WHERE pd.student_admission_id = 4
       GROUP BY fcm.fee_cat_id;

我还重写了它以使用明确的 JOIN 语法，推荐使用它，因为它更容易阅读和理解。