sql - SQL中有没有办法通过自反属性组合对？

Question

假设我有一张桌子（team1，team2，score）。

数据：

A    B    40

B    A    20

我如何将它们组合在一起，以便我的最终结果是

A    B    60

标签： sql

Answer 1

正如 Lukasz 所建议的，大多数数据库都支持least()/ 。greatest()更通用的解决方案使用case：

select (case when team1 < team2 then team1 else team2 end) as team1,
       (case when team1 < team2 then team2 else team1 end) as team2,
       sum(score)
from t
group by (case when team1 < team2 then team1 else team2 end),
         (case when team1 < team2 then team2 else team1 end);

如果所有对都在两个方向的数据中，那就没问题了。但是，如果某些对仅在一个方向上，那么您可能会得到原始数据中没有的结果。那是：

team1     team2     score
  6         3         10

将转换为：

team1     team2     score
  3         6        10

该对不在原始数据中的位置。

如果这是不可取的，您可以使用：

select team1, team2, sum(score)
from ((select team1, team2, score
       from t
       where team1 <= team2 
      ) union all
      (select team2, team1 score
       from t
       where team2 < team1 and
             not exists (select 1 from t t2 where t2.team1 = t.team2 and t2.team2 = t.team1
                        )
      )
     ) t
group by team1, team2;