MySQL分区间进行数据统计

假设当前数据库mc_userdb，其中有customer_login(用户登陆表)

CREATE DATABASE `mc_userdb`;
USE `mc_userdb`;

DROP TABLE IF EXISTS `customer_login`;
CREATE TABLE `customer_login` (
  `customer_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `login_name` VARCHAR(20) NOT NULL COMMENT '用户登陆名',
  `password` CHAR(32) NOT NULL COMMENT 'md5加密的密码',
  `user_stats` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '用户状态',
  `modified_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`customer_id`)
) ENGINE=INNODB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8 COMMENT='用户登陆表';

另外有数据库mc_orderdb，其中有order_master(订单主表)

CREATE DATABASE `mc_orderdb`;
USE `mc_orderdb`;

DROP TABLE IF EXISTS `order_master`;
CREATE TABLE `order_master` (
  `order_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `order_sn` BIGINT(20) UNSIGNED NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn',
  `customer_id` INT(10) UNSIGNED NOT NULL COMMENT '下单人ID',
  `shipping_user` VARCHAR(10) NOT NULL COMMENT '收货人姓名',
  `province` SMALLINT(6) NOT NULL COMMENT '收货人所在省',
  `city` SMALLINT(6) NOT NULL COMMENT '收货人所在市',
  `district` SMALLINT(6) NOT NULL COMMENT '收货人所在区',
  `address` VARCHAR(100) NOT NULL COMMENT '收货人详细地址',
  `payment_method` TINYINT(4) NOT NULL COMMENT '支付方式:1现金,2余额,3网银,4支付宝,5微信',
  `order_money` DECIMAL(8,2) NOT NULL COMMENT '订单金额',
  `district_money` DECIMAL(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额',
  `shipping_money` DECIMAL(8,2) NOT NULL DEFAULT '0.00' COMMENT '运费金额',
  `payment_money` DECIMAL(8,2) NOT NULL DEFAULT '0.00' COMMENT '支付金额',
  `shipping_comp_name` VARCHAR(10) DEFAULT NULL COMMENT '快递公司名称',
  `shipping_sn` VARCHAR(50) DEFAULT NULL COMMENT '快递单号',
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  `shipping_time` DATETIME DEFAULT NULL COMMENT '发货时间',
  `pay_time` DATETIME DEFAULT NULL COMMENT '支付时间',
  `receive_time` DATETIME DEFAULT NULL COMMENT '收货时间',
  `order_status` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
  `order_point` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单积分',
  `invoice_title` VARCHAR(100) DEFAULT NULL COMMENT '发票抬头',
  `modified_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `ux_ordersn` (`order_sn`)
) ENGINE=INNODB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8 COMMENT='订单主表';

 业务场景：统计消费总金额大于1000元的，800到1000元的，500到800元的，以及500元以下的人数

 第一步： 根据customer_id字段进行分组，计算出每位用户的消费总和

SELECT customer_id,SUM(order_money) AS total_money
FROM mc_orderdb.`order_master`
GROUP BY customer_id

 第二步：将customer_login与order_master进行左连接，对左连接的结果集使用count函数计算出各个区间段的数据统计

SELECT COUNT(CASE WHEN IFNULL(total_money,0) >=1000 THEN a.customer_id END) AS '>1000',
      COUNT(CASE WHEN IFNULL(total_money,0) >=800 AND IFNULL(total_money,0) <1000 THEN a.customer_id END) AS '800~1000',
      COUNT(CASE WHEN IFNULL(total_money,0) >=500 AND IFNULL(total_money,0) <800 THEN a.customer_id END) AS '500~800',
      COUNT(CASE WHEN IFNULL(total_money,0) <500  THEN a.customer_id END) AS '<500'
FROM mc_userdb.`customer_login` a
LEFT JOIN
( SELECT customer_id,SUM(order_money) AS total_money
  FROM mc_orderdb.`order_master` GROUP BY customer_id) b
ON a.`customer_id`=b.`customer_id`