Orcle用户表统计最近12个月每个月的创建数量含本月

Orcle用户表统计最近12个月的每个月创建数量含本月
一、场景需求
想在数据库中用户表中查出最近12个月的用户注册数，查出的字段要有：创建年月year-month、数量count
二、准备所需
Orcle数据库（mysql应该也可以暂时还没试）test，
用户表user
表字段：id，name，createtime（仅测试字段）
三、sql语句
select
count(1) as cnt,
to_char(t.createtime, ‘yyyy-mm’) as year_month
from
test.user t
where
to_char(t.createtime, ‘yyyy-mm’)=to_char(add_months(sysdate, 0), ‘yyyy-mm’)
group by
to_char(t.createtime, ‘yyyy-mm’)

	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -1), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -2), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -3), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -4), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

	UNION

	select
	count(1),
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -5), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -6), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -7), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -8), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -9), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -10), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -11), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')
	UNION

	select
	count(1) as cnt,
	to_char(t.createtime, 'yyyy-mm') as year_month
	from
	test.user t
	where
	to_char(t.createtime, 'yyyy-mm')=to_char(add_months(sysdate, -12), 'yyyy-mm')
	group by
	to_char(t.createtime, 'yyyy-mm')

四、显示结果