时间to_date，层级查询 --工作备忘2016/1/8

richieruan

已于 2022-06-30 22:22:02 修改

阅读量717

点赞数

分类专栏：数据库个人经验总结技术总结文章标签：工作 oracle数据库

于 2016-01-08 16:51:59 首次发布

原文链接：https://blog.csdn.net/u010142437/article/details/102314410?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1.opensearchhbase&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefa

版权

个人经验总结同时被 3 个专栏收录

38 篇文章 0 订阅

订阅专栏

数据库

24 篇文章 1 订阅

订阅专栏

技术总结

15 篇文章 0 订阅

订阅专栏

工作备忘2016/1/8

1、 to_date() 使用方法

一、在使用Oracle的to_date函数来做日期转换时，很多Java程序员也许会直接的采用"yyyy-MM-dd HH:mm:ss"的格式作为格式进行转换，但是在Oracle中会引起错误："ORA 01810 格式代码出现两次"。

select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;

如：
原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。

select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;

二、另要以24小时的形式显示出来要用HH24

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//mi是分钟
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm会显示月份

oracle中的to_date参数含义

1.日期格式参数含义说明

D 一周中的星期几
DAY 天的名字，使用空格填充到9个字符
DD 月中的第几天
DDD 年中的第几天
DY 天的简写名
IW ISO标准的年中的第几周
IYYY ISO标准的四位年份
YYYY 四位年份
YYY,YY,Y 年份的最后三位，两位，一位
HH 小时，按12小时计
HH24 小时，按24小时计
MI 分
SS 秒
MM 月
Mon 月份的简写
Month 月份的全名
W 该月的第几个星期
WW 年中的第几个星期 1.日期时间间隔操作
当前时间减去7分钟的时间
select sysdate,sysdate - interval '7' MINUTE from dual
当前时间减去7小时的时间
select sysdate - interval '7' hour from dual
当前时间减去7天的时间
select sysdate - interval '7' day from dual
当前时间减去7月的时间
select sysdate,sysdate - interval '7' month from dual
当前时间减去7年的时间
select sysdate,sysdate - interval '7' year from dual
时间间隔乘以一个数字
select sysdate,sysdate - 8 *interval '2' hour from dual

2.日期到字符操作

select sysdate,to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' ) from dual

select sysdate,to_char(sysdate, 'yyyy-mm-dd hh:mi:ss' ) from dual

select sysdate,to_char(sysdate, 'yyyy-ddd hh:mi:ss' ) from dual

select sysdate,to_char(sysdate, 'yyyy-mm iw-d hh:mi:ss' ) from dual

参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)

3. 字符到日期操作

select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual

具体用法和上面的to_char差不多。

4. trunk/ ROUND函数的使用

select trunc(sysdate , 'YEAR' ) from dual

select trunc(sysdate ) from dual

select to_char(trunc(sysdate , 'YYYY' ), 'YYYY' ) from dual

2、层次查询（Hierarical Retrival)

SELECT [LEVEL],column,expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];

前提条件：

数据表中包含ID1 和ID2 两个字段，这两个字段转换成树形的上下级关系，

如ID2是ID1的父节点，以此类推，可画成树形图

总结：
1.其中level关键字是可选的，level是层次查询的一个伪列，如果有level，必须有connect by,start with可以没有，level不可以前面加表名。
2.From之后可以是table,view 但是只能是一个table。
3.Where条件限制了查询返回的行，但是不影响层次关系，不满足条件的节点不返回。

4.Start with是表示开始节点，如果没有start with,则每行都当作root。Start with后面可以使用子查询，可以带多个条件。

5.Connect by与prior 确定一个层次查询的条件和遍历的方向(prior 确定)。

start with column_1=xxx

Connect by prior column_1=column_2; (其中prior表示以哪个字段为查询条件),

解释执行方式： 1.start with 语句表示从数据表中找到符合条件的那一行数据，并且以该行数据为起点； 2. connect by prior主要关注与prior同侧的字段，此处为column_1，所以含义是查询数据表中（树形结构中）所有column_2为步骤1中column_1的值；（解释为以column_1的值为条件，查找column_2中为column_1值得数据项）；

如果Connect by column_1= prior column_2;则为，在步骤1中结果中，以column_2为查询条件，查找所有column_1中等于column_2值的数据；

