【Mysql】日期函数 、group_concat函数

业务场景：可视化图表开发，这咋写嘛，捋清思路最后本着先实现在优化的方式，先求出当前月份内每一天新增的用户数
用到了一个group_concat函数得出一个新增数量的字符串 然后再根据平年、润年去区分月份的天数

一、业务场景

二、实现

SELECT group_concat( xx.asset SEPARATOR ',' ) name FROM (	SELECT
	a.click_date,
	IFNULL((SELECT COUNT(de.property_id)	FROM (SELECT * FROM design_digital_asset dto WHERE create_time = (SELECT MIN(create_time) FROM design_digital_asset dtoo WHERE dto.property_id = dtoo.property_id) AND dto.asset_own = '7' ORDER BY create_time DESC)AS de WHERE DATE_FORMAT( de.create_time, '%d' ) = a.click_date AND DATE_FORMAT( de.create_time, '%m' ) = DATE_FORMAT( now(), '%m' ) ), 0 ) AS asset FROM(
	SELECT
		DATE_FORMAT( now(), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 1 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 2 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 3 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 4 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 5 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 6 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 7 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 8 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 9 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 10 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 11 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 12 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 13 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 14 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 15 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 16 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 17 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 18 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 19 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 20 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 21 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 22 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 23 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 24 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 25 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 26 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 27 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 28 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 29 DAY ), '%d' ) AS click_date UNION
	SELECT
		DATE_FORMAT(( now() - INTERVAL 30 DAY ), '%d' ) AS click_date 
	) a
GROUP BY
	a.click_date) as xx

结果：
name
1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

三、注意事项

3.1 日期函数

select DATE_ADD(curdate(),interval -day(curdate())+1 day) -- 获取本月第一天
MySQL----获取当前日期当月第一天，最后一天

3.2 group_concat函数

注意事项
最大值限制GROUP_CONCAT() 是有最大长度限制的，默认值是 1024。当总长度达到 1024 后，后面的记录就被截断掉。可以通过 group_concat_max_len 参数进行动态设置。参数范围可以是 Global 或 Session

类型如果group_concat_max_len 的值被设置为小等于 512，那么 GROUP_CONCAT 的返回值类型是 VARCHAR 或 VARBINARY；否则是 TEXT 或 BLOB。实际上，group_concat_max_len 的值可以设置非常大，但会受到参数max_allowed_packet 的限制。

mysql高级函数——GROUP_CONCAT
group_concat用法详解

四、无关推荐

SQL中UPDATE更新语句、REPLACE（）替换函数

update in 的优化写法