我有两个问题很难解决。
显示不同表中两个单元格值之间的差异
根据子查询中返回的行数和/或单元格的值显示自定义输出
在这个特定的例子中，我有几个表需要在一个mysql查询中与自定义输出连接和组合。
去掉非必要列并包含虚假数据的表是：

transactions
+----+-------------+------------+--------+
| id | vendor_id   | payment_id | amount |
+----+-------------+------------+--------+
| 1  | 12          | 45         | 25     |
| 2  | 13          | 46         | 50     |
| 3  | 14          | 47         | 50     |
| 4  | 15          | 48         | 50     |
+----+-------------+------------+--------+

vendors
+-----+-------------+
| id  | vendor_code |
+-----+-------------+
| 12  | abc123      |
| 13  | efg321      |
| 14  | hjk456      |
| 15  | fed789      |
+-----+-------------+

payments
+-----+-------+--------+
| id  | order | amount |
+-----+-------+--------+
| 45  | 0032  | 25     |
| 46  | 0063  | 50     |
| 47  | 0073  | 50     |
| 48  | 0087  | 50     |
+-----+-------+--------+

refunds
+----+----------------+--------+
| id | transaction_id | amount |
+----+----------------+--------+
| 65 | 2              | 10     |
+----+----------------+--------+

properties
+-----+----------+
| id  | group_id |
+-----+----------+
| 100 | 222      |
| 200 | 333      |
+-----+----------+

vendor_properties
+----+-------------+------------+
| id | property_id | vendor_id  |
+----+-------------+------------+
| 1  | 100         | 12         |
| 2  | 200         | 12         |
| 3  | 100         | 13         |
| 4  | 200         | 14         |
+----+-------------+------------+

groups
+-----+---------+
| id  | name    |
+-----+---------+
| 222 | groupA  |
| 333 | groupB  |
+-----+---------+

存在以下关系：

transactions.payment_id -> payments.id
transactions.vendor_id -> vendors.id
payments.vendor_id -> vendors.id
refunds.transaction_id -> transactions.id
properties.property_group_id -> groups.id
vendor_properties.property_id -> properties.id
vendor_properties.vendor_id -> vendors.id

我当前的sql查询是：

SELECT
    `transactions`.`id` AS DB_transID,
    `transactions`.`vendor_id` AS DB_vendID,
    `vendors`.`vendor_code` AS "VendorCode",
    `payments`.`order` AS "Order",
    `transactions`.`amount` AS "Amount"
    `refunds`.`amount` AS "Refunded",
    `groups`.`name` AS "Group"
FROM
    `transactions`
LEFT JOIN
    `payments` ON `payments`.`id` = `transactions`.`payment_id`
LEFT JOIN
    `vendors` ON `vendors`.`id` = `transactions`.`vendor_id`
LEFT JOIN
    `refunds` ON `refunds`.`transaction_id` = `transactions`.`id`
LEFT JOIN
    `groups` ON `groups`.`id` IN
    ( 
        SELECT `group_id` FROM `properties` WHERE `id` IN
        ( 
            SELECT `property_id` FROM `vendor_properties`
            WHERE `vendor_id` = @refID
        )
     );

输出为：

+------------+-----------+------------+-------+--------+----------+--------+
| DB_transID | DB_vendID | VendorCode | Order | Amount | Refunded | Group  |
+------------+-----------+------------+-------+--------+----------+--------+
| 1          | 12        | abc123     | 0032  | 25     | NULL     | groupA |
| 1          | 12        | abc123     | 0032  | 25     | NULL     | groupB |
| 2          | 13        | efg321     | 0063  | 50     | 10       | groupA |
| 3          | 14        | hjk456     | 0073  | 50     | NULL     | groupB |
| 4          | 15        | fed789     | 0087  | 50     | NULL     | NULL   |
+------------+-----------+------------+-------+--------+----------+--------+

我想做的是：
添加包含（金额-退款）值的“净额”列
当一个事务有多个组（如事务1中所示）时，我不希望创建两行，而是希望在组列中有一行带有“groupa，groupb”。当group为null时，值应该为“none”。应删除组名列表中的重复数据。例如，如果groupa出现在三个事务中，groupb出现在两个事务中，那么输出将是“groupa，groupb”。
退款为空时，值应为“0”
所以期望的输出是：

+------------+-----------+------------+-------+--------+----------+-----+----------------+
| DB_transID | DB_vendID | VendorCode | Order | Amount | Refunded | Net | Group          |
+------------+-----------+------------+-------+--------+----------+-----+----------------+
| 1          | 12        | abc123     | 0032  | 25     | 0        | 25  | groupA, groupB |
| 2          | 13        | efg321     | 0063  | 50     | 10       | 40  | groupA         |
| 3          | 14        | hjk456     | 0073  | 50     | 0        | 50  | groupB         |
| 4          | 15        | fed789     | 0087  | 50     | 0        | 50  | None           |
+------------+-----------+------------+-------+--------+----------+-----+----------------+

在现实生活中，这些表中的大多数都有几千行。作为一个额外的问题，告诉我为什么我有时会得到空的组，即使数据是这样的，应该有一个值？
欢迎对查询进行任何其他改进！