SQL SUM函数内使用CASE函数

 

- 实例 -

 

在这个表里进行查询：

 

 

查询出如下结果（统计每天的输赢次数）：

 

 

 

 

- 开始查询 -

 

首先创建测试表：

CREATE TABLE info(
    date varchar(255),
    result varchar(255)
);

 

 

插入测试数据：

INSERT INTO info(date,result) VALUES('2015-10-09','win');
INSERT INTO info(date,result) VALUES('2015-10-09','lose');
INSERT INTO info(date,result) VALUES('2015-10-09','win');
INSERT INTO info(date,result) VALUES('2015-10-09','lose');
INSERT INTO info(date,result) VALUES('2015-10-10','win');
INSERT INTO info(date,result) VALUES('2015-10-10','win');
INSERT INTO info(date,result) VALUES('2015-10-10','lose');

 

 

起初用了一种最“简单粗暴”的方式查了出来：

SELECT info.date,winTable.win,loseTable.lose FROM info
LEFT JOIN (SELECT date,COUNT(date) AS win FROM info
             WHERE result = 'win'
             GROUP BY date) winTable
ON winTable.date = info.date
LEFT JOIN (SELECT date,COUNT(date) AS lose FROM info
             WHERE result = 'lose'
             GROUP BY date) loseTable
ON loseTable.date = info.date
GROUP BY info.date,winTable.win,loseTable.lose
ORDER BY info.date;

 这方案绝对可以优化！

 

 

 

 

 

果然，用了SUM函数之后，SQL语句变简单了，效率也大大提高了！

SELECT date,
    　　SUM(case result when 'win' then 1 else 0 end) AS win,
        SUM(case result when 'lose' then 1 else 0 end) AS lose
FROM info
GROUP BY date
ORDER BY date;

 

 

 

 

测试完了，删除测试表：

DROP TABLE info;

 

 

 

参考资料：

http://www.yiibai.com/mysql/case-function.html

http://www.cnblogs.com/yazdao/archive/2009/12/09/1620482.html

http://blog.csdn.net/u012531272/article/details/50295397

 

 

---

 ficow原创，转载请注明出处： http://www.cnblogs.com/ficow/p/7723550.html