MySql查询近一年、近一月、近七天的日期；积分范围统计（行转列）

近一年（截至到当月）

SELECT
	DATE_FORMAT(
	DATE_ADD(DATE_ADD(CURDATE(), INTERVAL - MONTH(CURDATE()) + 2 MONTH),
	INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) MONTH
	), '%Y-%m') `date`
FROM mysql.help_topic
WHERE
	help_topic_id < MONTH ( curdate( ) )
ORDER BY help_topic_id

查询结果：

近一月（截至到当天）

SELECT
	DATE_ADD(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 2 DAY),
	INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY
	) `date`
FROM mysql.help_topic
WHERE
	help_topic_id < DAY ( CURDATE( ) )
ORDER BY help_topic_id

查询结果：（假设当天为2021-06-14）

近七天

方法一：借助help_topic_id

SELECT
	DATE_ADD(DATE_ADD(CURDATE(), INTERVAL - 5 DAY),
	INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY
	) `date`
FROM mysql.help_topic
WHERE
	help_topic_id < 7
ORDER BY help_topic_id

方法2：借助变量@num

SELECT @num := @num-1, date_format(DATE_SUB( CURDATE(), INTERVAL @num DAY),'%Y-%m-%d') `date`
FROM test_log_info, (SELECT @num := 7) t
WHERE @num > 0
ORDER BY `date` ASC

查询结果：

mybatis中结合写

写在一起会不会更乱呢？

SELECT
	<choose>
		-- 1：近7天
	    <when test = "timeType == 1">
	        DATE_FORMAT(
	        DATE_ADD(DATE_ADD(CURDATE(), INTERVAL - 5 DAY),
	        INTERVAL (cast( help_topic_id AS signed ) - 1 ) DAY
	        ), '%Y-%m-%d') `date`
	    </when>
	    -- 2：近1月
	    <when test = "timeType == 2">
	        DATE_FORMAT(
	        DATE_ADD(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 2 DAY),
	        INTERVAL (cast( help_topic_id AS signed ) - 1 ) DAY
	        ), '%Y-%m-%d') `date`
	    </when>
	    <otherwise>
	    	-- 近1年
	        DATE_FORMAT(
	        DATE_ADD(DATE_ADD(CURDATE(), INTERVAL - MONTH(CURDATE()) + 2 MONTH),
	        INTERVAL (cast( help_topic_id AS signed ) - 1 ) MONTH
	        ), '%Y-%m') `date`
	    </otherwise>
	</choose>
	
	FROM mysql.help_topic
	WHERE
	<choose>
	    <when test = "timeType == 1">
	        help_topic_id <![CDATA[ < ]]> 7
	    </when>
	    <when test = "timeType == 2">
	        help_topic_id <![CDATA[ < ]]> DAY ( CURDATE( ) )
	    </when>
	    <otherwise>
	        help_topic_id <![CDATA[ < ]]> MONTH ( curdate( ) )
	    </otherwise>
	</choose>
	ORDER BY help_topic_id

注：mysql版本是5.7.17
cast( help_topic_id AS signed INTEGER )之前忘了报啥错，去掉了Integer就可以了，但我另一个项目这么写没问题（狗头）

积分排行

SELECT 
	elt(INTERVAL(score, 0, 21, 41, 61, 81), '1/0-20', '2/21-40', '3/41-60', '4/61-80', '5/81-100') score_range, 
	count(user_id) as counts
FROM test_log_info t
WHERE 
-- 统计的条件
DATE_FORMAT(test_end_time, '%Y-%m-%d') = DATE_ADD(CURDATE(), INTERVAL -1 DAY)
GROUP BY score_range

重点是elt（）和interval（）俩函数，统计score字段在哪个范围内。结合下面的行转列，确保没有数据的积分段也显示。

行转列

SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(@type, ',', help_topic_id+1), ',', -1) AS score_range
FROM
mysql.help_topic ,(SELECT @type := '1/0-20,2/21-40,3/41-60,4/61-80,5/81-100') AS init
WHERE
LENGTH(@type) - LENGTH(REPLACE(@type,',','')) + 1 > help_topic_id
ORDER BY score_range

查询结果：