系统中 有一个表 的结构是二叉树类型的 。
现在 我想以起点开始 ,找出每一层左右两区 一个时间点最新的节点如图中所示,那么对于A来说,他的第一层左边为B节点,右边C节点,C节点就是我要的节点;
他的第二层左边是D、E ,右边是F、G,E和F就是我想要的节点, 请教如何用SQL查找with t as (select 1 as user_id, null as father_id,2 as line,'2015/12/24 11:18:00' as time,1 as levels from dual union all
select 2 as user_id,1 as father_id,1 as line,'2015/12/24 11:19:00' as time,2 as levels from dual union all
select 3 as user_id,1 as father_id,2 as line,'2015/12/24 11:19:00' as time,2 as levels from dual union all
select 4 as user_id,2 as father_id,1 as line,'2015/12/24 11:19:00' as time,3 as levels from dual union all
select 7 as user_id,3 as father_id,2 as line,'2015/12/24 11:20:00' as time,3 as levels from dual union all
select 6 as user_id,3 as father_id,1 as line,'2015/12/24 11:20:00' as time,3 as levels from dual union all
select 5 as user_id,2 as father_id,2 as line,'2015/12/24 11:20:00' as time,3 as levels from dual union all
select 8 as user_id,4 as father_id,1 as line,'2015/12/24 11:24:00' as time,4 as levels from dual union all
select 15 as user_id,7 as father_id,2 as line,'2015/12/24 11:26:00' as time,4 as levels from dual union all
select 14 as user_id,7 as father_id,1 as line,'2015/12/24 11:26:00' as time,4 as levels from dual union all
select 13 as user_id,6 as father_id,2 as line,'2015/12/24 11:26:00' as time,4 as levels from dual union all
select 12 as user_id,6 as father_id,1 as line,'2015/12/24 11:26:00' as time,4 as levels from dual union all
select 11 as user_id,5 as father_id,2 as line,'2015/12/24 11:25:00' as time,4 as levels from dual union all
select 10 as user_id,5 as father_id,1 as line,'2015/12/24 11:25:00' as time,4 as levels from dual union all
select 9 as user_id,4 as father_id,2 as line,'2015/12/24 11:25:00' as time,4 as levels from dual union all
select 29 as user_id,8 as father_id,1 as line,'2017/02/16 18:23:00' as time,5 as levels from dual union all
select 3449 as user_id,14 as father_id,1 as line,'2016/02/24 18:37:00' as time,5 as levels from dual union all
select 30 as user_id,29 as father_id,1 as line,'2017/02/16 18:25:00' as time,6 as levels from dual union all
select 4088 as user_id,3449 as father_id,1 as line,'2017/08/13 12:00:00' as time,6 as levels from dual union all
select 3397 as user_id,3449 as father_id,2 as line,'2017/07/13 18:11:00' as time,6 as levels from dual union all
select 31 as user_id,30 as father_id,1 as line,'2017/02/16 18:26:00' as time,7 as levels from dual union all
select 3396 as user_id,4088 as father_id,2 as line,'2017/07/13 18:07:00' as time,7 as levels from dual union all
select 3395 as user_id,4088 as father_id,1 as line,'2017/07/13 18:07:00' as time,7 as levels from dual union all
select 3419 as user_id,3397 as father_id,2 as line,'2017/07/13 00:00:00' as time,7 as levels from dual union all
select 3388 as user_id,3397 as father_id,1 as line,'2017/07/13 18:23:00' as time,7 as levels from dual union all
select 32 as user_id,31 as father_id,1 as line,'2017/02/16 18:27:00' as time,8 as levels from dual union all
select 4815 as user_id,3396 as father_id,1 as line,'2017/09/26 10:22:00' as time,8 as levels from dual union all
select 4472 as user_id,3396 as father_id,2 as line,'2017/10/16 15:02:00' as time,8 as levels from dual union all
select 3812 as user_id,3395 as father_id,2 as line,'2017/11/16 00:00:00' as time,8 as levels from dual union all
select 3398 as user_id,3395 as father_id,1 as line,'2017/08/07 00:00:00' as time,8 as levels from dual union all
select 3711 as user_id,3419 as father_id,1 as line,'2017/08/10 00:00:00' as time,8 as levels from dual union all
select 33 as user_id,32 as father_id,1 as line,'2017/02/16 18:28:00' as time,9 as levels from dual union all
select 4814 as user_id,4815 as father_id,1 as line,'2017/09/26 10:22:00' as time,9 as levels from dual union all
select 4487 as user_id,4472 as father_id,2 as line,'2017/10/16 22:29:00' as time,9 as levels from dual union all
select 4465 as user_id,4472 as father_id,1 as line,'2017/10/16 16:37:00' as time,9 as levels from dual union all
select 6225 as user_id,3398 as father_id,2 as line,'2018/01/31 17:48:00' as time,9 as levels from dual union all
select 3389 as user_id,3398 as father_id,1 as line,'2017/08/07 00:00:00' as time,9 as levels from dual union all
select 3822 as user_id,3711 as father_id,1 as line,'2017/08/10 00:00:00' as time,9 as levels from dual union all
select 34 as user_id,32 as father_id,2 as line,'2017/02/16 18:28:00' as time,9 as levels from dual union all
select 35 as user_id,33 as father_id,1 as line,'2017/02/16 18:30:00' as time,10 as levels from dual union all
select 4145 as user_id,4814 as father_id,1 as line,'2017/09/26 10:22:00' as time,10 as levels from dual union all
select 4686 as user_id,4487 as father_id,2 as line,'2017/10/20 22:41:00' as time,10 as levels from dual union all
select 4453 as user_id,4487 as father_id,1 as line,'2017/10/16 22:33:00' as time,10 as levels from dual union all
select 4488 as user_id,4465 as father_id,2 as line,'2017/10/16 22:43:00' as time,10 as levels from dual union all
select 4473 as user_id,4465 as father_id,1 as line,'2017/10/16 16:58:00' as time,10 as levels from dual union all
select 6227 as user_id,6225 as father_id,2 as line,'2018/01/31 17:52:00' as time,10 as levels from dual union all
select 6226 as user_id,6225 as father_id,1 as line,'2018/01/31 17:51:00' as time,10 as levels from dual union all
select 38 as user_id,34 as father_id,2 as line,'2017/02/16 18:32:00' as time,10 as levels from dual union all
select 37 as user_id,34 as father_id,1 as line,'2017/02/16 18:31:00' as time,10 as levels from dual )select * from t;
现在 我想以起点开始 ,找出每一层左右两区 一个时间点最新的节点如图中所示,那么对于A来说,他的第一层左边为B节点,右边C节点,C节点就是我要的节点;
他的第二层左边是D、E ,右边是F、G,E和F就是我想要的节点, 请教如何用SQL查找with t as (select 1 as user_id, null as father_id,2 as line,'2015/12/24 11:18:00' as time,1 as levels from dual union all
select 2 as user_id,1 as father_id,1 as line,'2015/12/24 11:19:00' as time,2 as levels from dual union all
select 3 as user_id,1 as father_id,2 as line,'2015/12/24 11:19:00' as time,2 as levels from dual union all
select 4 as user_id,2 as father_id,1 as line,'2015/12/24 11:19:00' as time,3 as levels from dual union all
select 7 as user_id,3 as father_id,2 as line,'2015/12/24 11:20:00' as time,3 as levels from dual union all
select 6 as user_id,3 as father_id,1 as line,'2015/12/24 11:20:00' as time,3 as levels from dual union all
select 5 as user_id,2 as father_id,2 as line,'2015/12/24 11:20:00' as time,3 as levels from dual union all
select 8 as user_id,4 as father_id,1 as line,'2015/12/24 11:24:00' as time,4 as levels from dual union all
select 15 as user_id,7 as father_id,2 as line,'2015/12/24 11:26:00' as time,4 as levels from dual union all
select 14 as user_id,7 as father_id,1 as line,'2015/12/24 11:26:00' as time,4 as levels from dual union all
select 13 as user_id,6 as father_id,2 as line,'2015/12/24 11:26:00' as time,4 as levels from dual union all
select 12 as user_id,6 as father_id,1 as line,'2015/12/24 11:26:00' as time,4 as levels from dual union all
select 11 as user_id,5 as father_id,2 as line,'2015/12/24 11:25:00' as time,4 as levels from dual union all
select 10 as user_id,5 as father_id,1 as line,'2015/12/24 11:25:00' as time,4 as levels from dual union all
select 9 as user_id,4 as father_id,2 as line,'2015/12/24 11:25:00' as time,4 as levels from dual union all
select 29 as user_id,8 as father_id,1 as line,'2017/02/16 18:23:00' as time,5 as levels from dual union all
select 3449 as user_id,14 as father_id,1 as line,'2016/02/24 18:37:00' as time,5 as levels from dual union all
select 30 as user_id,29 as father_id,1 as line,'2017/02/16 18:25:00' as time,6 as levels from dual union all
select 4088 as user_id,3449 as father_id,1 as line,'2017/08/13 12:00:00' as time,6 as levels from dual union all
select 3397 as user_id,3449 as father_id,2 as line,'2017/07/13 18:11:00' as time,6 as levels from dual union all
select 31 as user_id,30 as father_id,1 as line,'2017/02/16 18:26:00' as time,7 as levels from dual union all
select 3396 as user_id,4088 as father_id,2 as line,'2017/07/13 18:07:00' as time,7 as levels from dual union all
select 3395 as user_id,4088 as father_id,1 as line,'2017/07/13 18:07:00' as time,7 as levels from dual union all
select 3419 as user_id,3397 as father_id,2 as line,'2017/07/13 00:00:00' as time,7 as levels from dual union all
select 3388 as user_id,3397 as father_id,1 as line,'2017/07/13 18:23:00' as time,7 as levels from dual union all
select 32 as user_id,31 as father_id,1 as line,'2017/02/16 18:27:00' as time,8 as levels from dual union all
select 4815 as user_id,3396 as father_id,1 as line,'2017/09/26 10:22:00' as time,8 as levels from dual union all
select 4472 as user_id,3396 as father_id,2 as line,'2017/10/16 15:02:00' as time,8 as levels from dual union all
select 3812 as user_id,3395 as father_id,2 as line,'2017/11/16 00:00:00' as time,8 as levels from dual union all
select 3398 as user_id,3395 as father_id,1 as line,'2017/08/07 00:00:00' as time,8 as levels from dual union all
select 3711 as user_id,3419 as father_id,1 as line,'2017/08/10 00:00:00' as time,8 as levels from dual union all
select 33 as user_id,32 as father_id,1 as line,'2017/02/16 18:28:00' as time,9 as levels from dual union all
select 4814 as user_id,4815 as father_id,1 as line,'2017/09/26 10:22:00' as time,9 as levels from dual union all
select 4487 as user_id,4472 as father_id,2 as line,'2017/10/16 22:29:00' as time,9 as levels from dual union all
select 4465 as user_id,4472 as father_id,1 as line,'2017/10/16 16:37:00' as time,9 as levels from dual union all
select 6225 as user_id,3398 as father_id,2 as line,'2018/01/31 17:48:00' as time,9 as levels from dual union all
select 3389 as user_id,3398 as father_id,1 as line,'2017/08/07 00:00:00' as time,9 as levels from dual union all
select 3822 as user_id,3711 as father_id,1 as line,'2017/08/10 00:00:00' as time,9 as levels from dual union all
select 34 as user_id,32 as father_id,2 as line,'2017/02/16 18:28:00' as time,9 as levels from dual union all
select 35 as user_id,33 as father_id,1 as line,'2017/02/16 18:30:00' as time,10 as levels from dual union all
select 4145 as user_id,4814 as father_id,1 as line,'2017/09/26 10:22:00' as time,10 as levels from dual union all
select 4686 as user_id,4487 as father_id,2 as line,'2017/10/20 22:41:00' as time,10 as levels from dual union all
select 4453 as user_id,4487 as father_id,1 as line,'2017/10/16 22:33:00' as time,10 as levels from dual union all
select 4488 as user_id,4465 as father_id,2 as line,'2017/10/16 22:43:00' as time,10 as levels from dual union all
select 4473 as user_id,4465 as father_id,1 as line,'2017/10/16 16:58:00' as time,10 as levels from dual union all
select 6227 as user_id,6225 as father_id,2 as line,'2018/01/31 17:52:00' as time,10 as levels from dual union all
select 6226 as user_id,6225 as father_id,1 as line,'2018/01/31 17:51:00' as time,10 as levels from dual union all
select 38 as user_id,34 as father_id,2 as line,'2017/02/16 18:32:00' as time,10 as levels from dual union all
select 37 as user_id,34 as father_id,1 as line,'2017/02/16 18:31:00' as time,10 as levels from dual )select * from t;
解决方案 »
- Oracle Berkely DB 模糊搜索的问题
- oracle参数错误
- oracle 9i exp imp的数据转移问题
- 存储过程中备份表 写法
- 求:将select结果存入文件的例子
- 分区表的问题(高手进)我建了一张表,带分区,条件是假设销量在1000以下的商品信息存在a区,表空间任意,大于1000的商品信息存在b区,表
- 帮我看一下这个JOB错在哪里?每隔7天系统执行p_sysdeleteinfo存储过程。
- sql语句的疑问,字段为空时怎么处理
- 非常严重的问题!! 我的sql语句在存储过程之外运行结果正常,当在存储过程内运行时的结果却是错误的??!!
- 一个奇怪但是简单的问题,目前有点困惑
- SQL优化求解(有关联合主键)
- oci.dll问题
(select user_id,father_id,time,levels,row_number() over(partition by father_id order by time desc) rn
from t where father_id is not null
)
where rn = 1
order by father_id,user_id
看下这个是不是你想要的