mysql语句 一个表统计每天产生的不同类型数据 根据类型字段 统计每天有多少

DROP TABLE IF EXISTS `risk_business02`;
CREATE TABLE `risk_business02` (
  `rb02id` bigint(20) NOT NULL COMMENT '主键',
  `rb01id` bigint(20) DEFAULT NULL COMMENT '风险商户1主键',
  `settleDate` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '日期',
  `createTime` datetime DEFAULT NULL COMMENT '产生日期',
  `b01id` bigint(20) DEFAULT NULL COMMENT '商户ID',
  `reason` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '风险原因(0:夜间交易频繁  1:交易地点分散  2:交易金额固定)',
  `fxpc` int(11) DEFAULT NULL COMMENT '风险批次',
  PRIMARY KEY (`rb02id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ----------------------------
-- Records of risk_business02
-- ----------------------------
INSERT INTO `risk_business02` VALUES ('1', '1', '2017-05-23', '2017-05-24 18:06:10', '1000000108', '1', '0');
INSERT INTO `risk_business02` VALUES ('2', '2', '2017-05-23', '2017-05-24 18:06:11', '1000000109', '0', '0');
INSERT INTO `risk_business02` VALUES ('3', '1', '2017-05-23', '2017-05-24 18:06:11', '1000000108', '0', '0');
INSERT INTO `risk_business02` VALUES ('4', '1', '2017-05-23', '2017-05-24 18:06:10', '1000000108', '2', '0');
INSERT INTO `risk_business02` VALUES ('5', '2', '2017-05-24', '2017-05-24 18:06:11', '1000000109', '2', '0');
INSERT INTO `risk_business02` VALUES ('6', '1', '2017-05-24', '2017-05-24 18:06:11', '1000000108', '1', '0');

先GROUP BY
settleDate,
reason  字段 查出 统计每天 分类原因 个数

然后再GROUP BY
settleDate 一次 再sum 分类原因字段

SELECT
settleDate,
sum(ifnull(r0, 0)) AS r0,
sum(ifnull(r1, 0)) AS r1,
sum(ifnull(r2, 0)) AS r2
FROM
(
SELECT
settleDate,
CASE reason
WHEN 0 THEN
count(reason)
END r0,
CASE reason
WHEN 1 THEN
COUNT(reason)
END r1,
CASE reason
WHEN 2 THEN
COUNT(reason)
END r2
FROM
risk_business02
GROUP BY
settleDate,
reason
)   AS  table_a

WHERE
settleDate >= "2017-05-23"
AND settleDate <= "2017-05-25"
GROUP BY
settleDate
ORDER BY
settleDate DESC
LIMIT 0,
 10