mysql实现按照某个时间段分组统计

SELECT
	DATE_FORMAT( create_order_time, '%H' ) AS h,
	COUNT( 1 ) 
FROM
	order_item 
GROUP BY
	DATE_FORMAT( create_order_time, '%H' )
ORDER BY
	DATE_FORMAT( create_order_time, '%H' )

-- 	区间数字SQL
SELECT
	(FLOOR(DATE_FORMAT( create_order_time, '%H' ) / 2) * 2) AS h2,
	COUNT( 1 ) 
FROM
	order_item 
GROUP BY
	FLOOR(DATE_FORMAT( create_order_time, '%H' ) / 2)
ORDER BY
	FLOOR(DATE_FORMAT( create_order_time, '%H' ) / 2)

-- 最终SQL
SELECT
	 CONCAT(
	 FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 ) * 2,
	 '-',
	 FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 ) * 2 + 2)
	 AS h2,
	COUNT( 1 ) 
FROM
	order_item 
GROUP BY
	FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 ) 
ORDER BY
	FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 )

select elt(interval(finish_ime,'2019-11-01','2020-11-01','2021-11-01','2022-11-01'),2019,2020,2021,2022) as i,count(*)
from cases
group by i

    select count(*) as '数量', t.`year` as "年份"
    from cases c left join tmp t on c.finish_time>=t.start_date and 
                                    c.finish_time<=t.end_date 
    group by t.`year`

create function get_year(finishTime datetime) returns int
begin
    declare d_year int(4);
    set d_year = year(finishTime);
    if (finishTime >= concat(d_year, '-11-01 00:00:00')) then
        return d_year + 1;
    else
        return d_year;
    end if;
end;

    select count(*) as '数量',get_year(c.finish_ime) as "年份"
    from cases c 
    group by get_year(c.finish_ime)