所以，我有两个这样的表：
main_table: property_table:
+----------------------------+ +-------------------------+
| event| property_id | value | | property_id | name |
+----------------------------+ +-------------------------+
| 1 | 1 | 100 | | 1 | property1 |
| 2 | 1 | 200 | | 2 | property2 |
| 3 | 2 | 50 | | 3 | property3 |
| 4 | 3 | 10 | +-------------+-----------+
| 5 | 3 | 30 |
并使用以下查询：
SELECT pt.name, SUM(value) as subtotal
FROM main_table as mt
LEFT JOIN property_table as pt
ON mt.property_id = pt.property_id
GROUP BY pt.name;
我得到下表：
+-------------------------+
| name | subtotal |
---------------------------
| property1 | 300 |
| property2 | 50 |
| property3 | 40 |
现在我想在这个表中添加一行来计算所有属性的总数。但这里有一个问题，一些属性要添加，一些属性要减去。因此，为了论证起见，我们可以说它是total = property1 - property2 + property3。
+-------------------------+
| name | subtotal |
---------------------------
| property1 | 300 |
| property2 | 50 |
| property3 | 40 |
| total | 290 |
我现在可以将“GROUP BY”与“WITH ROLLUP”一起使用，但这只会添加所有值。我猜你可以在技术上允许负数进入表并用“WITH ROLLUP”解决所有问题，但这似乎容易出现用户错误。是否超过顶部以避免负整数？
最后我发现使用 GROUP BY WITH ROLLUP 时可以在 SUM 内部实现一个案例：
SELECT pt.name,
SUM(
CASE
WHEN pt.name = 'property2' THEN -mt.value
ELSE mt.value
END
) as subtotal
FROM main_table as mt
LEFT JOIN property_table as pt
ON mt.property_id = pt.property_id
GROUP BY pt.name WITH ROLLUP;
但它的实际实现我选择添加一个名为 action 的列：
property_table:
+-------------------------+---------+
| property_id | name | action |
+-------------------------+---------+
| 1 | property1 | '+' |
| 2 | property2 | '-' |
| 3 | property3 | '+' |
+-------------+-----------+---------+
并用 IF 替换 CASE：
SELECT pt.name,
SUM(IF(pt.action='-', -mt.value, mt.value) as subtotal
FROM main_table as mt
LEFT JOIN property_table as pt
ON mt.property_id = pt.property_id
GROUP BY pt.name WITH ROLLUP;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句