根据表中的时间戳字段统计每天数据的数量

参考：https://my.oschina.net/bobwei/blog/1842705
https://blog.csdn.net/qq_39370658/article/details/88706890
今天遇到一个需求是：导出表中每天数据的个数
这个是表：

这个是sql：

SELECT DATE_FORMAT(a.operation_time,'%Y-%m-%d') AS TIME , COUNT(*) AS COUNT
FROM (SELECT * FROM dvsp_data_search_log WHERE operation_time BETWEEN '2019-08-01' AND '2019-11-01' AND channel = 'wx') a
GROUP BY  time

这样查出来是想要的数据，但是又有一个问题是：
怎么设置那一天没有数据的时候为0？https://blog.csdn.net/weixin_34349320/article/details/92075115
这里还有一个7天补全的思路：
https://blog.csdn.net/ljxbbss/article/details/78028424
还有一种是辅助表的形式：https://blog.csdn.net/luckly_p/article/details/91044564

SELECT
	t2.all_day AS `日期`,  
	
	COUNT(operation_time) AS `访问次数`
FROM
	(
		SELECT
			@rownum :=@rownum + 1 AS NO,
			DATE_ADD(
				'2019-05-14',
				INTERVAL @rownum DAY
			) AS all_day
		FROM
			(SELECT @rownum := -1) r_init,
			dvsp_data_search_log
	) t2
LEFT JOIN dvsp_data_search_log ON (
	t2.all_day = DATE(dvsp_data_search_log.operation_time)
)
WHERE
	t2.all_day >= '2019-05-14'
AND t2.all_day <= '2019-06-14'
GROUP BY
	t2.all_day;

https://blog.csdn.net/qq_41669724/article/details/80653073
如果说写sql的话在正式的项目中且不说这个sql不好写，单纯是在mapper文件里面都不好写。所以碰到类似的问题是建议在代码中实现。
下面是通过上面链接的配置类做的更改

 public static void exporTwotExcel(HSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers1, String[] headers2, ArrayList<ArrayList<String>> result1, ArrayList<ArrayList<String>> result2) throws Exception {

        //生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        workbook.setActiveSheet(sheetNum);
        workbook.setSheetName(sheetNum,sheetTitle);
        //设置表格列宽为20字节
        sheet.setDefaultColumnWidth((short) 20);
        //生成一个样式 并且创建一个居中格式2
        HSSFCellStyle style =workbook.createCellStyle();
        //设置单元格样式
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers1.length; i++) {
            HSSFCell cell = row.createCell((short) i);

            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers1[i]);
            cell.setCellValue(text.toString());
        }
        // 遍历集合数据，产生数据行
        if (result1 != null) {
            int index = 1;
            for (List<String> m : result1) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                HSSFCell cell = row.createCell((short) cellIndex);
                cell.setCellValue(index);
                cellIndex++;
                for (String str : m) {
                    HSSFCell cell1 = row.createCell((short) cellIndex);
                    cell1.setCellValue(str.toString());
                    cellIndex++;
                }
                index++;
            }
        }

        // 产生表格标题行
        row = sheet.createRow(result1.size()+3);
        for (int i = 0; i < headers2.length; i++) {
            HSSFCell cell = row.createCell((short) i);

            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers2[i]);
            cell.setCellValue(text.toString());
        }
        // 遍历集合数据，产生数据行
        if (result2 != null) {
            int index = result1.size()+4;
            for (List<String> m : result2) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                HSSFCell cell = row.createCell((short) cellIndex);
                cell.setCellValue(index-result1.size()-3);
                cellIndex++;
                for (String str : m) {
                    HSSFCell cell1 = row.createCell((short) cellIndex);
                    cell1.setCellValue(str.toString());
                    cellIndex++;
                }
                index++;
            }
        }
    }


    public static void main(String[] args) {
        String[] head1= {"序号","华盛顿","鞍山市刘德华"};
        ArrayList<ArrayList<String>> arrayLists1 = new ArrayList<>();
        for (int i = 0; i <7; i++) {
            ArrayList<String> strings = new ArrayList<>();
            strings.add("22");
            strings.add("32432");
            arrayLists1.add(strings);
        }
        String[] head2= {"序号","华盛顿111111111","鞍山市刘德华1"};
        ArrayList<ArrayList<String>> arrayLists2 = new ArrayList<>();
        for (int i = 0; i < 4; i++) {
            ArrayList<String> strings = new ArrayList<>();
            strings.add("12");
            strings.add("3223");
            arrayLists2.add(strings);
        }
        HSSFWorkbook sheets = new HSSFWorkbook();
        try {
            exporTwotExcel(sheets,0,"haha",head1,head2,arrayLists1,arrayLists2);
        } catch (Exception e) {
            e.printStackTrace();
        }

        OutputStream outputStream = null;
        try {
            File file = new File("d:text.xls");//可能会抛异常：NullPointerException
            outputStream = new FileOutputStream(file);//1.打开资源：输出文件流；2.可能会抛异常：FileNotFoundException
            /* 关于 HSSFWorkbook.write(OutputStream stream) throws IOException {}
             | 方法的原文注释如下：
             |   Method write - write out this workbook to an Outputstream.  Constructs
             |   a new POI POIFSFileSystem, passes in the workbook binary representation  and
             |   writes it out.
             |   @param stream - the java OutputStream you wish to write the XLS to
             |   @exception IOException if anything can't be written.
             */
            //write会自动新建一个xls模板，然后把数据以二进制的形式写到里面，然后再写到输出流中
            sheets.write(outputStream);//可能会抛异常：IOException
        } catch (IOException e) {
            System.out.println(e.getMessage());//异常要处理给人看，要么log，要么...
        }




    }