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

posted @ 2017-10-24 14:55  Ficow  阅读(2016)  评论(0编辑  收藏