相关SQL

于 2020-03-28 17:41:39 发布
阅读量169 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/xpplrm/article/details/105164853
本文详细介绍了通过SQL查询统计图书馆办证数量、资源门户点击量及读者年龄分布等关键信息，涵盖不同时间段和读者类型的数据分析。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

  1. 资源门户点击量统计
     

    select b.*,a.elec_name,a.metadbid from  ( 
 
  select elec_stat_log.elec_id ,count(id)  from  elec_stat_log where elec_stat_log.elec_lib_code = 'zjlib'  
  and elec_stat_log .cretae_date >= to_date ('2019/1/23','yyyy-MM-dd')
  and elec_stat_log .cretae_date <= to_date ('2019/3/24','yyyy-MM-dd')
  
group by elec_stat_log.elec_id ) b, elec a  where a.id = b.elec_id

  2. 办证系统办证量统计

    select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and card_state in ('1','2','4','6','7')  
group by userinfo.branchcode; ---有效证


select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and card_state in ('3','5')  
group by userinfo.branchcode; ---退证


select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and  reader_card.card_time >  to_date('2015/12/31 23:59:59','yyyy-MM-dd hh24:mi:ss') 
and  reader_card.card_time <  to_date('2017/1/1 0:0:0','yyyy-MM-dd hh24:mi:ss') 
group by userinfo.branchcode;---新办证


select userinfo.voucher_no || ',' as voucher_no  ,userinfo.fullname,  reader_card.card_no ,reader_card.card_type  ,card_time    from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and  reader_card.card_time >=  to_date('2019/3/21 11:00:00','yyyy-MM-dd hh24:mi:ss') 
and  reader_card.card_time <=  to_date('2019/3/21 13:0:0','yyyy-MM-dd hh24:mi:ss')  and  reader_card.create_user = 'admin' order by reader_card.card_time desc 
 



select to_char(operation_date, 'yyyy-MM-dd')     ,count( id )   from reader_card_log
where reader_card_log.operation_date > to_date('2019-01-01 00:02:00', 'yyyy-MM-dd hh24:mi:ss')
and reader_card_log.operation_type = '1'
group by  to_char(operation_date, 'yyyy-MM-dd')   order by  count( id )  desc ; --每天的办证两


select count(user_code) from reader_card where reader_card.create_date  <  to_date('2017-01-01 00:02:00', 'yyyy-MM-dd hh24:mi:ss'); --截止时间的办证量

select count(user_code) from reader_card where reader_card.create_date  >  to_date('2019-01-01 00:02:00', 'yyyy-MM-dd hh24:mi:ss'); --大于止时间的办证量






select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and  reader_card.card_time >  to_date('2019/12/31 23:59:59','yyyy-MM-dd hh24:mi:ss') 
and  reader_card.card_time <  to_date('2021/1/1 0:0:0','yyyy-MM-dd hh24:mi:ss')  
and reader_card.card_type in (select card_type_code  from card_type where  card_type.branchcode in ('sxslib') and card_type.is_adult != 'Y')
group by userinfo.branchcode;---新办证 (少儿)

--- 4642

select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and  reader_card.card_time >  to_date('2019/12/31 23:59:59','yyyy-MM-dd hh24:mi:ss') 
and  reader_card.card_time <  to_date('2021/1/1 0:0:0','yyyy-MM-dd hh24:mi:ss')  
and reader_card.card_type in (select card_type_code  from card_type where  card_type.branchcode in ('sxslib') and card_type.is_adult = 'Y')
group by userinfo.branchcode;---新办证 (成人)

--  15560 


select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and  reader_card.card_time >  to_date('2019/12/31 23:59:59','yyyy-MM-dd hh24:mi:ss') 
and  reader_card.card_time <  to_date('2021/1/1 0:0:0','yyyy-MM-dd hh24:mi:ss')  
and reader_card.card_type in (select card_type_code  from card_type where  card_type.branchcode in ('sxslib') and card_type.is_adult = 'Y')
and  trim(translate(userinfo.voucher_no,'0123456789',' ')) is NULL
and  (to_char(sysdate, 'yyyy') - substr(userinfo.voucher_no, 7, 4)) > 59 
group by userinfo.branchcode; ---新办证 (成人 > 59)

--  93 



select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and  reader_card.card_time >  to_date('2019/12/31 23:59:59','yyyy-MM-dd hh24:mi:ss') 
and  reader_card.card_time <  to_date('2021/1/1 0:0:0','yyyy-MM-dd hh24:mi:ss')  
and reader_card.card_type in (select card_type_code  from card_type where  card_type.branchcode in ('sxslib') and card_type.is_adult = 'Y')
and  trim(translate(userinfo.voucher_no,'0123456789',' ')) is NULL
and  (to_char(sysdate, 'yyyy') - substr(userinfo.voucher_no, 7, 4)) >= 14 
and  (to_char(sysdate, 'yyyy') - substr(userinfo.voucher_no, 7, 4)) <= 59 


