如何在Netezza中使用自连接查找父记录 [英] How to find the parent record using self join in Netezza

问题描述

我有一个表，其中的键(即数字)为org_key和par_org_key

I have a table with keys (i.e just numbers) as org_key and par_org_key

Org_key | Par_Org_key
52      26
23      89
26      14
14      8

在上表中，org_key 52的父代为26，org_key 26的父代为14，依此类推 上述系列的根父级为8(因为未在org_key列中列出). 我需要找到这样的根，并在单个行上具有完整的链接，例如:8 | 14 | 26 | 52.

In the above tablem the parent of org_key 52 is 26, the parent of org_key 26 is 14 an so on The root parent for the above series is 8 (as it is not listed in the org_key column). I need to find such roots and have the complete link eg: 8|14|26|52 on a single row.

此SQL在Oracle中运行:

This SQL is working in Oracle:

select 
      substr(sys_connect_by_path(org_nbr,'|'),2)  spa
     ,substr(sys_connect_by_path(org_key,'|'),2)  org_key_line
     ,connect_by_root(org_key)                    org_key_root
     ,level                                       lvl
     ,org.*
from org
start with par_org_key is null
connect by par_org_key = prior org_key 

我的问题是，如何使用Netezza SQL进行相同的查询?

My question is, how I can do the same query using Netezza SQL?

推荐答案

Netezza不支持递归通用表表达式，否则它将是更优雅和可扩展的解决方案.我发现最好的解决方法是对同一张表使用左外部联接.

Netezza doesn't support recursive Common Table Expressions otherwise that would be the more elegant and scalable solution. I've found the best workaround is to use Left Outer Joins to the same table.

创建一个测试表.我们用-1表示根节点.

Create a test table. We use -1 to denote the root node.

CREATE TABLE t1 AS
SELECT 52 AS Org_key, 26 AS Par_Org_key
UNION
SELECT 23 AS Org_key, 89 AS Par_Org_key
UNION
SELECT 26 AS Org_key, 14 AS Par_Org_key
UNION
SELECT 14 AS Org_key, 8 AS Par_Org_key
UNION
SELECT 8 AS Org_key, -1 AS Par_Org_key;

这将返回8 | 14 | 26 | 52

This returns 8|14|26|52

SELECT NVL(a.Org_key,'0') || '|' || NVL(b.Org_key,'0') || '|' || NVL(c.Org_key,'0') || '|' || NVL(d.Org_key,'0')
FROM t1 a
LEFT OUTER JOIN t1 b ON a.Org_key = b.Par_Org_key
LEFT OUTER JOIN t1 c ON b.Org_key = c.Par_Org_key
LEFT OUTER JOIN t1 d ON c.Org_key = d.Par_Org_key
LEFT OUTER JOIN t1 e ON d.Org_key = e.Par_Org_key
WHERE a.Par_Org_key = -1;

如果您想添加更多的左外部联接以支持未知数量的级别，则可以帮助处理导致的空值

If you want to add more Left Outer Joins to support an unknown number of levels, this can help handle the nulls that result

SELECT NVL(CAST(a.Org_key AS VARCHAR(10)),'') || '|' || NVL(CAST(b.Org_key AS VARCHAR(10)),'') || '|' || NVL(CAST(c.Org_key AS VARCHAR(10)),'') || '|' || NVL(CAST(d.Org_key AS VARCHAR(10)),'')

这篇关于如何在Netezza中使用自连接查找父记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！