mysql - 如何以无序方式比较 SQL 查询中的字段(可能是数组)？

Question

假设您有一个数据库，其中包含 2 名玩家组成的团队的游戏数据。有一个 team 表，其中包含 player_id 字段和 win 字段(0 表示失败，1 表示获胜)，以及一个 player带有 id 和 weapon 字段的 表。

您想知道 2 名玩家使用的武器组合的团队胜率。

这里重要的是哪个玩家使用哪种武器并不重要。所以我们想以无序的方式比较玩家的武器。

您还可以在团队表中使用 players 数组字段，但我认为这种方法会带来同样的问题。

理想情况下我想写这样的东西:

SELECT AVG(t.win) AS win_fraction, weapon1, weapon2
FROM team t
JOIN player p1 ON t.player_id1 = p1.id
JOIN player p2 ON t.player_id2 = p2.id
GROUP BY UNORDERED(p1.weapon, p2.weapon) AS (weapon1, weapon2)

显然，UNORDERED 关键字不是 SQL 的一部分，但我认为这里的语法足以清楚地表明这是想要的结果:

| win_fraction | weapon1 | weapon2 |
|--------------|---------|---------|
| 0.63         | AK_47   | P90     |
| 0.75         | AK_47   | AK_47   |
| 0.22         | P90     | P90     |

请注意，不应存在将 P90 作为 weapon1 并将 AK_47 作为 weapon2 的行，除非返回与这些武器按相反顺序排列的行完全相同的 win_fraction(因此武器的排序不应影响计算出的 win_fraction)。

有人遇到过同样的问题吗？如果是这样，您是如何解决的？

感谢您的帮助，非常感谢!

编辑:

解决方案需要足够通用才能容纳 X 个玩家。针对 2 名玩家问题情况的特定解决方案(例如使用 LEAST 和 GREATEST)在 3 名玩家问题情况下不起作用，因为我们还需要一个 MIDDLE 关键字:

SELECT AVG(t.win) AS win_fraction
FROM team t
JOIN player p1 ON t.player_id1 = p1.id
JOIN player p2 ON t.player_id2 = p2.id
JOIN player p3 ON t.player_id3 = p3.id
GROUP BY
    LEAST(p1.weapon, p2.weapon, p3.weapon),
    MIDDLE(p1.weapon, p2.weapon, p3.weapon),
    GREATEST(p1.weapon, p2.weapon, p3.weapon);`

Answer 1

您可以使用LEAST和GREATEST标量函数进行聚合:

SELECT AVG(t.win) AS win_fraction
FROM team t
INNER JOIN player p1
    ON t.player_id1 = p1.id
INNER JOIN player p2
    ON t.player_id2 = p2.id
GROUP BY
    LEAST(p1.weapon, p2.weapon),
    GREATEST(p1.weapon, p2.weapon);

请注意，您可能还希望在选择中包含 GROUP BY 子句，具体取决于您期望的输出。

对于按三种武器进行分组，我们可以尝试:

SELECT AVG(t.win) AS win_fraction
FROM team t
INNER JOIN player p1
    ON t.player_id1 = p1.id
INNER JOIN player p2
    ON t.player_id2 = p2.id
GROUP BY
    LEAST(p1.weapon, p2.weapon, p3.weapon),
    CASE WHEN p1.weapon <> LEAST(p1.weapon, p2.weapon, p3.weapon) AND
              p1.weapon <> GREATEST(p1.weapon, p2.weapon, p3.weapon)
         THEN p1.weapon
         WHEN p2.weapon <> LEAST(p1.weapon, p2.weapon, p3.weapon) AND
              p2.weapon <> GREATEST(p1.weapon, p2.weapon, p3.weapon)
         THEN p2.weapon
         ELSE p3.weapon END,
    GREATEST(p1.weapon, p2.weapon, p3.weapon);