查询指定时间段内的数据并转换为json

向大佬看齐·

已于 2022-04-04 14:36:27 修改

阅读量798

点赞数

文章标签：数据库

于 2022-04-04 10:33:27 首次发布

本文链接：https://blog.csdn.net/xiaommxhxxl/article/details/123949794

版权

笔记

数据库字段

CREATE TABLE `IdentifyingInformation`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `label` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '识别标签',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `price` float(30, 4) NULL DEFAULT NULL COMMENT '价格',
  `total` float(30, 4) NULL DEFAULT NULL COMMENT '总价',
  `date` timestamp(0) NULL DEFAULT NULL COMMENT '时间',
  `user` int(11) NULL DEFAULT NULL COMMENT '用户',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `userid`(`user`) USING BTREE,
  CONSTRAINT `userid` FOREIGN KEY (`user`) REFERENCES `user_db` (`userId`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 241 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

1、查找指定时间段内的数据并统计数据出现的次数

SELECT `label`, `name`, COUNT(*) AS `count` FROM `identifyinginformation` WHERE `date` >= '2022-03-27 00:00:00' 
AND `date` <= '2022-04-03 00:00:00' 
GROUP BY
	`label`,
	`name`;

在这里插入图片描述
2、将查询结果转为json格式

SELECT CONCAT( JSON_OBJECT( 'label', `label`, 'name', `name`, 'count', COUNT(*))) FROM `identifyinginformation` WHERE `date` >= '2022-03-27 00:00:00' 
AND `date` <= '2022-04-03 00:00:00' 
GROUP BY
	`label`,
	`name`;

在这里插入图片描述
3、将查询结果转为json数组

SELECT CONCAT( '[', GROUP_CONCAT( JSON_OBJECT( 'label', `label`, 'name', `name` )), ']' ) FROM `identifyinginformation` WHERE `date` >= '2022-03-27 00:00:00' 
AND `date` <= '2022-04-03 00:00:00' 
GROUP BY
	`label`,
	`name`;

在这里插入图片描述

3、统计指定时间段内的某一列的总和

SELECT SUM( `price` ) AS sumPrcie FROM `identifyinginformation` WHERE `date` >= '2022-03-27 16:16:00' 
AND `date` <= '2022-04-03 16:16:00';

在这里插入图片描述

将查询到的数据转为json，并指定小数点位数

SELECT JSON_OBJECT('sumprice',TRUNCATE(SUM(`price`),2)) FROM `identifyinginformation` WHERE `date` >= '2022-03-27 16:16:00' 
AND `date` <= '2022-04-03 16:16:00';

在这里插入图片描述

4、jpa查询的时候将查询到的结果直接保存为键值对的形式，将查询结果保存到map中即可

Map<String,String>

    @Modifying
    @Query(value = "SELECT `label`,`name`,COUNT(*) AS `count`FROM `identifyinginformation` " +
            "WHERE`date`>=? AND `date` <=? GROUP BY `label`,`name`;", nativeQuery = true)
    List<Map<String,String>> findAllLabel(String now, String todayOfLastWeek);