快捷導(dǎo)航

MySql如何按照日期進(jìn)行分組統(tǒng)計(jì)

更新時(shí)間：2023年09月28日 10:07:12 作者：zhao_854093140

這篇文章主要介紹了MySql如何按照日期進(jìn)行分組統(tǒng)計(jì)問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

數(shù)據(jù)庫(kù)

CREATE TABLE `tb_count`  (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '編號(hào)',
  `tiems` datetime(0) NOT NULL COMMENT '日期',
  `number` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手機(jī)號(hào)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

我的sql是這樣的

這一條sql是查詢所有的，可以按照這個(gè)方法進(jìn)行分頁(yè)查詢，查詢所有

SELECT COUNT(*) AS counts,DATE_FORMAT(tiems,"%Y年%m月%d日") AS dates FROM tb_count  GROUP BY DATE_FORMAT (tiems,"%Y年%m月%d日")
-- DATE_FORMAT();按照格式對(duì)某個(gè)日期操作,
-- tb_count 表名
-- period 日期
-- 整段代碼的意思是：從tb_count表中 查詢?nèi)掌冢╬eriod ）（將日期按照"%Y年%m月"格式）并作為dates，統(tǒng)計(jì)數(shù)量（count(*)）,按照日期分組 GROUP BY DATE_FORMAT(period ,"%Y年%m月")

如果是進(jìn)行分頁(yè)查詢我是做了一個(gè)封裝，將count(數(shù)量)和period(時(shí)間)做了一個(gè)封裝，做了一個(gè)實(shí)體類，技術(shù)有限，只能想到封裝為一個(gè)對(duì)象

封裝對(duì)象

@ApiModel(description = "Statis",value = "Statis")
public class Statis implements Serializable {
    //數(shù)量
    @ApiModelProperty(value = "",required = false)
    private int counts;
    //時(shí)間
    @ApiModelProperty(value = "",required = false)
    private String dates;
    public int getCounts() {
        return counts;
    }
    public void setCounts(int counts) {
        this.counts = counts;
    }
    public String getDates() {
        return dates;
    }
    public void setDates(String dates) {
        this.dates = dates;
    }
}

Service層

  /**
     * 分頁(yè)查詢
     * @param page 當(dāng)前頁(yè)
     * @param size 每頁(yè)顯示的條數(shù)
     * @return
     */
    Result findPage(int page,int size);

ServiceImpl Service層實(shí)現(xiàn)類

我這邊用分頁(yè)插件是PageInfo，需要傳入一個(gè)page和size

  /**
     * 分頁(yè)查詢
     * @param page 當(dāng)前頁(yè)
     * @param size 每頁(yè)顯示的條數(shù)
     * @return
     */
    @Override
    public Result findPage(int page, int size) {
        try {
            //分頁(yè)查詢
            PageHelper.startPage(page, size);
            //查詢所有
            List<Statis> statisList = countsMapper.findAll();
            PageInfo<Statis> pageInfo = new PageInfo<Statis>(statisList);
            return new Result(true,StatusCode.OK,"分頁(yè)查詢成功",pageInfo);
        }catch (Exception e){
            e.printStackTrace();
            return  new Result(false,StatusCode.ERROR,"分頁(yè)查詢失敗");
        }
    }

Controller層

    /**
     * 分頁(yè)查詢
     * @param page 當(dāng)前頁(yè)
     * @param size 每頁(yè)顯示的條數(shù)
     * @return
     * @throws Exception
     */
    @GetMapping("/search/{page}/{size}")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "path", name = "page", value = "當(dāng)前頁(yè)", required = true, dataType = "Integer"),
            @ApiImplicitParam(paramType = "path", name = "size", value = "每頁(yè)顯示條數(shù)", required = true, dataType = "Integer")
    })
    @ApiOperation(value = "日期分頁(yè)查詢",notes = "日期分頁(yè)方法詳情",tags = {"MemberController"})
    public Result findPage(@PathVariable int page,@PathVariable int size) throws Exception {
      return   countsService.findPage(page, size);
    }