Java统计年月日的数据量（使用mysql）

许忆

已于 2022-06-20 16:36:46 修改

阅读量1.7k

点赞数 3

分类专栏： Java MySQL数据库文章标签： java mysql 开发语言

于 2022-06-16 18:01:37 首次发布

本文链接：https://blog.csdn.net/qq_43170312/article/details/125320371

版权

Java 同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

MySQL数据库

4 篇文章 0 订阅

订阅专栏

使用SQL查询近30天/月的日期

##  近30天的日期
SELECT ADDDATE(y.first, x.d) as date
FROM
(SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
SELECT 29 UNION ALL SELECT 30 ) x,
(SELECT DATE_SUB( '2022-06-14' , INTERVAL 30 DAY) AS first,CONCAT('2022-06-14') AS last) y

##  近30个月的日期
SELECT ADDDATE(y.first,INTERVAL + x.d MONTH) as date
FROM
(SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
SELECT 29 UNION ALL SELECT 30 ) x,
(SELECT DATE_SUB( '2022-06-14' , INTERVAL 30 MONTH) AS first,CONCAT('2022-06-14') AS last) y

其中，"2022-06-14"为传入的当前时间，使用mybatis时，直接传入当前的日期参数即可。

mybatis实际应用

SELECT
       A.date AS date,
       IF(B.OPERATION_DATE IS NULL OR B.INNER_GROUP_CNT IS NULL,0,B.INNER_GROUP_CNT) AS innerGroup,
       IF(B.OPERATION_DATE IS NULL OR B.DEPT_GROUP_CNT IS NULL,0,B.DEPT_GROUP_CNT) AS deptGroup
