在 MYSQL 中使用单个查询获取 3 个不同 SUM 的“最佳”方法是什么? - What is the "best" way to get 3 different SUM using a single query in MYSQL

What is the "best" way to get 3 different SUM using a single query in MYSQL

这似乎是主观的，但事实并非如此。

鉴于：

Table with the ff. field:
 - userid
 - breakstart
 - breakend
 - minutes
 - breaktype

目标：获取特定user的每种类型的休息SUM()，包括breakstart和breakend

到目前为止我做了什么：我能想到的最简单的是做 3 个单独的查询，就像这样......

SELECT SUM(minutes) as totalbreak 
FROM `breaklog`
WHERE
    userid = <some userid>
      AND
    DATE(breakstart) = "2015-06-11"
      AND
    DATE(breakend) = "2015-06-11"
        AND
    breaktype = "1"
# just feed breaktype with valid types i.e. ("1", "2", "3")

我

只是想知道是否有更好的方法来实现我的目标，可能只需要一个查询？或者这甚至可能吗？还是最好获取所有breaktypes并在SQL引擎之外进行过滤，顺便说一句，我在后端使用PHP。

提前感谢所有输入。

做一个GROUP BY：

SELECT breaktype, SUM(minutes) as totalbreak 
FROM `breaklog`
WHERE
    userid = <some userid>
      AND
    DATE(breakstart) = "2015-06-11"
      AND
    DATE(breakend) = "2015-06-11"
        AND
    breaktype IN ("1", "2", "3")
group by breaktype

您也可以通过拥有的组尝试这个

SELECT breaktype, SUM(minutes) as totalbreak 
FROM breaklog
WHERE
    userid = <some userid>
      AND
    DATE(breakstart) = "2015-06-11"
      AND
    DATE(breakend) = "2015-06-11"
group by breaktype
Having breaktype IN (1, 2,3)