sql - Mysql多对多计数和数学

Question

我的 mysql 数据库中设置了多对多表。团队可以参加多场比赛，每场比赛有 2 支球队。它们之间有一个名为 teams_games 的表。

我要做的是为每个团队创建统计数据。理想的打印输出是:

team_id, team_name, wins, losses, draws, error

我的问题是将主队或客队的数学联系起来，如果他们赢了，然后计算这些。然后我必须将这些与每支球队客场获胜的次数相加。然后最后将所有内容结合在一起。我当前的查询结构(不能正常工作)与创建表信息一起在下方。有什么想法吗？


SELECT t.*, COUNT(g_wins.home_score > g_wins.away_score) AS wins,
COUNT(g_wins.home_score < g_wins.away_score) AS losses
FROM teams as t
JOIN teams_games AS t_g_wins ON t_g_wins.tid = t.tid
JOIN games AS g_wins on t_g_wins.gid = g_wins.gid"






Table Create Table



<pre><code> teams CREATE TABLE `teams` (
`tid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(60) NOT NULL,
`league` varchar(2) NOT NULL,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`tid`)
</code></pre>



)



CREATE TABLE <code>teams_games</code> (



<pre><code>`tid` int(10) unsigned NOT NULL,
`gid` int(10) unsigned NOT NULL,
`homeoraway` tinyint(1) NOT NULL,
PRIMARY KEY (`tid`,`gid`),
KEY `gid` (`gid`),
CONSTRAINT `teams_games_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teams` (`tid`),
CONSTRAINT `teams_games_ibfk_2` FOREIGN KEY (`gid`) REFERENCES `games` (`gid`)
</code></pre>



)



CREATE TABLE <code>games</code> (



<pre><code>`gid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`location` varchar(60) NOT NULL,
`time` datetime NOT NULL,
`description` varchar(400) NOT NULL,
`error` smallint(2) NOT NULL,
`home_score` smallint(2) DEFAULT NULL,
`away_score` smallint(2) DEFAULT NULL,
PRIMARY KEY (`gid`)
</code></pre>



)


Answer 1

你让事情复杂化了:这应该是一个 2-n 而不是 n-m 的关系。删除 teams_games 表并为 games 表创建 2 个字段，例如:home_tid 和 away_tid

编辑:然后查询将是类似的东西:

select t.tid, t.name, 
       sum(g.home_score < g.away_score xor t.tid = g.home_tid) wins, 
       sum(g.home_score > g.away_score xor t.tid = g.home_tid) losses, 
       sum(g.home_score = g.away_score) draws
  from games g
       join teams t on t.tid = g.home_tid or t.tid = g.away_tid
 group by t.tid

所以答案是使用 sum，否则它会计算输出中的真值和假值，即连接产生的总行数。

EDIT2:

select t.tid, t.name, 
       sum(g.home_score < g.away_score xor tg.homeoraway) wins, 
       sum(g.home_score > g.away_score xor tg.homeoraway) losses, 
       sum(g.home_score = g.away_score) draws
  from games g
  join team_games tg on tg.gid = g.gid
  join teams t on t.tid = tg.tid
 group by t.tid