通過查看兩列而不考慮順序並將每列分別求和來對行進行分組

我有一張如下所示的表格：

+---------+---------+-------+
| SOURCE  | DEST    | VALUE |
+---------+---------+-------+
| A       | B       | 1     |
| B       | A       | 2     |
| A       | B       | 3     |
| C       | D       | 5     |
| D       | C       | 6     |
+---------+---------+-------+

所以我要做的是通過查看 value1 和 value2 將它們組合在一起，而不考慮這兩個值的順序，這意味著“A 和 B”與“B 和 A”相同。

我正在尋找一個看起來像這樣的結果：

+---------+---------+-------------+-----------+
|         |         | SUM(SOURCE) | SUM(DEST) |
+---------+---------+-------------+-----------+
| A       | B       | 4           | 2         |
| C       | D       | 5           | 6         |
+---------+---------+-------------+-----------+

有誰知道我怎麼能得到這個？

（我正在嘗試在 SQL Server 和 MySQL 上執行此操作）

相關：通過查看兩列而不考慮順序來對行進行分組

它不漂亮，但它有效。

SELECT * INTO YourTable
FROM
(
   SELECT 'A' AS [SOURCE],'B' AS DEST, 1 AS VALUE
   UNION ALL
   SELECT 'B','A',2
   UNION ALL
   SELECT 'A','B',3
   UNION ALL
   SELECT 'C','D',5
   UNION ALL
   SELECT 'D','C',6
) A;

WITH CTE
AS
(
   SELECT  CASE
               WHEN [Source] < Dest THEN [Source]
               ELSE Dest
           END col1,
           CASE
               WHEN [Source] < Dest THEN [Dest]
               ELSE [Source]
           END col2,
           SUM(Value) AS [SUM(Total)]
   FROM YourTable
   GROUP BY 
       CASE WHEN [Source] < Dest THEN [Source] ELSE Dest END,
       CASE WHEN [Source] < Dest THEN [Dest] ELSE [Source] END
)

SELECT  A.col1,
       A.col2,
       SUM(B.VALUE) AS [SUM(SOURCE)],
       [SUM(Total)]-SUM(B.VALUE) AS [SUM(DEST)]
FROM CTE A
INNER JOIN YourTable B
   ON A.[col1] = B.[SOURCE]
   AND A.col2 = B.DEST
GROUP BY A.col1,A.col2,[SUM(Total)]

DROP TABLE YourTable

結果：

col1 col2 SUM(SOURCE) SUM(DEST)
---- ---- ----------- -----------
A    B    4           2
C    D    5           6

SELECT  LEAST(source, dest),
       GREATEST(source, dest),
       SUM(IF(source < dest, value, 0)),
       SUM(IF(source > dest, value, 0))
   FROM  tbl
   GROUP BY  1,2;

引用自：https://dba.stackexchange.com/questions/94816