# 一、关于日期操作的相关的函数
##（一）、日期函数
###①、sysdate，该函数返回系统时间
![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/01/10/kuangstudy5a5c749f-3d18-40c4-bb4a-8c9790e2f592.png)
###②、months_between(d1,d2)，该函数用于计算日期d1和日期d1之间隔了多少个月，d1<d2则返回负数，反之则正数
```sql
select months_between(to_date('20200818', 'yyyymmdd'), to_date('20201018', 'yyyymmdd')) as months from dual --  -2
UNION all
select months_between(to_date('20201018', 'yyyymmdd'), to_date('20200818', 'yyyymmdd')) as months from dual -- 2
```
###③、add_months(d,n)，该函数在日期d上增加n个月数，n为正数则加，n为负数则减
```sql

select add_months(TO_DATE('2020-08-31', 'yyyy-mm-dd'),-2) from dual --2020-06-30 00:00:00
UNION all
select add_months(TO_DATE('2020-08-31', 'yyyy-mm-dd'),2) from dual --2020-10-31 00:00:00
```

###④、next_day(d, ‘星期*’)， 该函数是指定日期d下一个星期*对应的日期

```sql
select next_day(TO_DATE('2020-08-31', 'yyyy-mm-dd'), '星期五') as nextday from dual --2020-09-04 00:00:00
```

###⑤、last_day(d)，该函数返回指定日期d所在月份的最后一天

```sql
select  last_day(TO_DATE('2020-09-01', 'yyyy-mm-dd')) from dual  --2020-09-30 00:00:00
```

###⑥、extract(month from d)，该函数获取指定日期d的月份
```sql

SELECT extract(month from TO_DATE('2020-09-01', 'yyyy-mm-dd')) from dual -- 9
SELECT extract(month from TO_DATE('20201201', 'yyyymmdd')) from dual  --12
```

##（二）、日期、字符串、时间戳之间的转换
###①、to_char(d,format)：时间转字符串

```sql
select to_char(sysdate,'YYYY"年"MM"月"DD"日"') "时间转字符串" from dual  --2020年08月31日
UNION all
select to_char(sysdate,'yyyy-mm-dd') "时间转字符串" from dual  --2020-08-31
```

###②、to_date(chr,format)：字符串转时间

```sql
select to_date('2020-08-31 17:08:21','yyyy-MM-dd HH24:mi:ss') "字符串转时间" from dual  --2020/8/31 17:08:21
```

###③、to_char(systimestamp,format)：时间戳转字符串

```sql
select TO_CHAR(systimestamp,'YYYY-MM-DD hh24:mi:ss:ff3')from dual  --2020-08-31 17:04:32:028
```

###④、TO_TIMESTAMP_TZ('2020-10-23 17:58:07.254000','YYYY-MM-DD HH24:MI:SS.FF')：字符串转时间戳

```sql
SELECT TO_TIMESTAMP_TZ('2020-10-23 17:58:07.254000','YYYY-MM-DD HH24:MI:SS.FF') from dual--2020-10-23 17:58:07.254000000 +08:00
```

# 二、递归函数
```sql
--select  * from  表   start with 条件1   connect by prior 条件2  
select  * from people   start with id='123'   connect by prior id=parentid   --向下查询
select  * from people   start with id='123'   connect by prior parentid=id   --向上查询
```
# 三、oracle中比较符号在mybatis中的转换
![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/01/10/kuangstudyf43df27c-db59-4146-ab9f-857bc15484dc.png)

# 四、sys_guid()生成uuid函数及乱码解决
```sql
SELECT sys_guid() from dual ;  --+�>Ѭ�B|���W)+��
SELECT  rawtohex(sys_guid()) from dual;  --874455C82D3E4875853195AC6317FF85
```

使用sys_guid()在navicat上生成的uuid乱码时，可以利用数据库函数转换下（RAW是种二进制数据）即可 rawtohex(sys_guid())。

# 五、oracle删除重复数据保留一条
   i![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/01/10/kuangstudy9596af80-d1f6-43d5-a036-914c335a0752.png)d为2和5的数据重复

##1、根据id查找表中重复的数据

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/01/10/kuangstudy8f7c82c3-6a3d-4cdb-9f86-96249c004238.png)

select * from a_demo where id in (select id from a_demo group by id having count(Id) > 1)

##2、删除重复的数据，只保留一条（将查询语句改为delete即可删除）
![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/01/10/kuangstudy90555ca1-e1ef-4493-be5c-7a6fe35aa934.png)
```sql
select * from a_demo WHERE (id) IN ( SELECT id FROM a_demo GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM a_demo GROUP BY id HAVING COUNT(*) > 1);
```

##3、查询表中多个字段重复记录

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/01/10/kuangstudyaaf35340-134a-4be9-ac87-469766b60e9d.png)

```sql
select * from a_demo a
where (a.Id,a.class) in (select Id,class from a_demo group by Id,class having count(*) > 1)

```
# 六、oracle行转列、列转行函数
##1、行转列

![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/01/10/kuangstudyd89808ac-1256-405f-98c3-49c24af5128f.png)

数据如上图，我需要将dict_label装换到一行中的一例里面，用逗号隔开，上sql
```sql
select listagg( to_char(DICT_LABEL),',') within group (order by DICT_LABEL) c from JS_SYS_DICT_DATA where DICT_TYPE='P_HONORTYPE'
```

结果如图![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/01/10/kuangstudyd30e965a-febd-4484-adbd-a52d0db68787.png)

##2、列转行

数据如右图，![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/01/10/kuangstudyba5e3cc9-18eb-44eb-b975-06ecb8a41421.png)，我需要将其转行为两行展示，上sql

```sql
select
regexp_substr(honortype,'[^,]+',1,level) as honortype
from PROJPERSON where PROJPERSONID ='1341307905741766656'
connect by level<=regexp_count(honortype,'[^,]+')
and rowid=prior rowid
and prior dbms_random.value is not null
```

结果如右图![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/01/10/kuangstudyb78828ca-9833-4b08-9274-60ea004630e4.png)

ORACLE数据库常用sql语句收集，方便使用时查询

总共已有 0 条评论