oracle “JCSJ”(借出时间)以"yyyy-mm-dd00:00:00"的格式储存每次借书的时间,如何统计每月的借书量
0
只能用“JCSJ”,想做成这样的视图
2014-01 **(该月借书量)
2014-02 **(该月借书量)
2014-03 **(该月借书量)
... ...
我自己写的用了两个表,但是依旧没有编写成功,这是我写的
第一张表
create or replace view baobiao12 as
with taps as (select to_date(substr(JCSJ,1,4),'yyyy') AS 年份,to_date(substr(JCSJ,1,7),'yyyy-MM') AS 月份,JCSJ from gxqt_tsgjyxxsjzl1 where JCSJ BETWEEN '2013-01-0100:00:00' AND '2016-12-3124:00:00')
select row_number() over(partition by 年份 order by 月份) as 数量,年份,月份 from taps;
第二张表
create or replace view baobiao13 as
select "数量","月份"
from BAOBIAO12 where 数量 in
(
select max(数量)from BAOBIAO12 group by 月份,年份
);
2014-01 **(该月借书量)
2014-02 **(该月借书量)
2014-03 **(该月借书量)
... ...
我自己写的用了两个表,但是依旧没有编写成功,这是我写的
第一张表
create or replace view baobiao12 as
with taps as (select to_date(substr(JCSJ,1,4),'yyyy') AS 年份,to_date(substr(JCSJ,1,7),'yyyy-MM') AS 月份,JCSJ from gxqt_tsgjyxxsjzl1 where JCSJ BETWEEN '2013-01-0100:00:00' AND '2016-12-3124:00:00')
select row_number() over(partition by 年份 order by 月份) as 数量,年份,月份 from taps;
第二张表
create or replace view baobiao13 as
select "数量","月份"
from BAOBIAO12 where 数量 in
(
select max(数量)from BAOBIAO12 group by 月份,年份
);
没有找到相关结果
重要提示:提问者不能发表回复,可以通过评论与回答者沟通,沟通后可以通过编辑功能完善问题描述,以便后续其他人能够更容易理解问题.
1 个回复
li052468 2017-11-22 回答
赞同来自:
如果JCSJ是字符串,select to_date(substr(JCSJ,1,7),'yyyy-mm'),count(*) from table group by to_date(substr(JCSJ,1,7),'yyyy-mm')