php - 如果 'OR' 条件都为真，则返回同一行两次

Question

我正在尝试运行 SQL 查询以显示比赛中一组战斗的结果。
战斗详细信息存储在战斗机 1、战斗机 2 和获胜者字段中。
我需要做的是将所有战斗的列表放在一起，每个战斗在列表中出现两次 - 例如，一次

Competitor:fighter1, Opponent:fighter2, Winner:fighter1

而且，在列表中的其他地方

Competitor:fighter2, Opponent:fighter1, Winner:fighter1

我一直在使用 sql union 从每次战斗中提取详细信息两次，并尝试为 fighter1 和 fighter2 列分配不同的别名以交换它们，但我什至不确定这是否可能。

我的查询是这样的(省略了其他表中不必要的内容)

SELECT wp_ema_fights.fighter1 AS competitor,
wp_ema_fights.fighter2 AS opponent, 
wp_ema_fights.winner AS winner
FROM wp_ema_fights
UNION ALL
SELECT wp_ema_fights.fighter1 AS opponent,
wp_ema_fights.fighter2 AS competitor,   
wp_ema_fights.winner AS winner
FROM wp_ema_fights
ORDER BY competitor

此查询显示每场战斗两次，但如下所示:

Competitor:fighter1, Opponent:fighter2, Winner:fighter1
Competitor:fighter1, Opponent:fighter2, Winner:fighter1

有没有人知道我如何交换两者并根据每次被指定为“竞争对手”的战斗机来排序列表？

提前致谢。

Answer 1

尝试:

SELECT   wp_ema_fights.fighter1 AS competitor,
         wp_ema_fights.fighter2 AS opponent,
         wp_ema_fights.winner AS winner
FROM     wp_ema_fights
UNION ALL
SELECT   wp_ema_fights.fighter2 AS competitor,
         wp_ema_fights.fighter1 AS opponent,
         wp_ema_fights.winner AS winner
FROM     wp_ema_fights
ORDER BY competitor