读者--相关的统计

—查询读者违章情况
select a.cert_id 证件号,a.name 姓名,a.dept 单位,a.code01 年级组,c.prop_no 财产号,c.call_no 索书号,
d.volt_name 违章类型
from reader a,reader_volt_lst b,indi_acct c,violate_code d
where a.cert_id=b.cert_id
and b.prop_no(+)=c.prop_no
and b.volt_code=d.volt_code
and b.punish_date >=’2000-01-01’
and b.punish_date <=’2017-12-31’;

—零借阅的有效读者数据
select a.name 姓名, a.cert_id 证件号,b.redr_type_name 读者类型,a.dept 单位
from reader a,reader_type b
where a.redr_type_code=b.redr_type_code
and a.redr_flag=’1’
and not exists
(select cert_id_f from lend_hist where a.cert_id=lend_hist.cert_id_f and substr(lend_date,1,10)>=’1990-01-01’ and substr(lend_date,1,10)<=’2018-07-31’);

—有借阅的有效读者数据
select a.name 姓名, a.cert_id 证件号,b.redr_type_name 读者类型,a.dept 单位
from reader a,reader_type b
where a.redr_type_code=b.redr_type_code
and a.redr_flag=’1’
and a.cert_id in
(select cert_id_f from lend_hist where a.cert_id=lend_hist.cert_id_f and substr(lend_date,1,10)>=’1990-01-01’ and substr(lend_date,1,10)<=’2018-07-31’);

—导出毕业班的读者欠书欠费信息：包括读者证件号、读者身份证号、院系、班级、已借册数、超期册数、违章欠费、超期欠费和总欠费
select tmp_reader.cert_id 证件号,tmp_reader.id_card 身份证号,tmp_reader.name 姓名,tmp_reader.dep_name 院系,tmp_reader.dept 班级,
tmp_reader.num1 在借册数,tmp_reader.num2 超期册数,tmp_reader.num3 违章欠费,tmp_reader.num4 超期欠费,tmp_reader.debt 总欠费
from (select a.cert_id,a.id_card,a.name,department.dep_name,a.dept,nvl(bb.num1,0) num1,nvl(cc.num2,0) num2,
nvl(dd.num3,0) num3,nvl(ee.num4,0) num4,(nvl(dd.num3,0)+nvl(ee.num4,0)) debt,(nvl(bb.num1,0)+nvl(dd.num3,0)+nvl(ee.num4,0)) flag
from reader a,department,
(select b.cert_id ,count(b.prop_no) num1 from lend_lst b
group by b.cert_id) bb,
(select c.cert_id ,count(c.prop_no) num2 from lend_lst c
where substr(c.norm_ret_date,1,10)<=to_char(sysdate,’yyyy-mm-dd’) group by c.cert_id) cc,
(select d.cert_id,sum(d.real_fine_amt) num3 from reader_volt_lst d where d.fine_date is null group by d.cert_id) dd,
(select e.cert_id,sum(e.fine_amt) num4 from exceed_fine_lst e where e.debt_deal_flag=’0’ group by e.cert_id) ee
where a.cert_id=bb.cert_id(+) and a.cert_id=cc.cert_id(+) and a.cert_id=dd.cert_id(+) and a.cert_id=ee.cert_id(+)
and a.r_dep_id=department.dep_id(+) and a.code01=’2016’) tmp_reader where flag<>0 order by tmp_reader.cert_id;

—提供指定读者未处理的违章和未处理的超期罚款的详细信息
select from (select b.redr_cert_id 条码号,a.name 姓名,a.dept 单位,a.code01 年级组,a.education 文化程度,c.prop_no 财产号,
d.volt_name 违章类型,c.REAL_FINE_AMT 罚款金额 from reader a,reader_cert b,READER_VOLT_LST c,violate_code d
where a.cert_id=b.cert_id and a.cert_id=c.cert_id and c.volt_code=d.volt_code
and a.education in(‘专科’,’大专’) and a.code01=’2015’ and a.redr_flag=’1’
and c.fine_date is null)T1 union all select from (select b.redr_cert_id 条码号,a.name 姓名,a.dept 单位,a.code01 年级组,a.education 文化程度,c.prop_no 财产号,
‘超期欠款’,c.fine_amt 罚款金额
from reader a,reader_cert b,EXCEED_FINE_LST c
where a.cert_id=b.cert_id and a.cert_id=c.cert_id
and a.education in(‘专科’,’大专’) and a.code01=’2015’ and a.redr_flag=’1’
and c.debt_deal_flag=’0’)T2;

—查询遗失赔偿。其中退赔里有内容说明该数据做过退赔处理
select decode(a.recoup_type,’1’,’赔书’,’2’,’赔钱’,’3’,’退赔’,’4’,’赔书预处理’) 赔偿类型,b.name 姓名,b.cert_id 证件号,b.dept 单位,b.code01 年级组,a.recoup_date 操作时间,a.src_price 原始书刊价格,
a.tech_fee 技术处理费,a.serv_fee 人工服务费,a.real_recoup_amt 实际赔款额,a.recoup_flag 退赔情况,
d.m_title 题名,d.m_author 作者,d.m_publisher 出版社,d.m_pub_year 出版年,a.prop_no 财产号,c.call_no 索书号
from lost_recoup_lst a,reader b,indi_acct c,marc d
where a.cert_id=b.cert_id and a.prop_no=c.prop_no and c.marc_rec_no=d.marc_rec_no
and a.recoup_date >=’2018-01-01’ and a.recoup_date <=’2018-12-31’
order by 赔偿类型;

—积分排行的语句
select * from (select a.cert_id 证件号,a.name 读者姓名,a.dept 读者单位,a.code01 年级组,a.credit_num 当前积分数
from reader a
where a.redr_flag=’1’
order by a.credit_num desc) where rownum<=’20’;

—根据系别查询有效读者的积分情况
select b.dep_name 系别,a.cert_id 证件号,a.name 读者姓名,a.dept 读者单位,a.code01 年级组,a.credit_num 当前积分数
from reader a,department b
where a.r_dep_id=b.dep_id(+)
and a.redr_flag=’1’
order by b.dep_name;