WITH CTE AS (
SELECT
UNNEST(ARRAY[1,2,3,4,5,6,7,8]) AS NODELEVEL,
unnest(ARRAY[
NULL,
lv1_dept_code,
lv2_dept_code,
lv3_dept_code,
lv4_dept_code,
lv5_dept_code,
lv6_dept_code,
lv7_dept_code
]) AS PNODECODE,
unnest(ARRAY[
lv1_dept_code,
lv2_dept_code,
lv3_dept_code,
lv4_dept_code,
lv5_dept_code,
lv6_dept_code,
lv7_dept_code,
lv8_dept_code
]) AS NODECODE,
unnest(ARRAY[
lv1_dept_cn_name,
lv2_dept_cn_name,
lv3_dept_cn_name,
lv4_dept_cn_name,
lv5_dept_cn_name,
lv6_dept_cn_name,
lv7_dept_cn_name,
lv8_dept_cn_name
]) AS NAME
FROM DBDATAVOICE.DWR_DIM_DV_DEPT_D
WHERE del_flag = 'N'
AND dept_lv BETWEEN 0 AND 9
)
SELECT DISTINCT
NODECODE,
NAME,
NODELEVEL,
PNODECODE
FROM CTE
WHERE NODECODE IS NOT NULL
AND (NODECODE <> PNODECODE OR PNODECODE IS NULL)
ORDER BY NODELEVEL, PNODECODE, NODECODE, NAME;
WITH deduplicated_data AS (
SELECT DISTINCT
dept_code AS nodeCode,
dept_lv AS nodeLevel,
dept_cn_name AS nodeName,
CASE
WHEN dept_lv = 0 THEN NULL -- 0级节点没有父节点
WHEN dept_lv = 1 THEN NULL -- 1级节点也没有父节点（缺少lv0_dept_code）
WHEN dept_lv = 2 THEN lv1_dept_code
WHEN dept_lv = 3 THEN lv2_dept_code
WHEN dept_lv = 4 THEN lv3_dept_code
WHEN dept_lv = 5 THEN lv4_dept_code
WHEN dept_lv = 6 THEN lv5_dept_code
WHEN dept_lv = 7 THEN lv6_dept_code
WHEN dept_lv = 8 THEN lv7_dept_code
WHEN dept_lv = 9 THEN lv8_dept_code
ELSE NULL
END AS pNodeCode
FROM DBDATAVOICE.DWR_DIM_DV_DEPT_D
WHERE dept_lv BETWEEN 0 AND 9
AND del_flag = 'N' -- 确保只处理有效层级
)
SELECT
nodeCode,
nodeName,
nodeLevel,
pNodeCode
FROM deduplicated_data
WHERE nodeCode IS NOT NULL
AND (nodeCode <> pNodeCode OR pNodeCode IS NULL)
ORDER BY nodeLevel, nodeCode;为什么这两段SQL查询的条数不一致
该博客可能围绕 Oracle 数据库中的层次遍历展开，虽未提供具体内容，但推测会涉及相关技术实现、应用场景等信息技术领域知识。
扫一扫
4624
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
