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.rzrgm.cn/yazdao/archive/2009/12/09/1620482.html

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

ficow原創，轉載請注明出處： http://www.rzrgm.cn/ficow/p/7723550.html

posted @ 2017-10-24 14:55 Ficow 閱讀(4136) 評論(0) 收藏舉報

刷新頁面返回頂部

Ficow Shen

Fly in the code world. Ficow Shen 簡歷鏈

SQL SUM函數內使用CASE函數

公告