当A、B相等时，对组/和列C、D的SQL查询

Question

我有一个有4列的MySQL表，我们将它们称为C1、C2、C3和C4。

在这些数据中，我有如下所示的数据集：

C1,C2,C3,C4
A,B,12,24
C,D,23,13
A,D,19,15
B,C,15,15
A,C,21,9
B,D,19,2
B,A,8,19
D,C,29,1

我需要得到如下输出：

A,71
B,66
D,59
C,48

我试图编写一个查询，但似乎无法理解如何在另外两个字段相等时对两个字段进行求和，并对它们进行分组/排序。

SELECT C1, SUM(C3,C4) AS Total
WHERE C1 = C2
GROUP BY C1
ORDER BY C1 Desc;

这将返回一个空集，我知道为什么，因为C1和C2在同一行中不相等，但似乎无法确定下一步该做什么。

Answer 1

我这样做是因为：

select col, sum(val)
from ((select c1 as col, c3 as val from t
      ) union all
      (select c2, c4 from t
      )
     ) cc
group by col;

考虑到您提供的数据，这似乎是最简单的方法。与在or条件下使用join的方法相比，这应该具有更好的性能。

Answer 2

第一解决方案(MySql 8.0):

WITH d AS (
  SELECT DISTINCT C1 AS name FROM tablename
  UNION
  SELECT DISTINCT C2 AS name FROM tablename
)

SELECT tt.name, SUM(tt.value) AS total FROM (
  SELECT 
    d.name,
    CASE WHEN t.C1 = d.name THEN C3 ELSE 0 END + CASE WHEN t.C2 = d.name THEN C4 ELSE 0 END AS value
  FROM d
  INNER JOIN tablename AS t 
  ON t.C1 = d.name OR t.C2 = d.name 
) AS tt
GROUP BY name;

请参阅演示

第二解决方案(MySql 5.7):

SELECT tt.name, SUM(tt.value) AS total FROM (
SELECT 
  d.name,
  CASE WHEN t.C1 = d.name THEN C3 ELSE 0 END + CASE WHEN t.C2 = d.name THEN C4 ELSE 0 END AS value
FROM (
  SELECT DISTINCT C1 AS name FROM tablename
  UNION
  SELECT DISTINCT C2 AS name FROM tablename
) AS  d
INNER JOIN tablename AS t 
ON t.C1 = d.name OR t.C2 = d.name 
) AS tt
GROUP BY name;

请参阅演示