Mysql groupby + casewhen 统计总数

创建订单表

CREATE TABLE `orders_normal_orders` (
  `order_id` bigint(20) NOT NULL COMMENT '订单号',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `total_fee` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '订单金额，以分为单位',
  `order_status` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '订单状态。',
  `pay_status` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NOTPAY' COMMENT '支付状态',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

订单商品表

CREATE TABLE `orders_normal_orders_items` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `order_id` bigint(20) NOT NULL COMMENT '订单号',
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `spu` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '商品spu',
  `sku` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '商品编码',
  `num` int(10) unsigned NOT NULL COMMENT '购买商品数量',
  `price` int(10) unsigned NOT NULL COMMENT '单价，以分为单位',
  `total_fee` int(10) unsigned NOT NULL COMMENT '订单金额，以分为单位',
  PRIMARY KEY (`id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

业务场景：需要实时统计指定时间段“商品销售额、销售数量（需要取消订单、正常订单）”

使用groupby + casewhen来统计，取消订单为单独计算

SELECT
  i.spu,
    sum(case when o.order_status='CANCEL' then i.num else 0 end) AS cancel_order_num,
	sum(case when o.order_status='CANCEL' then i.total_fee+i.deposit_fee else 0 end) AS cancel_order_items_total_fee,
	sum(case when o.order_status<>'CANCEL' then i.num else 0 end) AS order_num,
	sum(case when o.order_status<>'CANCEL' then i.total_fee+i.deposit_fee else 0 end) AS order_items_total_fee
FROM
	orders_normal_orders_items i
	LEFT JOIN orders_normal_orders o ON i.order_id = o.order_id 
WHERE
	( i.create_time >= '1729078200' ) 
	AND ( i.create_time <= '1729267199' ) 
	AND ( o.pay_status = 'PAYED' ) 
GROUP BY
	i.spu