group by userinfo.branchcode ;   ---新办证 (成人 14-59 )

--  14089 


select * from userinfo where trim(translate(userinfo.voucher_no,'0123456789',' ')) is NULL  ;


select * from  userinfo 

select (to_char(sysdate, 'yyyy') - substr(userinfo.voucher_no, 7, 4)) age from userinfo where userinfo .voucher_no = '330182198409302315';





select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and  reader_card.card_time >  to_date('2020/12/31 23:59:59','yyyy-MM-dd hh24:mi:ss') 
and  reader_card.card_time <  to_date('2022/1/1 0:0:0','yyyy-MM-dd hh24:mi:ss')  
and reader_card.card_type in (select card_type_code  from card_type where  card_type.branchcode in ('sxslib'))
group by userinfo.branchcode;---新办证  27867
 



select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and  reader_card.card_time >  to_date('2020/12/31 23:59:59','yyyy-MM-dd hh24:mi:ss') 
and  reader_card.card_time <  to_date('2022/1/1 0:0:0','yyyy-MM-dd hh24:mi:ss')  
and  reader_card.create_user  ='aliwindow'
and  userinfo.create_user  ='aliwindow'
and reader_card.card_type in (select card_type_code  from card_type where  card_type.branchcode in ('sxslib'))
group by userinfo.branchcode;---支付宝新办证 -24678



select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and card_state in ('3','5')  
and reader_card.card_type in (select card_type_code  from card_type where  card_type.branchcode in ('sxslib'))
group by userinfo.branchcode; ---退证 143694

select  userinfo.branchcode ,count(userinfo.user_code) from reader_card , userinfo  
where reader_card.user_code = userinfo.user_code and card_state in ('1','2','4','6','7')  
group by userinfo.branchcode; ---有效证 272734
  




//分单位的退证情况

select  count(*) from reader_card_log  where reader_card_log.operation_type = '8'   

and  reader_card_log.operation_date > to_date('2021/1/1 0:0:0','yyyy-MM-dd hh24:mi:ss')  
and  reader_card_log.operation_date <  to_date('2022/1/1 0:0:0','yyyy-MM-dd hh24:mi:ss') 

and reader_card_log.clerk in  (

select adm_user.user_code  from adm_user  where user_id  in  (

 select bas_department_user .user_id  from  bas_department_user  where dept_id = 'ff80808149dcdf530149dce3584e0000' 
 
 ) )

  3. ORACLE表空间

    /*关闭启动oracle*/
[oracle@localhost ~]$ sqlplus /nolog
SQL>conn /as sysdba
SQL>shutdown immediate
SQL>exit
[oracle@localhost ~]$ emctl stop dbconsole
[oracle@localhost ~]$ isqlplusctl stop
[oracle@localhost ~]$ lsnrctl stop



[oracle@localhost ~]$ emctl start dbconsole
[oracle@localhost ~]$ lsnrctl start

[root@localhost ~]# su - oracle
[oracle@localhost ~]$ sqlplus /nolog
SQL>conn /as sysdba
SQL>startup


/*第1步：创建表空间*/

create temporary tablespace SXREADER_TEMP  tempfile 'E:/APP/ORADATA/ORCL/SXREADER_TEMP.dbf' size 50m  autoextend on  next 50m maxsize 1024m extent management local;  
 
create tablespace SXREADER_DATE datafile 'E:/APP/ORADATA/ORCL/SXREADER_DATE.dbf' size 50m autoextend on  next 50m maxsize 20480m  extent management local; 
 

/*ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON;*/

/*第2步：创建用户并且分配表空间*/


create user sxcard identified by sxcard  default tablespace SXREADER_DATE  temporary tablespace SXREADER_TEMP;  

create user sxbill identified by sxbill  default tablespace SXREADER_DATE  temporary tablespace SXREADER_TEMP;  

create user sxcas  identified by sxcas   default tablespace SXREADER_DATE  temporary tablespace SXREADER_TEMP;

 

/*第4步：给用户授予权限  */
grant connect,resource,dba to sxcard;

grant connect,resource,dba to sxbill;

grant connect,resource,dba to sxcas;


/*第4步其他延伸  */
1、 grant connect,resource,dba to sxcard;   --授予dba

2、grant select any table to userName; --授予查询任何表  

3、grant select any dictionary to userName;--授予 查询任何字典   


/*第5数据导入导出  */

imp userid=system/infcn fromuser=interlib touser=interlib ignore=y file=F:\interlib.dmp   
 
exp userid=sxcard/sxcard owner=sxcard file=sxcard.dmp




/*第6异机备份数据库*/


exp userid=system/infcn@111.111.111.111/orcl owner=tzcard file=D:\infcn\ORADATA\oracback\%date:~11,3%_tzcard.dmp log=D:\infcn\ORADATA\oracback\%date:~11,3%_tzcard.log


/*清出日志   需要切换到 sys 账号下*/

truncate table SYS.AUD$

xpplrm
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值