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