mysql - MySQL 的解码函数

Question

我有下面的sql:

SELECT     DATE_FORMAT(date_created, '%Y-%m-%d')   AS date_    , IF(STATUS=1,'Success','Failed')       AS PROCESSED_STATUS    ,COUNT(1)FROM     xWHERE     DATE_FORMAT(date_created, '%Y-%m-%d') BETWEEN  '2011-01-01' AND '2015-06-04'GROUP BY     DATE_FORMAT(date_created, '%Y-%m-%d')    , STATUSORDER BY     DATE_FORMAT(date_created, '%Y-%m-%d') DESC;

实际上格式如下所示

Date_       PROCESSED_STATUS    Count(1)2015-05-15  Failed              12015-05-13  Failed              6912015-05-13  Success             682012-05-19  Failed              3462012-05-19  Success             282012-05-18  Failed              1842012-05-18  Success             182012-05-17  Failed              1762012-05-17  Success             92012-05-16  Failed              4252012-05-16  Success             492012-03-13  Failed              12012-02-23  Success             193

但我需要这种格式:

Date_   Failed    Success

数据内部将显示日期以及失败和成功的计数。

通常在oracle上，我使用decode函数来实现这一点，但我不知道在mysql上。请帮忙:)

Answer 1

使用条件聚合:

SELECT DATE_FORMAT(date_created, '%Y-%m-%d') AS date_, 
       SUM(STATUS = 1) as Success,
       SUM(STATUS <> 1 OR STATUS IS NULL) as Failed
FROM x
WHERE DATE_FORMAT(date_created, '%Y-%m-%d') BETWEEN  '2011-01-01' AND '2015-06-04'
GROUP BY DATE_FORMAT(date_created, '%Y-%m-%d')
ORDER BY DATE_FORMAT(date_created, '%Y-%m-%d') DESC;