当步骤2完成后，再以此递归，以步骤2的结果集作为下次的递归的初始数据集。

举例：

第一种情况：

第一种：start with 子节点ID='...' connect by prior 子节点ID = 父节点ID

select * from mdm_organization o start with o.org_code='10000008' connect by prior o.org_code=o.org_parent_code

按照条件org_code='10000008'，对'10000008'（包括自己）及其子节点进行递归查询，结果如下

查询结果自己所有的后代节点（包括自己）。

第二种情况：

第二种：start with 子节点ID='...' connect by 子节点ID = prior 父节点ID

select * from mdm_organization o start with o.org_code='10000008' connect by o.org_code=prior o.org_parent_code

按照条件org_code='10000008'，对'10000008'（包括自己）及其父节点进行递归查询，结果如下

查询结果自己所有的前代节点（包括自己）。

第三种情况：

第三种：start with 父节点ID='...' connect by prior 子节点ID = 父节点ID

1	`select` `*` `from` `mdm_organization o start` `with` `o.org_parent_code='10000008'` `connect` `by` `prior` `o.org_code=o.org_parent_code`

　按照条件org_parent_code='10000008'，对'10000008'（不包括自己）子节点进行递归查询，结果如下

　查询结果自己所有的后代节点（不包括自己）。

第四种情况：

第四种：start with 父节点ID='...' connect by 子节点ID = prior 父节点ID

1	`select` `*` `from` `mdm_organization o start` `with` `o.org_parent_code='10000008'` `connect` `by` `o.org_code =` `prior` `o.org_parent_code`

按照条件org_parent_code='10000008'，对'10000008'（包括自己）的第一代孩子们及其父节点进行递归查询，结果如下

　查询结果自己的第一代后节点和所有的前代节点（包括自己）。

　如果有where 条件，如下

　select * from mdm_organization o where 条件 start with o.org_parent_code='10000008' connect by o.org_code = prior o.org_parent_code

　执行顺序为先执行start with connect by prior，然后再按照where条件进行过滤。

Oracle 层级查询 connect by prior再理解

Oracle的层级查询将数据表以树形结构进行查询与整合，其中树形结构可以理解为目录式树结构或者二叉树式树结构，
因为一直对prior 指向的方向无法通过字义理解，所以只能每次都重新翻阅资料，2022.6.30发现，可以将其理解为递归二叉树形式，
其中prior所指的列，为递归起始列，具体解释与说明如下

1.connect by 语句是将表内数据通过递归方式以树的形式整合与展示，其中start with的作用是指定数的根节点，如果不指定start with条件
层级语句将依次将每条记录都视为根节点生成一颗对应的树进行展示，所以start with的作用只是指定一个起始根节点，其条件可以任意指定，
不会直接影响connect by prior的效果

2. ****重点***** prior指定的列有什么含义？？通过start with选择一列作为递归树的根节点，那这颗树该以什么形式向下生长呢，
这时就用到了prior 关键字，例： connect by prior child_col = parent_col
其中prior指定根节点的 child_col 作为生长字段，寻找其他数据行中 parent_col字段与其值相同的节点作为根节点的子节点，依次类推。
可以理解为，以child_col的值为起点，寻找所有parent_col与其相同的节点，作为子节点
根节点--parent_col--child_col
|
子节点1--parent_col--child_col
子节点1--parent_col--child_col

如果connect by child_col = prior parent_col
则图例解释为其中prior指定根节点的 parent_col 作为生长字段，寻找其他数据行中 child_col字段与其值相同的节点作为根节点的子节点，依次类推。
根节点--parent_col--child_col
|
子节点1--parent_col--child_col
子节点1--parent_col--child_col

测试用例

with tbl as
(
select 'A' as parent_cd, 'C' as child_cd from dual
union all
select 'A' as parent_cd, 'D' as child_cd from dual
union all
select 'C' as parent_cd, 'B' as child_cd from dual
union all
select 'D' as parent_cd, 'E' as child_cd from dual
union all
select 'C' as parent_cd, 'F' as child_cd from dual
union all
select 'B' as parent_cd, 'H' as child_cd from dual
)
select level, tbl.* from tbl
connect by child_cd = prior parent_cd
--start with parent_cd = 'B'
order by level, parent_cd;