常用展示统计语句

1 当天借阅图书和归还图书数据量
select count(case substr(a.lend_date,1,10) when to_char(sysdate,’yyyy-mm-dd’) then 1 else null end) as today_lend,
count(case substr(a.ret_date,1,10) when to_char(sysdate,’yyyy-mm-dd’) then 1 else null end) as today_ret
from lend_hist a ;

2 当月借阅图书和归还图书数据量
select count(case substr(a.lend_date,1,7) when to_char(sysdate,’yyyy-mm’) then 1 else null end) as month_lend,
count(case substr(a.ret_date,1,7) when to_char(sysdate,’yyyy-mm’) then 1 else null end) as month_ret
from lend_hist a ;

3 当年各月借还图书数据量
—当年各月借阅量
SELECT
sum(decode(substr(a.lend_date,6,2),’01’,1,0)) “month01_lend”,
sum(decode(substr(a.lend_date,6,2),’02’,1,0)) “month02_lend”,
sum(decode(substr(a.lend_date,6,2),’03’,1,0)) “month03_lend”,
sum(decode(substr(a.lend_date,6,2),’04’,1,0)) “month04_lend”,
sum(decode(substr(a.lend_date,6,2),’05’,1,0)) “month05_lend”,
sum(decode(substr(a.lend_date,6,2),’06’,1,0)) “month06_lend”,
sum(decode(substr(a.lend_date,6,2),’07’,1,0)) “month07_lend”,
sum(decode(substr(a.lend_date,6,2),’08’,1,0)) “month08_lend”,
sum(decode(substr(a.lend_date,6,2),’09’,1,0)) “month09_lend”,
sum(decode(substr(a.lend_date,6,2),’10’,1,0)) “month10_lend”,
sum(decode(substr(a.lend_date,6,2),’11’,1,0)) “month11_lend”,
sum(decode(substr(a.lend_date,6,2),’12’,1,0)) “month12_lend”
from lend_hist a
where substr(a.lend_date,1,4) = to_char(sysdate,’yyyy’) ;

—当年各月还书量
SELECT
sum(decode(substr(a.ret_date,6,2),’01’,1,0)) “month01_ret”,
sum(decode(substr(a.ret_date,6,2),’02’,1,0)) “month02_ret”,
sum(decode(substr(a.ret_date,6,2),’03’,1,0)) “month03_ret”,
sum(decode(substr(a.ret_date,6,2),’04’,1,0)) “month04_ret”,
sum(decode(substr(a.ret_date,6,2),’05’,1,0)) “month05_ret”,
sum(decode(substr(a.ret_date,6,2),’06’,1,0)) “month06_ret”,
sum(decode(substr(a.ret_date,6,2),’07’,1,0)) “month07_ret”,
sum(decode(substr(a.ret_date,6,2),’08’,1,0)) “month08_ret”,
sum(decode(substr(a.ret_date,6,2),’09’,1,0)) “month09_ret”,
sum(decode(substr(a.ret_date,6,2),’10’,1,0)) “month10_ret”,
sum(decode(substr(a.ret_date,6,2),’11’,1,0)) “month11_ret”,
sum(decode(substr(a.ret_date,6,2),’12’,1,0)) “month12_ret”
from lend_hist a
where substr(a.ret_date,1,4) = to_char(sysdate,’yyyy’) ;

4 当年借书量最高的前5名读者
select b.cert_id 读者证件号,b.name 姓名,tmp_lend.count 借书量,tmp_lend.rank 排行
from
( select a.cert_id_f cert_id,count(a.cert_id_f) count,rank() over(order by count(a.cert_id_f) desc) rank
from lend_hist a
where substr(a.lend_date,1,4)=to_char(sysdate,’yyyy’)
group by a.cert_id_f) tmp_lend,reader b
where tmp_lend.cert_id=b.cert_id
and tmp_lend.rank<=5
order by tmp_lend.rank;

