oracle - Oracle SYS_CONNECT_BY_PATH 无法正常工作(始终为空)
问题描述
我对这种奇怪的行为很生气。
我正在使用 2 个 SYS_CONNECT_BY_PATH 查询,但一个总是为空,而我验证了有值。
目标是将多行合并为一个,用逗号分隔。
这是工作的:
SELECT id_audit_req, SUBSTR(MAX(SYS_CONNECT_BY_PATH (profs_names, ', ')), 3) all_descriptions
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY id_audit_req ORDER BY id_audit_req, profs_names) rnum, id_audit_req, profs_names
FROM (SELECT id_audit_req, nvl(p.nick_name, p.name) profs_names
FROM audit_req_prof arp, professional p
WHERE arp.flg_rel_type = 'A'
AND arp.id_professional = p.id_professional
)
)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR id_audit_req = id_audit_req
GROUP BY id_audit_req
使用此查询,内部(在 SYS_CONNECT_BY_PATH 之前)SQl 结果是
rnum id_audit_req profs_names
1 1 Auditor
1 501 Auditor
1 502 Auditor
2 502 Auditor2
1 503 Auditor
1 504 Auditor
1 505 Auditor
2 505 Auditor2
最后的结果是对的:
id_audit_req all_descriptions
1 Auditor
504 Auditor
502 Auditor, Auditor2
505 Auditor, Auditor2
503 Auditor
501 Auditor
现在我又查询了,内部结果也是对的,但是最终结果是null,不知道为什么:
SELECT id_epis_triage, SUBSTR (MAX(SYS_CONNECT_BY_PATH (bp, ', ')), 3) all_descriptions
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY id_epis_triage ORDER BY id_epis_triage, bp desc) rnum, id_epis_triage, bp
FROM (SELECT id_epis_triage, vsr.VALUE as bp
FROM vital_sign_read vsr, vital_sign vs
WHERE vsr.id_epis_triage in (SELECT arpe.id_epis_triage FROM audit_req_prof_epis arpe
WHERE arpe.id_audit_req_prof = 2)
AND vsr.flg_state = 'A'
AND vsr.id_vital_sign IN (SELECT id_vital_sign_detail FROM vital_sign_relation WHERE relation_domain = 'C')
AND vsr.dt_vital_sign_read in
(SELECT vsr2.dt_vital_sign_read
FROM vital_sign_read vsr2
WHERE (vsr2.id_epis_triage, vsr2.dt_vital_sign_read) IN
(SELECT id_epis_triage, MAX(vsr4.dt_vital_sign_read)
FROM alert.vital_sign_read vsr4
WHERE vsr4.id_epis_triage in (SELECT arpe.id_epis_triage FROM audit_req_prof_epis arpe
WHERE arpe.id_audit_req_prof = 2)
AND vsr4.id_vital_sign = vsr.id_vital_sign
AND vsr4.flg_state = 'A'
group by id_epis_triage)
AND vsr2.id_vital_sign = vsr.id_vital_sign
AND vsr2.id_vital_sign IN (SELECT id_vital_sign_detail
FROM vital_sign_relation
WHERE relation_domain = 'C'
AND vsr2.id_epis_triage in (SELECT arpe.id_epis_triage FROM audit_req_prof_epis arpe
WHERE arpe.id_audit_req_prof = 2)
AND id_vital_sign_parent = 28)
AND vsr2.flg_state = 'A')
AND vs.id_vital_sign = vsr.id_vital_sign
ORDER BY vs.intern_name_vital_sign
)
)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 and PRIOR id_epis_triage = id_epis_triage
group by id_epis_triage
内部结果:
rnum id_epis_triage bp
1 2134 120
2 2134 85
1 2137 112
2 2137 98
最后结果:
空值
有人知道吗?我尝试了很多变化,几乎总是相同的结果。只有当我删除 START WITH 子句时,我才得到:
id_epis_triage all_descriptions
2134 85
2137 98
这是,似乎它只获得每个 rnum 的最后一个值。但是,本质上,两个 SQL 是相同的,结构是完全一样的。
解决方案
它似乎对我有用(我在 11.2.0.4):
WITH inner_result AS (SELECT 1 rnum, 2134 id_epis_triage, 120 bp FROM dual UNION ALL
SELECT 2 rnum, 2134 id_epis_triage, 85 bp FROM dual UNION ALL
SELECT 1 rnum, 2137 id_epis_triage, 112 bp FROM dual UNION ALL
SELECT 2 rnum, 2137 id_epis_triage, 98 bp FROM dual)
SELECT id_epis_triage,
substr(MAX(sys_connect_by_path(bp, ', ')), 3) all_descriptions
FROM inner_result
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1
AND PRIOR id_epis_triage = id_epis_triage
GROUP BY id_epis_triage;
ID_EPIS_TRIAGE ALL_DESCRIPTIONS
-------------- ----------------
2137 112, 98
2134 120, 85
您使用的是什么版本,上述查询(原样,包括 inner_result 子查询)是否也适合您?
推荐阅读
- ios - 音频随机中断,必须拔掉耳机
- python - 在 Python 和 BeaufifulSoup 中抓取 div 类下的子类
- amazon-web-services - 在 AWS API Gateway 中分离环境的推荐方法是什么?
- c# - 为什么我的 Visual Studio Community 2019 在“调试”>“Windows”下没有“模块”选项以及如何修复它?
- google-apps-script - 如何使用 Google Apps 脚本 (GAS) 创建多个驱动器文件夹
- javascript - Chrome调度KeyboardEvent不起作用
- php - 带有代理到 https 链接的 php curl 请求
- playframework - 当我从集群中删除成员时,Infinispan 8.0.1 崩溃
- python-3.x - OpenCV 分类器错误:(-215) !empty() in function detectMultiScale
- java - 静态初始化,main和premain的执行顺序