我快被这种奇怪的行为气疯了。
我正在使用 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 子查询）是否也适用于您？
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句