sysdate - 10/1440 10分钟前 1440 表示 24*60(一天的小时数*分钟数)
sysdate - 2/24 2小时前
yyyy-mm-dd hh24:mi:ss 年月日
Oracle查询今天、昨天、本周、上周、本月、上月数据
查询今天数据:
SELECT COUNT(1) FROM T_CALL_RECORDS WHERE TO_CHAR(T_RKSJ,'YYYY-MM-DD')=TO_CHAR(SYSDATE,'YYYY-MM-DD');
查询昨天数据:
SELECT COUNT(1) FROM T_CALL_RECORDS WHERE TO_CHAR(T_RKSJ,'YYYY-MM-DD')=TO_CHAR(SYSDATE-1,'YYYY-MM-DD');
查询本周数据:
SELECT COUNT(1) FROM T_CALL_RECORDS WHERE T_RKSJ >= TRUNC(NEXT_DAY(SYSDATE-8,1)+1) AND T_RKSJ < TRUNC(NEXT_DAY(SYSDATE-8,1)+7)+1;
查询上周数据:
SELECT COUNT(1) FROM T_CALL_RECORDS WHERE T_RKSJ >= TRUNC(NEXT_DAY(SYSDATE-8,1)-6) AND T_RKSJ < TRUNC(NEXT_DAY(SYSDATE-8,1)+1);
查询本月数据:
SELECT COUNT(1) FROM T_CALL_RECORDS WHERE TO_CHAR(T_RKSJ,'YYYY-MM')=TO_CHAR(SYSDATE,'YYYY-MM');
查询上月数据:
SELECT COUNT(1) FROM T_CALL_RECORDS WHERE TO_CHAR(T_RKSJ,'YYYY-MM')=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM');
-- 调整英文日期和中文日期格式
select to_date('07-Aug-2015','dd-mm-yyyy','NLS_DATE_LANGUAGE = english') as 英文日期 from dual;
select to_date('2015-08-07','yyyy-mm-dd') as 中文日期 from dual;
-- 按时间去重复用法如下
delete from (select a.*, row_number() over(partition by order_id order by to_date(OPEN_TIME,'dd-mm-yy','NLS_DATE_LANGUAGE = english') desc) rw from TRANS_SOURCE_DATE a where ORDER_ID is not null) t where t.rw <> 1;
-- 按最大id去重方法
delete from TRANS_SOURCE_DATE
where order_id in (select order_id from TRANS_SOURCE_DATE group by order_id having count(*) > 1)
and id not in (select max(id) from TRANS_SOURCE_DATE group by order_id having count(*) > 1);
select * from temp_hlwzx_zh where CIRCUIT_NO_1 like '%'||chr(13)||'%'; //查询字段中是否包含回车
update CHK_GRID_RES_RELATION set int_id =replace(int_id,chr(13),'') //将回车替换
chr(9) 水平制表符
chr(10)换行键
chr(13)回车键
chr(39)单引号
SELECT a.tablespace_name TABLESPACE_NAME,
total / 1048576 TOTAL_M,
free / 1048576 FREE_M,
(total - free) / 1048576 USED_M,
ROUND((total - free) / total, 4) * 100 "USED%",
autoextensible autoextem
FROM (SELECT tablespace_name, SUM(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
SUM(bytes) total,
max(autoextensible) autoextensible
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
order by 6, 5 desc
查询时将多条列名以逗号分隔,显示在一列中
select 列名1||','||列名2||','||列名3 from 表名;
解决无效数字文字(table表示可能存在无效数字的表,aaa这个表示数字类型的字段)
select * from table where is_number(aaa)=0 and aaa is not null;
本文地址:https://blog.csdn.net/qq616774576/article/details/107376962
如对本文有疑问, 点击进行留言回复!!
Oracle 基本概念 Dadabase,schema,user,table...
dbeaver连接Oracle中文乱码的解决方案--druid
Oracle常用语句(时间格式、去重、去特殊符号、查询表空间等)
Navicate 如何导出数据库中的存储过程、事件、视图等?
每日一记:Oracle升级2020年4月份的数据库补丁Database Patch Set Update : 11.2.0.4.200414 (30670774)
Oracle 19.3 dataguard for Redhat 7.6
网友评论