分类汇总

查询需求

查询用户 指定月 各 类型的支出 总额

如下表结构

CREATE TABLE `sxy_consume_books` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`user_id` BIGINT(20) NOT NULL COMMENT '用户ID',
	`type` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '1支出，2收入，4不计入收支',
	`type_id` BIGINT(20) NOT NULL COMMENT '类型ID',
	`channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
	`money` BIGINT(20) UNSIGNED NOT NULL COMMENT '金额,单位分',
	`date` DATE NOT NULL COMMENT '消费日期',
	`year` YEAR NOT NULL DEFAULT '2023' COMMENT '年',
	`month` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '月',
	`comment` TEXT NULL DEFAULT NULL COMMENT '备注' COLLATE 'utf8mb4_unicode_ci',
	`created_at` TIMESTAMP NULL DEFAULT NULL,
	`updated_at` TIMESTAMP NULL DEFAULT NULL,
	`deleted_at` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `sxy_consume_books_user_id_index` (`user_id`) USING BTREE,
	INDEX `date` (`date`) USING BTREE,
	INDEX `sxy_consume_books_year_month_index` (`year`, `month`) USING BTREE
)
COMMENT='记账本明细'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=7
;

如下数据

INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (1, 1, 1, 1, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:31:47', '2023-04-17 16:31:47', NULL);
INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (2, 1, 1, 1, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:46:37', '2023-04-17 16:46:37', NULL);
INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (3, 1, 1, 2, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:46:39', '2023-04-17 16:46:39', NULL);
INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (4, 1, 1, 2, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:46:40', '2023-04-17 16:46:40', NULL);
INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (5, 1, 1, 3, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:46:41', '2023-04-17 16:46:41', NULL);
INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (6, 1, 1, 4, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:48:10', '2023-04-17 16:48:10', NULL);

查询1

SELECT type_id,SUM(money) AS '总消费'
FROM sxy_consume_books
WHERE user_id = 1 AND `type` = 1 AND `year` = 2023 AND `month` =  4
GROUP BY type_id
WITH ROLLUP;

结果1