mybatis按时间查询

vagrant_din

已于 2023-06-05 16:16:43 修改

阅读量602

点赞数 1

分类专栏： mybatis 文章标签： java

于 2023-06-05 16:14:21 首次发布

本文链接：https://blog.csdn.net/vagrant_din/article/details/131046540

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

大数据接口中按日期查询sql

测试数据
学生成绩表，测试数据自己添加

CREATE TABLE `subject_mark` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stud_id` int(10) DEFAULT NULL COMMENT '学生id',
  `subject_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '科目',
  `subject_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '科目',
  `mark` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '成绩',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

查询当天，昨天，近7天，近30天数据

<if test='map.timeType != null and map.timeType =="today"'>
     and TO_DAYS(insert_time) = TO_DAYS(NOW())
</if>
<if test='map.timeType != null and map.timeType =="yesterday"'>
      and  TO_DAYS(insert_time) = TO_DAYS(DATE_SUB(NOW(),INTERVAL 1 DAY))
</if>
<if test='map.timeType != null and map.timeType =="sevenDay"'>
      and TO_DAYS(insert_time) >= TO_DAYS(DATE_SUB(NOW(),INTERVAL 7 DAY))
</if>
<if test='map.timeType != null and map.timeType =="fifteenDay"'>
    and TO_DAYS(insert_time) >= TO_DAYS(DATE_SUB(NOW(),INTERVAL 15 DAY))
</if>

近7天每天的数据

SELECT
	d.date,
	ifnull( s.num, 0 ) AS price 
FROM
	(
SELECT
	CURDATE( ) AS date UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 1 DAY ) AS date UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 2 DAY ) AS date UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 3 DAY ) AS date UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 4 DAY ) AS date UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 5 DAY ) AS date UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 6 DAY ) AS date 
	) d
	LEFT JOIN (
SELECT LEFT
	( insert_time, 10 ) AS insert_time,
	count( 1 ) AS num
FROM
	subject_mark AS o 
WHERE
	1 = 1 
GROUP BY
	DATE_FORMAT( insert_time, '%Y-%m-%d' ) 
	) AS s ON d.date = s.insert_time 
ORDER BY
	d.date DESC