mysql - 在具有不同 WHERE 条件的 MySQL 中查找不同列的 SUM

Question

我有下表称为designerindices

我想做如下的总和和伪代码

SUM(duration1) WHERE designer1 = X AND designerHistSrNumber = 16 + SUM(duration2) WHERE designer2 = X AND designerHistSrNumber = 16 + SUM(duration3) WHERE designer3 = X AND designerHistSrNumber = 16 + SUM(duration4) WHERE designer 4 = X AND designerHistSrNumber = 16

为此，我编写了以下 4 个单独的查询

SELECT SUM(duration1) as sumdur1 FROM designerindices WHERE designerHistSrNumber = 16 AND designer1 = "X" 这应该输出 10

SELECT SUM(duration2) as sumdur2 FROM designerindices WHERE designerHistSrNumber = 16 AND designer2 = "X" 这应该输出 2.4

SELECT SUM(duration3) as sumdur3 FROM designerindices WHERE designerHistSrNumber = 16 AND designer3 = "X" 这应该输出 5

SELECT SUM(duration4) as sumdur4 FROM designerindices WHERE designerHistSrNumber = 16 AND designer4 = "X" 这应该输出 1.1

我必须执行上述查询 4 ​​次。最后添加sumdur1 + sumdur2 + sumdur3 + sumdur4. 总数应为 18.5

有没有简单直接的方法来代替上述方法？

标签： mysql

Answer 1

您可以将所有 4 个查询包装在单独的 Select 子句中，并在其中添加，例如

Select (
(SELECT SUM(duration1) as sumdur1 FROM designerindices WHERE designerHistSrNumber = 16 AND designer1 = "X")
+
(SELECT SUM(duration2) as sumdur2 FROM designerindices WHERE designerHistSrNumber = 16 AND designer1 = "X")
+
(SELECT SUM(duration3) as sumdur3 FROM designerindices WHERE designerHistSrNumber = 16 AND designer1 = "X")
+
(SELECT SUM(duration4) as sumdur4 FROM designerindices WHERE designerHistSrNumber = 16 AND designer1 = "X")
) from Dual;