mysql 同一表和组的多个外键

up9lanfz  于 2023-03-28  发布在  Mysql
关注(0)|答案(2)|浏览(130)

我需要选择指定比赛中的所有比赛,并按对其进行分组。
问题是在matches中我有2个foreign key toteams
1.主机标识
1.访客标识
要选择两个团队,我选择竞赛并加入:
加入球队主机上matches.host_id =host.id加入球队客人上比赛。guest_id =guest.id
但是我有一个问题,如何将他们分组,以便将不同球队比赛的所有数据相加。而不是将东道主和客队的数据相乘。
因为如果我将他们分组的host.name和guest.name我将得到SUM的Team1像主机和SUM的Team1像客人分开。

e5nqia27

e5nqia271#

创建一个子选择来收集你想要的聚合,然后联接回你的主语句。

(rest of your joins)
join
(select host.id, sum(score) totalscore,avg(gametime) average_gametime,sum(whateverelse)
from host
group by host.ID) a
on host.id = a.id

现在可以在select子句中调用.totalscore、.average_gametime或其他任何内容

mqkwyuun

mqkwyuun2#

你似乎想要一支球队在主场和作客时的比赛信息:

select t.id, count(*), sum(...), ...
from teams t join matches m
where t.id = m.home or t.id=m.guest
group by (t.id)

除了球队id之外,你的比赛信息现在也可以分开为主场和客场。例如,匹配home_score和away_score列。然后你需要使用“或”来获取该信息:

select t.id,
    case when when t.id = m.home then "home" when t.id = m.guest then "guest" end case) as role,
    sum(*) as games_played,
    sum(case when t.id = m.home then m.home_score when t.id = m.guest then m.home_guest end case) as total_score,
    case when t.id = m.home then m.home_penalties when t.id = m.guest then m.home_penalties end case) as total_penalties,
    ...

这可以用更少的case表达式来写,但可能更慢:

select id, role, count(*), sum(score), sum(penalty)
from teams t
join (select home as team, "home" as role, m.home_score as score, m.home_penalty as penalty, ..., match.* from matches)
    union
    select away as team, "away" as role, m.away_score as score, m.away_penalty as penalty, ..., match.* from matches) m
on t.id = m.team
group by (t.id)

为了避免这些复杂性,您可以在表中保留一支球队是主场还是客场(角色)以及每场比赛的结果,并在另一个表中仅保留一场比赛的球队配对,然后将其home_/away_列定义为它们的视图。

相关问题