我在查询数据库时遇到了大麻烦。
我有一张这样的table:
+----------------------------------------+---------+---------+---------+
| name | spent_1 | spent_2 | spent_3 |
+----------------------------------------+---------+---------+---------+
| Category A | NULL | 124000 | 10884 |
| Category A | NULL | 25630 | 9779 |
| Category A | NULL | NULL | NULL |
| Category A | 0 | 0 | 0 |
| Category A | 75000 | NULL | NULL |
| Category A | 4000 | NULL | NULL |
| Category A | 2700 | NULL | NULL |
| Category B | 10585 | 76868 | 15037 |
| Category B | NULL | NULL | NULL |
| Category B | 0 | 0 | NULL |
| Category B | 5500 | NULL | NULL |
| Category C | 100000 | NULL | 140000 |
| Category C | 160000 | NULL | NULL |
| Category C | NULL | NULL | NULL |
| Category C | NULL | NULL | NULL |
| Category C | 0 | 0 | NULL |
| Category C | NULL | NULL | NULL |
| Category C | 100000 | NULL | NULL |
| Category C | NULL | NULL | NULL |
| Category C | NULL | NULL | NULL |
| Category C | NULL | NULL | 102040 |
| Category C | NULL | NULL | NULL |
| Category C | NULL | NULL | NULL |
| Category C | 75000 | NULL | NULL |
| Category C | 26000 | NULL | NULL |
+----------------------------------------+---------+---------+---------+
我想对每个类别的“花费\u 1”、“花费\u 2”和“花费\u 3”中的所有值求和,得到如下表:
+---------------------------------------------+-----------------------+
| name | total_spent |
+---------------------------------------------+-----------------------+
| Category A | sum of all occurences |
+---------------------------------------------+-----------------------+
| Category B | sum of all occurences |
+---------------------------------------------+-----------------------+
| Category C | sum of all occurences |
+---------------------------------------------+-----------------------+
1条答案
按热度按时间6psbrbz91#
我不知道这是否是最佳方式,但您可以尝试以下方法:
你可以选择
COALESCE
(是ansi标准)而不是IFNULL
(不是):