mysql不同的类的个数_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