如果有多个条件，如何使用SQL求和

Question

| 1dist | 1result | 2dist | 2 result | 3dist | 3result|
|-------|---------|-------|----------|-------|--------|
|  5    |  MADE   |  NULL |   NULL   |  NULL | NULL   |
| 10    |  MISS   |  2    |   MADE   |  NULL | NULL   |
|  5    |  MADE   |  NULL |   NULL   |  NULL | NULL   |
| 20    |  MISS   |  5    |   MILL   |  2    | MADE   |

从这个表中，我想要求和结果列=生成的1dist、2dist和3dist。我在这个例子中寻找的结果是5+2+5+2=14

SELECT SUM(CASE WHEN 1result = MADE THEN 1dist) +
SUM(CASE WHEN 2result = MADE THEN 2dist) +
SUM(CASE WHEN 3result = MADE THEN 3dist)
FROM table

Answer 1

在您的查询中，您只缺少END，当您比较任何字符串时，您必须使用单引号中的字符串'

试试这个

SELECT SUM(CASE WHEN 1result = 'MADE' THEN 1dist END) +
SUM(CASE WHEN 2result = 'MADE' THEN 2dist END) +
SUM(CASE WHEN 3result = 'MADE' THEN 3dist END)  As Final_Result
FROM Table_Name

Answer 2

有多种方法可以做到这一点——也许有更好的方法或更优化的方法，试试这个，我认为表名是test。只是并集所有对列

SELECT SUM(dist) AS sum, result 
FROM (
SELECT [1dist] AS dist, [1result] AS result 
FROM test 
UNION ALL 
SELECT [2dist] AS dist, [2result] AS result 
FROM test  
UNION ALL 
SELECT [3dist] AS dist, [3result] AS result 
FROM test 
) AS a 
GROUP BY result 
HAVING (result = 'made')