mysql - 计算数据包含在多个列中的列的总和

Question

我有一场辩论赛，我想找出哪支球队赢得和输掉的辩论次数最多。我遇到的问题是参加辩论的两个团队的 ID 在两个不同的列中(hostid、visitid)。

到目前为止，我有下面的内容，它给了我我想要的，但它只显示了 visitid 数据。

    CREATE TABLE teams (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255)
    ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

    CREATE TABLE debates (
        debateid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        debatedate DATE NOT NULL,
        hostid INT,
        visitid INT,
        winnerid INT
    ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

    INSERT INTO teams (id, name) VALUES
    (1,'team one'),
    (2,'team two'),
    (3,'team three'),
    (4,'team four'),
    (5,'team five'),
    (6,'team six');

    INSERT INTO debates (debateid, debatedate,hostid, visitid, winnerid ) VALUES
    (1,'2012-01-11', 1,2,1),
    (2,'2012-01-11', 3,4,4),
    (3,'2012-02-11', 5,6,5),
    (4,'2012-02-11', 1,4,1),
    (5,'2012-02-11', 2,5,5),
    (6,'2012-02-11', 3,6,3),
    (7,'2012-03-11', 6,1,1),
    (8,'2012-03-11', 5,2,5),
    (9,'2012-03-11', 3,4,4);

SELECT
    visitid AS id,
    t.name AS name,
    sum(visitid= deb.winnerid) as w,
    sum(visitid != deb.winnerid) as l
FROM debates AS deb
JOIN teams t ON t.id = deb.visitid
WHERE visitid != -1
AND debatedate < CURDATE( )
GROUP BY id
ORDER BY w DESC

结果

    -----------------------------------------
    |   ID  |   NAME        |   W   |   L   |
    |   4   |   team four   |   2   |   1   |
    |   5   |   team five   |   1   |   0   |
    |   1   |   team one    |   1   |   0   |
    |   6   |   team six    |   0   |   2   |
    |   2   |   team two    |   0   |   2   |
    -----------------------------------------

我如何合并这两列，我知道联合，但我想不出在这种情况下实现它的方法或我应该使用什么方法？

如果我让它按预期工作，结果将如下所示，其中 hostid 或 visitid = winnerid

    -----------------------------------------
    |   ID  |   NAME        |   W   |   L   |
    |   1   |   team one    |   3   |   0   |
    |   5   |   team five   |   3   |   0   |       
    |   4   |   team four   |   2   |   1   |
    |   3   |   team three  |   1   |   2   |
    |   2   |   team two    |   0   |   3   |
    |   6   |   team six    |   0   |   3   |
    -----------------------------------------

See fiidle for example

Answer 1

SELECT   t.id,
         t.name,
         SUM(t.id  = d.winnerid) AS w,
         SUM(t.id != d.winnerid) AS l
FROM     debates AS d
    JOIN teams   AS t ON t.id IN (d.hostid, d.visitid)
WHERE    d.visitid != -1  -- not sure what purpose this serves
     AND d.debatedate < CURDATE()
GROUP BY t.id
ORDER BY w DESC

查看 sqlfiddle .