sql - 在PostgreSQL中汇总具有互换的列值的行

我的输入表看起来像这样

Source   Destn   Value
-----------------------
A        B        1
B        A        2

期望输出->A B 3
在postgresql中获得上述结果的适当查询是什么？

最佳答案

试试这个：

SELECT CASE WHEN Source < Destn THEN Source ELSE Destn END,
       CASE WHEN Source >= Destn THEN Source ELSE Destn END,
       SUM(Value)
FROM mytable
GROUP BY CASE WHEN Source < Destn THEN Source ELSE Destn END,
         CASE WHEN Source >= Destn THEN Source ELSE Destn END

Demo here
编辑：功劳归于@nick barnes
使用LEAST和GREATEST可以简化查询：

SELECT least("Source","Destn"),
       greatest("Source","Destn"),
       SUM("Value")
FROM mytable
GROUP BY least("Source","Destn"),
         greatest("Source","Destn")

Demo here