基于col值的MySQL总和 [英] MySQL sum based on col value

问题描述

我有这张桌子:

+----+--------+-------+
| id | fruit  | a-b-c |
+----+--------+-------+
| 1  | orange | a     |
+----+--------+-------+
| 2  | banana | c     |
+----+--------+-------+
| 3  | orange | c     |
+----+--------+-------+
| 4  | orange | a     |
+----+--------+-------+
| 5  | orange | b     |
+----+--------+-------+

现在，我想基于a-b-c值列出所有水果和sum.a=1，b=2和c=3.所以，这就是我想要的结果:

Now I want to list all the fruits and a sum based on the a-b-c values.a=1, b=2 and c=3. So, this would be the result I want:

+--------+-----+
| fruit  | sum |
+--------+-----+
| banana | 3   |
+--------+-----+
| orange | 7   |
+--------+-----+

我非常确定我应该使用case，但是我不知道如何对它们进行求和.我的脑子里有这样的东西:

I'm fairly sure that I should use case, but I have no idea how to sum them. I have something like this in my mind:

SELECT
    fruit,
    sum(a-b-c)
    CASE
        WHEN a-b-c = 'a' THEN +=1
        ELSE
    CASE
        WHEN a-b-c= 'b' THEN +=2
        ELSE
    CASE
        WHEN a-b-c= 'c' THEN +=3

    END AS sum

FROM tbl_fruits
GROUP BY fruit;

推荐答案

您可以使用条件聚合:

SELECT fruit, SUM(CASE `a-b-c`
                     WHEN 'a' THEN 1
                     WHEN 'b' THEN 2
                     WHEN 'c' THEN 3
                  END) AS total 
FROM tbl_fruits
GROUP BY fruit;

SqlFiddleDemo

输出:

╔═════════╦═══════╗
║ fruit   ║ total ║
╠═════════╬═══════╣
║ banana  ║     3 ║
║ orange  ║     7 ║
╚═════════╩═══════╝

---

或者使用ELT/FIELD:

SELECT fruit, SUM(ELT(FIELD(`a-b-c`,'a','b','c'),1,2,3)) AS total 
FROM tbl_fruits
GROUP BY fruit;

SqlFiddleDemo2

这篇关于基于col值的MySQL总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！