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

我有一个辩论赛，我想知道哪个队赢了，哪个队输了。我现在遇到的问题是，辩论中两个参赛队的身份是在两个不同的栏目中（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   |
    -----------------------------------------

我知道union，但是我想不出在这种情况下实现这一点的方法，或者应该使用什么方法？
如果我让它按预期工作，结果将如下所示：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

最佳答案

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上查看。

关于mysql - 计算多列中包含数据的列的总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19729065/