如何在MySQL中获取同一列的两个不同值

Question

另一个表match_master存储正在播放的所有比赛：

match_master:match_id,team1_id,team2_id

{match_id}_match_main的Desc是:player_id,team_id,goal,elegel,red。

select B.Name as home_player_name,B1.Name as away_player_name 
from {match_id}_match_main A 
inner join users B on A.player_id=B.player_id,
users B1
inner join {match_id}_match_main A1 on A1.player_id=B1.player_id
where A.team_id='t1' and A1.team_id='t2';

但是这个查询不提供不同的值，而是提供重复的值。

Answer 1

我想这个查询会给你想要的结果。它从match_main中查找不同的team_id值，然后再次将s连接到match_main两次，以获得每个队的球员列表以及行号，行号用于确保我们不会重复球员对：

SELECT u1.player_name, u2.player_name
FROM (SELECT MIN(team_id) AS team_1, MAX(team_id) AS team_2 FROM match_main) teams
JOIN (SELECT *, @rn1 := @rn1 + 1 AS rownumber
      FROM match_main
      CROSS JOIN (SELECT @rn1 := 0) r
      ORDER BY team_id ASC) m1 ON m1.team_id = teams.team_1
JOIN (SELECT *, @rn2 := @rn2 + 1 AS rownumber
      FROM match_main
      CROSS JOIN (SELECT @rn2 := 0) r
      ORDER BY team_id DESC) m2 ON m2.team_id = teams.team_2 AND m2.rownumber = m1.rownumber
JOIN users u1 ON u1.player_id = m1.player_id
JOIN users u2 ON u2.player_id = m2.player_id

我在dbfiddle上创建了一个小演示，这样您就可以在工作中看到它，并使用它。

第一个派生表：

SELECT MIN(team_id) AS team_1, MAX(team_id) AS team_2 FROM match_main

在每场比赛中找到两个独立的队。第二和第三个派生表：

SELECT *, @rn1 := @rn1 + 1 AS rownumber
FROM match_main
CROSS JOIN (SELECT @rn1 := 0) r
ORDER BY team_id ASC

本质上是一样的。它们为match_main表中的每一行生成行号，按照team_id升序或降序排列，以使每个队的球员位于表的上半部分。m2.rownumber=m1.rownumber确保我们在最终结果中获得每个队的球员。最后两个join到users的连接使我们能够从该表中获得播放器名称。

SELECT u1.player_name, u2.player_name
FROM match_master teams
JOIN (SELECT *, @rn1 := @rn1 + 1 AS rownumber
      FROM {match_id}_match_main
      CROSS JOIN (SELECT @rn1 := 0) r
      ORDER BY team_id ASC) m1 ON m1.team_id = teams.team1_id
JOIN (SELECT *, @rn2 := @rn2 + 1 AS rownumber
      FROM {match_id}_match_main
      CROSS JOIN (SELECT @rn2 := 0) r
      ORDER BY team_id DESC) m2 ON m2.team_id = teams.team2_id AND m2.rownumber = m1.rownumber
JOIN users u1 ON u1.player_id = m1.player_id
JOIN users u2 ON u2.player_id = m2.player_id
WHERE teams.match_id = {match_id}