常用sql方法(Oracle)

一、分组编号

row_number() over(partition by pbid order by zdfzl desc) fzbh

二、取整

--向上取整：
ceil((sysdate - a.JDDJSJ) * 24) - 1 ((sysdate - a.JDDJSJ) * 24*60)
--向下取整：
floor((sysdate-a.JDDJSJ)*24) yjjb1

三、取绝对值

select abs(-1) from dual

四、判断奇偶数

--偶数：
select mod(a,2) from dual; --0
--奇数：
select mod(a,2) from dual;--1

五、保留两位小数

select round(1.23455,2) from dual ;

六、判断百分数，不是百分数的置为空

select trim(translate('-1.6%','0123456789.-%',' '))  from dual;

七、时间相关

--timestamp转换成date
select cast(create_date as date) from dual;
--获取本月的每一天
select to_date(to_char(sysdate, 'yyyymm'), 'yyyymm') + (rownum - 1) day_id from dual
connect by rownum <= to_number(to_char(last_day(to_date(to_char(sysdate, 'yyyymm'), 'yyyymm')), 'dd'));
--获取月初月末
select trunc(sysdate,'month') 本月第一天,trunc((trunc(sysdate,'month')-1),'month') 上月第一天,(trunc(sysdate,'month')-1) 上月最后一天 from dual;
select trunc(sysdate,'mm') 本月第一天,trunc(add_months(sysdate,-1),'mm') 上月第一天 from dual;
--获取年:
select extract(year from sysdate) from dual;
--获取月:
select extract(month from sysdate)  from dual;
--获取日:
select extract(day from sysdate) from dual;
--获取时:
select extract(hour from systimestamp) from dual;
--取1、2、3、4...到23点的时间
select to_char((to_date('2020-01-20', 'yyyy-mm-dd') + (level - 1) / 24),'hh24:mi') sj from dual connect by level <= 24;
--取近两个月
select add_months(sysdate,-2) from dual
--今年最后一天
select last_day(add_months(trunc(sysdate,'y'),11)) from dual
--去年第一天
select trunc(trunc(to_date('2023','yyyy'),'year')-1,'year') from dual ;
--今年的每一天
SELECT TRUNC(SYSDATE, 'YYYY') + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE, 'YYYY')
--获取本月天数
select add_months(to_date(to_char(sysdate, 'yyyy/mm'), 'yyyy/mm'), 1) -
       to_date(to_char(sysdate, 'yyyy/mm'), 'yyyy/mm') m_num
  from dual;

八、正则表达式

--汉字中截取数字的正则表达式
select regexp_replace(t.error_msg,'[^0-9]') yhbm ,t.error_msg,t.*
  from T_MSG_SENDERROR t
 where t.error_name = '中压用户-必填项校验'
   and t.create_time > trunc(date '2021-07-13');
--汉字中截取字母和数字的正则表达式
select regexp_replace(t.error_msg,'[^A-Za-z0-9]') yhbm ,t.error_msg,t.*
  from T_MSG_SENDERROR t
 where t.error_name = '中压用户-必填项校验'
   and t.create_time > trunc(date '2021-07-13')

九、分页

select a.* from (select t.*,rownum bh from (select * from aaaa ) t where rownum <=6) a where  a.bh>0;
select a.* from (select t.*,rownum bh from (select * from aaaa ) t where rownum <=8) a where  a.bh>6;

十、行转列

SELECT * FROM ( select t.unit_id, t.shs
  from t_shsfjb t
 where t.rq = to_number(to_char(sysdate, 'mm'))
   and t.mb_type = '3'
   and t.unit_id <> '2101') b 
   pivot(
   sum (shs)
   for unit_id in ('210101' as 藁城,'210102' as 晋州,'210103' as 栾城 ,'210104' as 赵县,'210105' as 元氏,'210106' as 无极,'210107' as 赞皇,'210108' as 新乐,'210109' as 灵寿,
   '210110' as 鹿泉,'210111' as 高邑,'210112' as 平山,'210113' as 行唐,'210114' as 辛集,'210115' as 井陉,'210116' as 深泽, '210117' as 正定 ));

十一、查询一年中的12个月

--第一种:
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT('2022', '0101'), 'YYYYMMDD'),
                          LEVEL - 1),
               'mm') || '月' AS month
  FROM DUAL
CONNECT BY LEVEL <= 12;

--第二种:
SELECT TO_CHAR(ADD_MONTHS(DATE '2022-01-01', LEVEL - 1), 'mm') || '月' AS month
  FROM DUAL
CONNECT BY LEVEL <= 12;

--第三种: 英文
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT('2023', '0101'), 'YYYYMMDD'),
                          LEVEL - 1),
               'fmMonth') AS month
  FROM DUAL
CONNECT BY LEVEL <= 12;


--第四种：带时间格式
select t1.rn,t1.month begin_time,t2.month end_time 
       from (select rownum rn,to_char(add_months(trunc(sysdate, 'year'), level - 1),'yyyy-mm-dd')||' 00:00:00' month from dual connect by level <= 12) t1,
            (select rownum rn,to_char(add_months(add_months(trunc(sysdate, 'year'),1)-1, level - 1),'yyyy-mm-dd')||' 23:59:59' month from dual connect by level <= 12) t2
       where t1.rn=t2.rn；


--第五种：一年中的12个月指定日期
select t1.rn,t1.month begin_time,t2.month end_time 
from (select rownum rn,add_months(trunc(sysdate, 'year'), level - 1)+9 month from dual connect by level <= 12) t1,
     (select rownum rn,add_months(add_months(trunc(sysdate, 'year'),1)-1, level - 1)+10 month from dual connect by level <= 12) t2
where t1.rn=t2.rn

十二、oralce排名函数

--Oracle常用分析函数（排名）
rank() over(order by score desc)   --按照大小进行排名，纯粹的排名，相同大小名次一样，会有名次的跳跃，即除了相同的，其余排名顺序与行号相同，第一名有十个，那第十一个就是第十一名
dense_rank() over(order by score desc)  --按照大小进行排名，相同大小名次一样，但是不会进行名次的跳跃，即第一名完了就是第二名，就算第一名有十个，那第十一个也是第二名
row_number() over(order by score desc)  --按照大小进行排名，相同大小名次不一样，就是按照行号走下去

十三、本日、本周、本月、本季、本年

--本天
select trunc(sysdate) "今天",trunc(sysdate)+1 "明天" from dual;
--本周
select trunc(sysdate, 'iw') "本周第一天",trunc(sysdate, 'iw')+7 "下周第一天" from dual;
--本月
select trunc(sysdate, 'mm') "本月第一天",add_months(trunc(sysdate, 'mm'),1) "下月第一天" from dual;
--本季
select trunc(sysdate, 'q') "本季第一天",add_months(trunc(sysdate, 'q'),3) "下季第一天" from dual;
--本年
select trunc(sysdate, 'year') "今年第一天",add_months(trunc(sysdate, 'year'),12) "下年第一天" from dual;
--今天0点
select trunc(sysdate) from daul
--本周第一天
SELECT TRUNC(SYSDATE, 'iw') "本周第一天" FROM DUAL;
--本月第一天
SELECT TRUNC(SYSDATE, 'mm') "本月第一天" FROM DUAL;
--本季第一天
SELECT TRUNC(SYSDATE, 'q') "本季第一天" FROM DUAL;
--本年第一天
SELECT TRUNC(SYSDATE, 'year') "本年第一天" FROM DUAL;