需求要看最近7天新增评论数,而且每天都要查一次
SQL不复杂

SELECT to_char(createtime,'yyyy-mm-dd') createtime,count(*) c
FROM
mvbox_space.user_comment
where createtime>=trunc(sysdate - interval '7' day)
and createtime<trunc(sysdate)
group by to_char(createtime,'yyyy-mm-dd')

查询结果:

但是需求要横着看数据。
这块一般都是固定行数行转列处理,但是每天的日期是变化的,行转列的值不能写死.

这样就得用数字辅助表过渡一下。

select
max(case when lv=1 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=2 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=3 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=4 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=5 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=6 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=7 then nvl(t2.c,0) else null end) || '' c
from (
select to_char(trunc(sysdate)-numtodsinterval(level, 'day'),'yyyy-mm-dd') dt,level lv from dual connect by level <=7
) t1
left join(
SELECT to_char(createtime,'yyyy-mm-dd') createtime,count(*) c
FROM
mvbox_space.user_comment
where createtime>=trunc(sysdate - interval '7' day)
and createtime<trunc(sysdate)
group by to_char(createtime,'yyyy-mm-dd')
) t2 on (t1.dt=t2.createtime);

查询结果:

但是这样导出的时候,没有带具体的日期信息。
看着又不直观了。
所以再稍作修改。

select
max(case when level=1 then to_char(trunc(sysdate)-numtodsinterval(level, 'day'),'yyyy-mm-dd') else null end) || '' c,
max(case when level=2 then to_char(trunc(sysdate)-numtodsinterval(level, 'day'),'yyyy-mm-dd') else null end) || '' c,
max(case when level=3 then to_char(trunc(sysdate)-numtodsinterval(level, 'day'),'yyyy-mm-dd') else null end) || '' c,
max(case when level=4 then to_char(trunc(sysdate)-numtodsinterval(level, 'day'),'yyyy-mm-dd') else null end) || '' c,
max(case when level=5 then to_char(trunc(sysdate)-numtodsinterval(level, 'day'),'yyyy-mm-dd') else null end) || '' c,
max(case when level=6 then to_char(trunc(sysdate)-numtodsinterval(level, 'day'),'yyyy-mm-dd') else null end) || '' c,
max(case when level=7 then to_char(trunc(sysdate)-numtodsinterval(level, 'day'),'yyyy-mm-dd') else null end) || '' c
from dual connect by level <=7
union all
select
max(case when lv=1 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=2 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=3 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=4 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=5 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=6 then nvl(t2.c,0) else null end) || '' c,
max(case when lv=7 then nvl(t2.c,0) else null end) || '' c
from (
select to_char(trunc(sysdate)-numtodsinterval(level, 'day'),'yyyy-mm-dd') dt,level lv from dual connect by level <=7
) t1
left join(
SELECT to_char(createtime,'yyyy-mm-dd') createtime,count(*) c
FROM
mvbox_space.user_comment
where createtime>=trunc(sysdate - interval '7' day)
and createtime<trunc(sysdate)
group by to_char(createtime,'yyyy-mm-dd')
) t2 on (t1.dt=t2.createtime)
;

查询结果:

妥了

ITPub博客

动态固定行转列

最新文章