一些统计的sql,按月份，季度，半年，年份等

本文链接：https://blog.csdn.net/devotemyself/article/details/106713852

按半年统计

SELECT
	count( 1 ) num,
	(
CASE
	WHEN MONTH ( vip_begin_time ) <= 6 THEN
	CONCAT( DATE_FORMAT( vip_begin_time, '%Y' ), '上半年' ) ELSE CONCAT( DATE_FORMAT( vip_begin_time, '%Y' ), '下半年' ) 
END 
	) date
FROM
	vip_operate_log 
WHERE
	DATE_FORMAT( vip_begin_time, '%Y' ) != 000000 
	AND vip_begin_time IS NOT NULL 
GROUP BY
date

按年统计

 SELECT
        count( 1 ) num,
        DATE_FORMAT( vip_begin_time, '%Y' ) groupDate
        FROM
        vip_operate_log
        WHERE
        DATE_FORMAT( vip_begin_time, '%Y' ) != 000000
        and vip_begin_time is not null
        <if test="beginDate != null and beginDate != '' ">
            and #{1} &gt;= vip_begin_time
        </if>
        GROUP BY
        DATE_FORMAT( vip_begin_time, '%Y' )

按季度统计

SELECT
    count( 1 ) num,
    CONCAT(YEAR(vip_begin_time),'_',quarter(vip_begin_time)) qu
    FROM
    vip_operate_log
    WHERE
    DATE_FORMAT( vip_begin_time, '%Y-%m' ) != 000000
    and vip_begin_time is not null
    <if test="beginDate != null and beginDate != '' ">
        and #{1} &gt;= vip_begin_time
    </if>
    GROUP BY
    qu

按月分统计

  SELECT
        count( 1 ) num,
        DATE_FORMAT( vip_begin_time, '%Y-%m' ) groupDate
        FROM
        vip_operate_log
        WHERE
        DATE_FORMAT( vip_begin_time, '%Y-%m' ) != 000000
        and vip_begin_time is not null
        <if test="beginDate != null and beginDate != ''">
            and #{1} &gt;= vip_begin_time
        </if>
        GROUP BY
        DATE_FORMAT( vip_begin_time, '%Y-%m' )