计数查询没有正确分组

Question

我有一些问题搞清楚什么似乎很简单，但它躲过了我。任何帮助深表感谢。

CREATE TABLE IF NOT EXISTS `match_history` (
    `id` int(11) NOT NULL auto_increment, 
    `match_id` int(11) NOT NULL, 
    `team_id` int(11) NOT NULL, 
    `player_id` int(11) NOT NULL, 
    `map` varchar(150) NOT NULL, 
    `score` int(11) NOT NULL, 
    `outcome` varchar(25) NOT NULL, 
    `notes` longtext NOT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=271 ; 

match_id links to matches.id 
team_id links to matchteams.id 
player_id links to persons.id 

我希望看到的是胜利和每队的损失，但我有问题，因为上面的match_history表将每场比赛，每个队多行。

例如：

INSERT INTO `match_history` (`id`, `match_id`, `team_id`, `player_id`, `map`, `score`, `outcome`, `notes`) VALUES 
(221, 44, 2, 124, 'Village', 1570, 'Win', ''), 
(220, 44, 2, 115, 'Village', 1600, 'Win', ''), 
(219, 44, 2, 92, 'Village', 2740, 'Win', ''), 
(218, 44, 4, 105, 'Village',1000, 'Loss', ''), 
(217, 44, 4, 111, 'Village', 1220, 'Loss', ''), 
(216, 44, 4, 130, 'Village', 1440, 'Loss', ''), 
(215, 44, 4, 122, 'Village', 2160, 'Loss', ''), 
(214, 44, 4, 130, 'Seatown', 1410, 'Loss', ''), 
(213, 44, 4, 122, 'Seatown', 1600, 'Loss', ''), 
(212, 44, 4, 111, 'Seatown', 1790, 'Loss', ''), 
(211, 44, 4, 105, 'Seatown', 1790, 'Loss', ''), 
(210, 44, 2, 113, 'Seatown', 1020, 'Win', ''), 
(209, 44, 2, 124, 'Seatown', 1480, 'Win', ''), 
(207, 44, 2, 115, 'Seatown', 2850, 'Win', ''), 
(208, 44, 2, 92, 'Seatown', 2160, 'Win', ''), 
(222, 44, 2, 113, 'Village', 900, 'Win', ''), 
(223, 45, 1, 123, 'Hardhat', 2970, 'Win', ''), 
(224, 45, 1, 26, 'Hardhat', 2930, 'Win', ''), 
(225, 45, 1, 107, 'Hardhat', 1710, 'Win', ''), 
(226, 45, 3, 101, 'Hardhat', 1530, 'Loss', ''), 
(227, 45, 3, 100, 'Hardhat', 1420, 'Loss', ''), 
(228, 45, 3, 125, 'Hardhat', 1010, 'Loss', ''), 
(229, 45, 1, 107, 'Seatown', 2520, 'Win', ''), 
(230, 45, 1, 123, 'Seatown', 2260, 'Win', ''), 
(231, 45, 1, 26, 'Seatown', 1560, 'Win', ''), 
(232, 45, 3, 101, 'Seatown', 1510, 25, 3, 42, 0.6, 0, 0, 0, 'Loss', ''), 

这是我用的查询，但它的计算每一行的1

select mh.team_id as team_id, COUNT(distinct(mh.match_id)) as matches, 
    count(mh.map) as maps, mh.outcome, SUM(IF(mh.outcome='Win',1,0)) as wins, 
    SUM(IF(mh.outcome='Loss',1,0)) as losses, m.id, mt.name as teamname 
FROM match_history mh, matches m, ladders l, match_teams mt 
WHERE mh.team_id = mt.id and mh.match_id = m.id and 
    m.ladder_id = l.id and l.type = 'internal' 
GROUP by mh.team_id 
ORDER by wins desc 

Answer 1

我不知道，因为还没有在一段时间尝试SQL，但尝试这个.. 让我知道如果它使任何错误

select team_id, count(*) from match_history 
where outcome = 'Win' 
group by team_id 

，如果它工作，然后我们就可以愈合它与L-奥赛斯

编辑： 这种麻烦的事情...... 你最好别用好这个..我已经花了几天时间（当然，至少分钟：P）得到这个 我已经在SQL Server测试它如果需要的话，将它定制为mysql。 祝你好运！ ：d

select aa.tid, aa.win, COALESCE(c.loss, 0) from 
(SELECT distinct a.team_id tid, COALESCE(b.win, 0) win 
    FROM [match_history] a 
    left join 
    (select team_id, count(*) win 
    from [match_history] 
    where outcome = 'Win' 
    group by team_id) b 
    on a.team_id = b.team_id)aa 

    left join 
    (select team_id, count(*) loss 
    from [match_history] 
    where outcome = 'Loss' 
    group by team_id) c 
    on aa.tid = c.team_id 

结果：

tid win Loss 
1 6 0 
2 7 0 
3 0 3 
4 0 8 

Answer 2

MySQL的，不像标准的SQL，允许包括（在SELECT条款）是不是在GROUP BY和不在现场列表列汇总列—请参阅http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html —但您必须小心，否则您可能会得到不可预知的结果。在你的情况，你GROUP荷兰国际集团BYmh.team_id，所以你可以放心地包括依赖于团队-ID（例如，mt.name应该是安全的）列，但你不能选择mh.outcome和m.id，因为这取决于具体比赛。所以，我会删除这些列。

然后，我们需要改变SUM(1-or-0)到COUNT(DISTINCT match-ID-or-NULL)让我们只算不同的比赛编号：

select mh.team_id as team_id, 
     COUNT(distinct(mh.match_id)) as matches, 
     count(mh.map) as maps, 
     COUNT(DISTINCT IF(mh.outcome='Win',m.id,NULL)) as wins, 
     COUNT(DISTINCT IF(mh.outcome='Loss',m.id,NULL)) as losses, 
     mt.name as teamname 
    FROM match_history mh, 
     matches m, 
     ladders l, 
     match_teams mt 
WHERE mh.team_id = mt.id 
    and mh.match_id = m.id 
    and m.ladder_id = l.id 
    and l.type = 'internal' 
GROUP by mh.team_id 
ORDER by wins desc 

应该做你想要什么。