本文已参与「新人创作礼」活动，一起开启掘金创作之路。

mysql的最近12个月的分段统计查询：

SELECT
	SUM(
		CASE
		WHEN '2020-09-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2020-09-30 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2020-09',
	SUM(
		CASE
		WHEN '2020-10-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2020-10-31 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2020-10',
	SUM(
		CASE
		WHEN '2020-11-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2020-11-30 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2020-11',
	SUM(
		CASE
		WHEN '2020-12-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2020-12-31 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2020-12',
	SUM(
		CASE
		WHEN '2021-01-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2021-01-31 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2021-01',
	SUM(
		CASE
		WHEN '2021-02-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2021-02-28 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2021-02',
	SUM(
		CASE
		WHEN '2021-03-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2021-03-31 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2021-03',
	SUM(
		CASE
		WHEN '2021-04-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2021-04-30 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2021-04',
	SUM(
		CASE
		WHEN '2021-05-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2021-05-31 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2021-05',
	SUM(
		CASE
		WHEN '2021-06-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2021-06-30 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2021-06',
	SUM(
		CASE
		WHEN '2021-07-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2021-07-31 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2021-07',
	SUM(
		CASE
		WHEN '2021-08-01 00:00:00' <= strJoinPartyTime
		AND strJoinPartyTime <= '2021-08-31 23:59:59' THEN
			1
		ELSE
			0
		END
	) AS '2021-08'
FROM
	household_user
WHERE
	isDeleted = 0
AND isEnable = 0
AND isParty = 1

把每个月份的起始日期以list为mapper条件：

[
 {"date":"2020-01","start":"2020-01 00:00:00","key":"2020-01 23:59:59","key":0},
 {"date":"2020-01","start":"2020-01 00:00:00","key":"2020-01 23:59:59","key":0},
{"date":"2020-01","start":"2020-01 00:00:00","key":"2020-01 23:59:59","key":0}
]

转换mybatis的xml语法：

 SELECT
        <foreach  item="item" collection="months" index="index" separator=","  >
            SUM(
            CASE
            WHEN #{item.start}  &lt;= strJoinPartyTime
            AND strJoinPartyTime  &lt;=  #{item.end} THEN
            1
            ELSE
            0
            END
            ) AS #{item.date}
        </foreach>

        FROM
        household_user
        WHERE
        isDeleted = 0
        AND isEnable = 0
        AND isParty = 1

java把图片压缩成base64 支持网络图片

    @RequestMapping(value="/test",method = {RequestMethod. POST ,RequestMethod. GET })
    public String test(){
  
        url = "D:\\yishi.png";
        url="http://127.0.0.1:8123/public/themes/admin_simpleboot3/public/assets/images/bg_esint.png";
        String str = ImageToBase64ByLocal(url);
        System.out.println(str);
        return null;
    }


    // 将图片文件转化为字节数组字符串，并对其进行Base64编码处理
    public static String ImageToBase64ByLocal(String imgFile) {
        InputStream in = null;
        byte[] data = null;
        // 读取图片字节数组
        try {
            URL url = new URL(imgFile);
            in = new BufferedInputStream(url.openStream());

            data = new byte[in.available()];
            in.read(data);
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 对字节数组Base64编码
        BASE64Encoder encoder = new BASE64Encoder();
        return encoder.encode(data);// 返回Base64编码过的字节数组字符串
    }