- 实例 -
在这个表里进行查询：
查询出如下结果（统计每天的输赢次数）：
- 开始查询 -
首先创建测试表：
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