mysql - 如何查询超过2列的计数

我正在尝试编写一个SQL查询，该查询显示两个团队互相对抗的频率。

Id | Team1 | Team2 | Date
1  |   A   |   B   | 25/5/11
2  |   B   |   A   | 26/5/11
3  |   A   |   C   | 27/5/11
4  |   C   |   B   | 28/5/11
5  |   A   |   B   | 28/5/11

结果应该是:

A vs B => 3
A vs C => 1
C vs B => 1

将A-B和B-A区别为一个简单的查询。但是我无法将它们放在一起计算。

有什么建议么？

最佳答案

SELECT Team1, Team2, SUM(num) FROM (
   SELECT Team1, Team2, COUNT(*) num
   FROM table_name
   GROUP BY Team1, Team2
  UNION ALL
   SELECT Team2, Team1, COUNT(*) num
   FROM table_name
   GROUP BY Team2, Team1
) combined
WHERE Team1 < Team2
GROUP BY Team1, Team2

编辑:必要时更新为撤消团队。

注意:这将比其他答案中提供的使用CASE的版本运行速度快得多，因为它将充分利用索引。

Edit2:将索引移动到更快的位置。