FROM (
    SELECT ADDDATE(y.first, x.d) as date
    FROM
    (SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
    SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
    SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
    SELECT 29 UNION ALL SELECT 30 ) x,
    (
    SELECT DATE_SUB( #{date} , INTERVAL 30 DAY) AS first, <![CDATA[  CONCAT(#{date}) ]]> AS last
    ) y
)   AS A
LEFT JOIN report_group_create_summary AS B ON A.date = B.OPERATION_DATE
GROUP BY date
ORDER BY date

使用@date的方式查询年月日

SQL分析

查询出所有所需时间

SELECT
DATE_FORMAT(@cdate := DATE_ADD( @cdate, INTERVAL + 1 DAY ),'%Y-%m-%d' ) cdate
FROM
(SELECT @cdate := DATE_ADD('2022-05-17', INTERVAL - 1 DAY ) FROM PL_MATERIAL_INFO LIMIT 7 ) t0

1、这里的’2022-05-17’是统计开始时间。
例如，需要查2022-05-29之前12天的数据，这里在上面的serviceImpl中进行了逻辑运算，可以得到最开始的时间是2022-05-17，所以统计开始时间就是2022-05-17。
2、这里的“7”，表示查询的时间节点数是7个。
3、PL_MATERIAL_INFO这是我的项目中用到的数据表，替换为你需要统计的表即可。
注意：如果这张表里的数据量小于要查询的时间节点数，即这里的“7”，那么查询出来的时间节点数只有当前表中的数据量。

mybatis mapper.xml实际应用

<!-- 统计办公物资出入库数量 -->
<select id="statisticImAndOut" resultType="com.jnx.smart.logistics.base.entity.vo.ImAndExVO"
        parameterType="com.jnx.smart.logistics.base.entity.qo.StatisticsQO">
    SELECT
    date.cdate date,IFNULL(IM.imNum,0) imNum,IFNULL(EX.exNum,0) exNum
    FROM (
        SELECT
        DATE_FORMAT(@cdate := DATE_ADD( @cdate, INTERVAL + 1 ${qo.dateType} ),#{qo.dateFormat} ) cdate
        FROM
        (SELECT @cdate := DATE_ADD(#{qo.startDate}, INTERVAL - 1 ${qo.dateType} ) FROM PL_MATERIAL_INFO LIMIT #{qo.step} )
        t0
    ) date
    LEFT JOIN (
        SELECT P1.MATERIAL_ID,COUNT(P1.ID) imNum,DATE_FORMAT(P1.CREATOR_TIME,#{qo.dateFormat}) date FROM
        PL_MATERIAL_IM_INFO P1
        LEFT JOIN PL_MATERIAL_INFO M1 ON M1.MATERIAL_ID = P1.MATERIAL_ID
        <where>
            <if test="qo.materialType != null and qo.materialType != ''">
                AND M1.MATERIAL_TYPE = #{qo.materialType}
            </if>
        </where>
        GROUP BY date
    ) IM ON date.cdate = IM.date
    LEFT JOIN (
        SELECT P2.MATERIAL_ID,COUNT(P2.ID) exNum,DATE_FORMAT(P2.CREATOR_TIME,#{qo.dateFormat}) date FROM
        PL_MATERIAL_EX_INFO P2
        LEFT JOIN PL_MATERIAL_INFO M2 ON M2.MATERIAL_ID = P2.MATERIAL_ID
        <where>
            <if test="qo.materialType != null and qo.materialType != ''">
                AND M2.MATERIAL_TYPE = #{qo.materialType}
            </if>
        </where>
        GROUP BY date
    )EX ON date.cdate = EX.date
    ORDER BY date.cdate
</select>

controller层

@GetMapping("/statistic/imAndOut")
@ApiOperation(value = " 统计出入库数量", httpMethod = HttpMethod.GET)
public List<ImAndExVO> statisticImAndOut(StatisticsQO qo) {
    return baseService.statisticImAndOut(qo);
}

service业务接口

/**
 * 统计办公物资出入库数量
 *
 * @param qo 查询条件
 * @return 出入库统计数量
 */
List<ImAndExVO> statisticImAndOut(StatisticsQO qo);

serviceImpl业务实现类

@Override
public List<ImAndExVO> statisticImAndOut(StatisticsQO qo) {
    // 如果没传时间类型，默认按"日"统计
    if (qo.getDateType() == null || StrUtil.isBlank(qo.getDateType().trim())) {
        qo.setDateType(DateFormatEnum.DAY.getDateType());
    }
    // 如果没设置当前时间，按照系统当前时间开始计算
	if (qo.getCurrentTime() == null || StrUtil.isBlank(qo.getCurrentTime().trim())) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            qo.setCurrentTime(sdf.format(new Date()));
        }
    }
    Integer step = qo.getStep();
    // 如如果展示时间没设置，默认查询7条
    if (step == null) {
        step = 7;
        qo.setStep(step);
    }
    // 获取"当前时间"，即执行计算时间
    final LocalDateTime now = cn.hutool.core.date.DateUtil.parseLocalDateTime(qo.getCurrentTime());
    LocalDateTime start;
    final DateFormatEnum dateType = DateFormatEnum.getByDateType(qo.getDateType());
    switch (dateType) {
        case MONTH:
            start = now.minusMonths(step - 1);
            break;
        case YEAR:
            start = now.minusYears(step - 1);
            break;
        default:
            start = now.minusDays(step - 1);
    }
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    String beginDate = start.format(formatter);
    String endDate = now.format(formatter);
    qo.setStartDate(beginDate).setEndDate(endDate)
            .setDateFormat(DateFormatEnum.getFormatByDateType(qo.getDateType()));
    return baseMapper.statisticImAndOut(qo);
}

mapper层

/**
 * 统计办公物资出入库数量
 *
 * @param qo 查询条件
 * @return 出入库统计数量
 */
List<ImAndExVO> statisticImAndOut(@Param("qo")StatisticsQO qo);

部分使用到的实体

/**
 * 入库出库实体VO
 *
 * @author Joi
 * @date 2022年06月16日 11:36
 */
@Data
@ApiModel(value = "入库出库实体VO")
@Accessors(chain = true)
public class ImAndExVO implements Serializable {

    @ApiModelProperty(value = "入库数量")
    private Integer imNum;

    @ApiModelProperty(value = "出库数量")
    private Integer exNum;

    @ApiModelProperty(value = "时间")
    private String date;

}

/**
 * 统计实体
 *
 * @author Joi
 * @date 2022年06月16日 11:09
 */
@Data
@ApiModel(value = "统计查询条件实体")
@Accessors(chain = true)
public class StatisticsQO implements Serializable {

    @ApiModelProperty(value = "日期类型", allowableValues = "YEAR,MONTH,DAY")
    private String dateType;

    @ApiModelProperty(value = "时间格式")
    private String dateFormat;

    @ApiModelProperty(value = "数据展示条数")
    private Integer step;

    @ApiModelProperty(value = "物资类型编号")
    private String materialType;

    @ApiModelProperty(value = "开始日期,格式为 2022-10-01")
    private String startDate;

    @ApiModelProperty(value = "结束日期,格式为 2022-10-01")
    private String endDate;

}

/**
 * 时间格式枚举类
 *
 * @author Joi
 * @date 2022年06月16日 14:58
 */
public enum DateFormatEnum {

    /**
     * 天
     */
    DAY("%Y-%m-%d", "DAY"),

    /**
     * 月
     */
    MONTH("%Y-%m", "MONTH"),

    /**
     * 年
     */
    YEAR("%Y", "YEAR"),

    ;

    private final String format;

    private final String dateType;

    DateFormatEnum(String format, String dateType) {
        this.format = format;
        this.dateType = dateType;
    }

    public String getFormat() {
        return format;
    }

    public String getDateType() {
        return dateType;
    }

    /**
     * 根据日期类型获取枚举类型
     *
     * @param dateType 日期类型
     * @return 枚举类型
     */
    public static DateFormatEnum getByDateType(String dateType) {
        String finalDateType = dateType.trim().toUpperCase();
        Optional<DateFormatEnum> optional = Arrays.stream(DateFormatEnum.values())
                .filter(item -> finalDateType.equals(item.getDateType()))
                .findFirst();
        if (!optional.isPresent()) {
            throw BusinessException.create(ErrorMessage.PARAM_WRONG, "无效的参数：" + dateType);
        }
        return optional.get();
    }

    /**
     * 根据日期类型获取日期格式
     *
     * @param dateType 日期类型
     * @return 日期格式
     */
    public static String getFormatByDateType(String dateType) {
        String finalDateType = dateType.trim().toUpperCase();
        Optional<DateFormatEnum> optional = Arrays.stream(DateFormatEnum.values())
                .filter(item -> finalDateType.equals(item.getDateType()))
                .findFirst();
        if (!optional.isPresent()) {
            throw BusinessException.create(ErrorMessage.PARAM_WRONG, "无效的参数：" + dateType);
        }
        return optional.get().getFormat();
    }

}