我有两张{match_id}_match_main桌子和users桌子。match_maintable 存储所有参加比赛的玩家。
我想在两个不同的列中选择在同一张桌子上为两支球队效力的名字球员。
另一个表match_master存储正在播放的所有比赛：
描述match_master：match_id,team1_id,team2_id
描述{match_id}_match_main为：player_id,team_id,goal,yellow,red。
描述users为：player_id,player_name。
我用 join 试过了
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';
但是此查询不提供不同的值，它提供重复的值。
我认为这个查询会给你你想要的结果。它team_id从 中找到不同的值match_main，然后JOINsmatch_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确保我们在最终结果中得到每个团队的球员。最后两个JOINsusers使我们能够从该表中获取玩家姓名。
更新
要使用球队 id 的 from match_master（以确保主客场球员正确排序），您需要使用{match_id}查询中的值（有 3 个位置）；这样的事情应该工作：
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}
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句