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')
四、显示结果