mysql - 获得队名，胜利数，出场数

我有这样一个数据库：

CREATE TABLE IF NOT EXISTS `opponents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

INSERT INTO `opponents` (`id`, `name`) VALUES
(1, 'Team 1'),
(2, 'Team 2'),
(3, 'Team 3'),
(4, 'Team 4'),
(5, 'Team 5'),
(6, 'Team 6'),
(7, 'Team 7'),
(8, 'Team 8');

CREATE TABLE IF NOT EXISTS `matches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `winner_id` int(11) NOT NULL,
  `score` varchar(3) NOT NULL,
  `statut` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id` (`game_id`),
  KEY `winner_id` (`winner_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=29 ;

INSERT INTO `matches` (`id`, `game_id`, `winner_id`, `score`, `statut`) VALUES
(1, 2, 8, '2-3', 3),
(2, 2, 0, '', 1),
(3, 2, 0, '', 1);

CREATE TABLE IF NOT EXISTS `odds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `match_id` int(11) NOT NULL,
  `opponent_id` int(11) NOT NULL,
  `value` float NOT NULL,
  PRIMARY KEY (`id`),
  KEY `match_id` (`match_id`),
  KEY `opponent_id` (`opponent_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;


INSERT INTO `odds` (`id`, `datetime`, `match_id`, `opponent_id`, `value`) VALUES
(1, '2013-11-05 16:33:26', 1, 1, 1),
(2, '2013-11-05 16:33:26', 1, 8, 1),
(3, '2013-11-05 16:50:19', 2, 2, 1),
(4, '2013-11-05 16:50:19', 2, 3, 1),
(5, '2013-11-05 16:51:11', 3, 5, 1),
(6, '2013-11-05 16:51:11', 3, 7, 1);

我想获得所有对手的名单，包括他们的胜利数以及他们的总比赛次数。
我对对手和他们的胜利名单没有问题，写了这样一个查询：

SELECT `o`.`id`, `o`.`name`, COUNT(m.id) AS victories FROM (`opponents` o) LEFT JOIN `matches` m ON `m`.`winner_id` = `o`.`id` GROUP BY `o`.`id` ORDER BY `victories` DESC

但是我在检索他们的总比赛数时遇到问题。困难的部分是对手不是直接存储在MATCHES表中，而是存储在ODDS表中。我尝试了这个查询，但是当她只参加了1场比赛（并赢得了比赛）时，我总共返回了1队的7场比赛：

 SELECT `o`.`id`, `o`.`name`, COUNT(m.id) AS victories, COUNT(m2.id) AS played FROM (`estv_opponents` o) LEFT JOIN `estv_matches` m ON `m`.`winner_id` = `o`.`id` LEFT JOIN `estv_odds` ON `estv_odds`.`opponent_id` = `o`.`id` LEFT JOIN `estv_matches` m2 ON `m2`.`id` = `estv_odds`.`match_id` AND m2.statut = 3 GROUP BY `o`.`id`, `estv_odds`.`opponent_id` ORDER BY `victories` DESC

在我的第三个联接中，“ m2.statut = 3”在这里指定比赛已经进行并结束。那就是我想要的，是一场比赛的次数，而不是所有比赛的次数（有些还没有比赛）

任何帮助将不胜感激，我不明白我应该如何建立这个查询。

我的预期输出是一个像这样的数组：

这样的数组：

array(8) {
  [0]=>
  array(4) {
    ["id"]=>
    string(1) "8"
    ["name"]=>
    string(9) "Team #8"
    ["victories"]=>
    string(1) "1"
    ["played"]=>
    string(1) "1"
  }
  [1]=>
  array(4) {
    ["id"]=>
    string(1) "1"
    ["name"]=>
    string(8) "Team #1"
    ["victories"]=>
    string(1) "0"
    ["played"]=>
    string(1) "1"
  }
  [2]=>
  array(4) {
    ["id"]=>
    string(1) "2"
    ["name"]=>
    string(3) "Team #2"
    ["victories"]=>
    string(1) "0"
    ["played"]=>
    string(1) "0"
  }
  [3]=>
  array(4) {
    ["id"]=>
    string(1) "3"
    ["name"]=>
    string(5) "Team #3"
    ["victories"]=>
    string(1) "0"
    ["played"]=>
    string(1) "1"
  }
  [4]=>
  array(4) {
    ["id"]=>
    string(1) "4"
    ["name"]=>
    string(10) "Team #4"
    ["victories"]=>
    string(1) "0"
    ["played"]=>
    string(1) "1"
  }
  [5]=>
  array(4) {
    ["id"]=>
    string(1) "5"
    ["name"]=>
    string(5) "Team #5"
    ["victories"]=>
    string(1) "0"
    ["played"]=>
    string(1) "0"
  }
  [6]=>
  array(4) {
    ["id"]=>
    string(1) "6"
    ["name"]=>
    string(2) "Team #6"
    ["victories"]=>
    string(1) "0"
    ["played"]=>
    string(1) "0"
  }
  [7]=>
  array(4) {
    ["id"]=>
    string(1) "7"
    ["name"]=>
    string(7) "Team #7"
    ["victories"]=>
    string(1) "0"
    ["played"]=>
    string(1) "0"
  }
}

最佳答案

检查以下SQL（如果适用）。

select op.id, op.name, sum(if(o.opponent_id = m.winner_id, 1, 0)) victories, sum(if(m.statut = 3,1,0)) played from opponents as op left join odds o on op.id = o.opponent_id left join matches m on o.match_id = m.id group by op.id order by victories desc;