mysql - 我如何选择数据库中有两个特定玩家的所有比赛？

Question

情况:

比赛有 10 名球员，每队 5 名。总共有 100 名玩家，每场比赛的十个位置中的任何一个都可以有任何玩家。我需要提取以下给定的两个玩家:

1. 玩家 A 和玩家 B 有多少场比赛？
2. 他们赢了多少？
3. 选手 A 和选手 B 有多少场比赛？
4. 玩家 A 赢了多少个？

我目前的方法(太慢而且可能行不通):

我制作了三个表，“players”、“matches”和“matches_players”，它们将比赛映射到参加比赛的球员。 matches 中的“winning_team”列表示哪支球队获胜(0 = A 队，1 = B 队)，matches_players 表中的“position”列表示球员在阵容中的位置。 0-4 在 A 队，5-9 在 B 队。

---

玩家

---

player_id
43179582
63260623
31250276
54829050
22257854
etc...

---

匹配

---

match_id     winning_team
95824317     0
06236326     0
02763125     1
90505482     0
78544325     1
etc...

---

matches_players

---

relation_id     match_id     player_id     position
1               95824317     43179582      1
2               95824317     63260623      5
3               06236326     43179582      7
4               06236326     54829050      0
5               06236326     22257854      4
etc...

这是我用于查找同一团队英雄的匹配和获胜计数的语句(经过超过 12 小时的冲浪和尝试理解 SQL):

SELECT
COUNT(*) AS match_count,
SUM(CASE WHEN team = winning_team THEN 1 ELSE 0 END) AS win_count
FROM
    (SELECT
    matches.match_id,
    (CASE WHEN position < 5 THEN 0 ELSE 1 END) AS team,
    winning_team
    FROM matches INNER JOIN matches_players ON matches.match_id = matches_players.match_id
    WHERE player_id = [____] OR player_id = [____]
    GROUP BY match_id, team HAVING COUNT(*) = 2)
AS pair_matches

它似乎有效，但速度非常慢(250,000 场比赛超过 90 秒)，理想情况下，我希望在同一查询中获得对立团队的结果(这应该比两个单独的查询快，不是吗？)查询变得更快/更好？是否存在数据库设计缺陷？

我非常感谢任何帮助的人。欢迎任何领域的建议。谢谢。

Answer 1

我认为这应该可以，或者至少给你一个提示

-- How many matches were player A and player B allies?
select DISTINCT (match_id) from MATCHES_PLAYERS where PLAYER_ID='43179582' or PLAYER_ID='63260623' group by MATCH_ID;

-- How many of these did they win?
select  PLAYER_ID, count(WINNING_TEAM)
from 
(select  PLAYER_ID, WINNING_TEAM, POsition from MATCHES_PLAYERS mp, matches m where mp.MATCH_ID=m.MATCH_ID and position<=4 and WINNING_TEAM=0
union all
select PLAYER_ID, WINNING_TEAM, POsition from MATCHES_PLAYERS mp, matches m where mp.MATCH_ID=m.MATCH_ID and position>4 and WINNING_TEAM=1)
group by PLAYER_id;

-- How many matches were player A and player B opponents?
select mp1.*, m.WINNING_TEAM, mp2.PLAYER_ID as opponent from MATCHES_PLAYERS mp1, MATCHES_PLAYERS mp2, matches m where mp1.MATCH_ID=m.MATCH_ID and  mp1.MATCH_ID=mp2.MATCH_ID and mp1.PLAYER_ID!=mp2.PLAYER_ID and (mp1.PLAYER_id in (43179582, 63260623) and mp2.player_id in (43179582, 63260623))
select DISTINCT mp1.MATCH_ID from MATCHES_PLAYERS mp1, MATCHES_PLAYERS mp2, matches m where mp1.MATCH_ID=m.MATCH_ID and  mp1.MATCH_ID=mp2.MATCH_ID and mp1.PLAYER_ID!=mp2.PLAYER_ID and (mp1.PLAYER_id in (43179582, 63260623) and mp2.player_id in (43179582, 63260623))

-- How many of these did player A win?
select
mp1.*, m.WINNING_TEAM, mp2.PLAYER_ID as opponent
from MATCHES_PLAYERS mp1, MATCHES_PLAYERS mp2, matches m
where mp1.MATCH_ID=m.MATCH_ID
and mp1.MATCH_ID=mp2.MATCH_ID
and mp1.PLAYER_ID!=mp2.PLAYER_ID
and
(
   mp1.PLAYER_id=43179582
   and mp2.player_id=63260623
)
and mp1.position<=4
and m.WINNING_TEAM=0