5 当年被借次数最多的前5名图书名称
select b.m_title 题名, tmp_lend.num 借阅量, tmp_lend.rank 排行
from
(select a.marc_rec_no_f marc_rec_no, count(a.marc_rec_no_f) num,rank() over(order by count(a.marc_rec_no_f) desc) rank
from lend_hist a
where substr(a.lend_date,1,4)=to_char(sysdate,’yyyy’)
group by a.marc_rec_no_f) tmp_lend,marc b
where tmp_lend.marc_rec_no=b.marc_rec_no
and tmp_lend.rank<=5
order by tmp_lend.rank;

6 当年各月各学院借还图书数据量及排名
—当年各单位各月借书量
SELECT b.dept,
sum(decode(substr(a.lend_date,6,2),’01’,1,0)) “month01_lend”,
sum(decode(substr(a.lend_date,6,2),’02’,1,0)) “month02_lend”,
sum(decode(substr(a.lend_date,6,2),’03’,1,0)) “month03_lend”,
sum(decode(substr(a.lend_date,6,2),’04’,1,0)) “month04_lend”,
sum(decode(substr(a.lend_date,6,2),’05’,1,0)) “month05_lend”,
sum(decode(substr(a.lend_date,6,2),’06’,1,0)) “month06_lend”,
sum(decode(substr(a.lend_date,6,2),’07’,1,0)) “month07_lend”,
sum(decode(substr(a.lend_date,6,2),’08’,1,0)) “month08_lend”,
sum(decode(substr(a.lend_date,6,2),’09’,1,0)) “month09_lend”,
sum(decode(substr(a.lend_date,6,2),’10’,1,0)) “month10_lend”,
sum(decode(substr(a.lend_date,6,2),’11’,1,0)) “month11_lend”,
sum(decode(substr(a.lend_date,6,2),’12’,1,0)) “month12_lend”
from lend_hist a, reader b
where substr(a.lend_date,1,4) = to_char(sysdate,’yyyy’)
and b.cert_id = a.cert_id_f
group by b.dept;

—当年各单位各月还书量
SELECT b.dept,
sum(decode(substr(a.ret_date,6,2),’01’,1,0)) “month01_ret”,
sum(decode(substr(a.ret_date,6,2),’02’,1,0)) “month02_ret”,
sum(decode(substr(a.ret_date,6,2),’03’,1,0)) “month03_ret”,
sum(decode(substr(a.ret_date,6,2),’04’,1,0)) “month04_ret”,
sum(decode(substr(a.ret_date,6,2),’05’,1,0)) “month05_ret”,
sum(decode(substr(a.ret_date,6,2),’06’,1,0)) “month06_ret”,
sum(decode(substr(a.ret_date,6,2),’07’,1,0)) “month07_ret”,
sum(decode(substr(a.ret_date,6,2),’08’,1,0)) “month08_ret”,
sum(decode(substr(a.ret_date,6,2),’09’,1,0)) “month09_ret”,
sum(decode(substr(a.ret_date,6,2),’10’,1,0)) “month10_ret”,
sum(decode(substr(a.ret_date,6,2),’11’,1,0)) “month11_ret”,
sum(decode(substr(a.ret_date,6,2),’12’,1,0)) “month12_ret”
from lend_hist a,reader b
where substr(a.ret_date,1,4) = to_char(sysdate,’yyyy’)
and a.cert_id_f=b.cert_id
group by b.dept ;

—当年各单位借阅量排名
select b.dept,count(a.prop_no_f) count,rank() over(order by count(a.prop_no_f) desc) rank
from lend_hist a,reader b
where substr(a.lend_date,1,4)=to_char(sysdate,’yyyy’)
and a.cert_id_f=b.cert_id
group by b.dept;

7 当月新增图书数量
select count(*) month_collection
from indi_acct a
where a.book_stat_code >=’17’
and substr(a.in_date,1,7)=to_char(sysdate,’yyyy-mm’);