-
资源门户点击量统计
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
-
办证系统办证量统计
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' ) )
-
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$
相关SQL
最新推荐文章于 2024-10-05 00:24:42 发布