找回密码
 立即注册

QQ登录

只需一步,快速开始

sql问题,层级关系

根据传入的参数获取末级,如图,传入 010399 则返回结果 010302,010303,010304

IMG_20220814_115647.jpg

FineReport 帆软用户Oiytcni8LW 发布于 2022-8-14 13:49 (编辑于 2022-8-14 14:01)
1min数据分析任务体验问卷 立即参与
回答问题
悬赏:3 F币 + 添加悬赏
提示:增加悬赏、完善问题、追问等操作,可使您的问题被置顶,并向所有关注者发送通知
共2回答
最佳回答
1
CD20160914Lv7资深互助
发布于2022-8-14 15:45

with  tmp as (

select '010399' as parent_id,'010398' as sub_id from dual union all

select '010398' as parent_id,'010302' as sub_id from dual union all

select '010398' as parent_id,'010303' as sub_id from dual union all

select '010398' as parent_id,'010304' as sub_id from dual union all

select '010199' as parent_id,'010198' as sub_id from dual union all

select '010199' as parent_id,'010288' as sub_id from dual ) 

select 

a.parent_id,

b.sub_id

 from tmp a,

 tmp b 

 where a.parent_id='010399'

 and a.sub_id=b.parent_id

 

image.png

image.png

  • 帆软用户Oiytcni8LW 帆软用户Oiytcni8LW(提问者) 这个写法只支持三层的,三层以上不适用,比如传 010199 就关联不出来
    2022-08-14 15:55 
  • CD20160914 CD20160914 回复 帆软用户Oiytcni8LW(提问者) 什么意思?我上面查找出来的才3层看仔细
    2022-08-14 15:57 
最佳回答
0
吾姓独孤Lv6高级互助
发布于2022-8-14 13:55(编辑于 2022-8-14 14:31)

这能查出所有010399下面的子级,你再做个过滤把010399,010398那条去掉

WITH TABLE1 AS (

SELECT '010399' AS GRADE1 , '010398' AS GRADE2 FROM DUAL UNION ALL

SELECT '010398' AS GRADE1 , '010302' AS GRADE2 FROM DUAL UNION ALL

SELECT '010398' AS GRADE1 , '010303' AS GRADE2 FROM DUAL UNION ALL

SELECT '010398' AS GRADE1 , '010304' AS GRADE2 FROM DUAL UNION ALL

SELECT '010199' AS GRADE1 , '010198' AS GRADE2 FROM DUAL UNION ALL

SELECT '010199' AS GRADE1 , '010298' AS GRADE2 FROM DUAL UNION ALL

SELECT '010198' AS GRADE1 , '010101' AS GRADE2 FROM DUAL UNION ALL

SELECT '010198' AS GRADE1 , '010201' AS GRADE2 FROM DUAL UNION ALL

SELECT '019998' AS GRADE1 , '010199' AS GRADE2 FROM DUAL

)

SELECT

*

FROM

TABLE1

START WITH

GRADE1 = '010399' -- 参数

CONNECT BY

PRIOR GRADE2 = GRADE1

  • 2关注人数
  • 323浏览人数
  • 最后回答于:2022-8-14 15:45
    请选择关闭问题的原因
    确定 取消

    联系社区管理员|联系帆软|《帆软社区协议》|手机版|帆软社区|Copyright © 帆软软件有限公司 ( 苏ICP备18065767号-7 )

    GMT+8, 2022-9-27 09:50 , Processed in 0.403053 second(s), 56 queries , Gzip On.

    返回顶部