php - MYSQL Order By 2 rows depending on 2 other rows IDS

Question

我正在尝试创建一个查询，允许我选择如下所示的数据按角色 45 组织并按 DESC 顺序显示其胜率。例如 champ_1 = 45 和 champ_2 = 36 但有时它可以是 champ_1 = 36 和 champ_2 = 45。当这样我将如何进行查询以便根据它的 champ1 或 champ2 和然后组织它 desc。

id      Role      Champ_1       Champ_2    Champ_1_winrate  Champ_2_winrate
---|------------|------------|-----------|-----------------|---------------
1  | 1          |     45     |     17    |       0.54      |      0.46      
2  | 1          |     67     |     45    |       0.52      |      0.48      
5  | 1          |     45     |     27    |       0.59      |      0.41     
3  | 1          |     45     |     35    |       0.29      |      0.71     
4  | 1          |     45     |     23    |       0.094     |      0.916     
6  | 1          |     80     |     45    |       0.38      |      0.62     
7  | 1          |     45     |     47    |       0.544     |      0.456     
8  | 1          |     32     |     45    |       0.78      |      0.22     

它应该显示数据:

id      Role      Champ_1       Champ_2       winrate   
---|------------|------------|-----------|-----------------|
6  | 1          |     80     |     45    |       0.62      |      
5  | 1          |     45     |     27    |       0.59      |       
7  | 1          |     45     |     47    |       0.544     |     
1  | 1          |     45     |     17    |       0.54      |            
2  | 1          |     67     |     45    |       0.48      |            
3  | 1          |     45     |     35    |       0.29      |                   
8  | 1          |     32     |     45    |       0.22      |      
4  | 1          |     45     |     23    |       0.094     |      

我已经尝试了以下代码，这是我 3 个多小时以来最接近的代码，所以请有人帮忙。

SELECT * 
FROM Bronze_Matchups a 
WHERE (Champ_2 = 24 AND Role = 'JUNGLE') 
OR (Champ_1 = 24 AND Role = 'JUNGLE') 
ORDER BY Champ_2_winrate desc, Champ_1_winrate asc

Answer 1

您可以使用 UNION 和 ORDER BY(根据需要在 ORDER BY 子句中添加 desc 或 asc)

SELECT a.id, a.Role, a.Champ_1, a.Champ_2, a.Champ_2_winrate as winrate
 FROM Bronze_Matchups a 
 WHERE a.Champ_2 = 24 
 AND a.Role = 'JUNGLE'
UNION
SELECT a.id, a.Role, a.Champ_1, a.Champ_2, a.Champ_1_winrate as winrate
 FROM Bronze_Matchups a 
 WHERE a.Champ_1 = 24
 AND a.Role = 'JUNGLE'
ORDER BY winrate