基于其他列的Oracle查询

Question

我有一个表:

CUST_ITEM
ITEM   LINE_NO   LINK_LINE_NO
100      1          null
200      2          1
300      3          null

我想把结果作为:

ITEM   LINE_NO   TYPE          REF_ITEM    REF_LINE_NO 
100      1       Main             200         200
200      2       Linked           100         100
300      3       Independent      null        null

Item: 100是Main Item，因为Item: 200附加在它上面Item: 200是一个链接项，因为它附加到Item: 100项目:300是独立的，不是Main或Linked

怎么做呢?

Answer 1

对于您发布的示例数据，一个选项可能是这样的(阅读代码中的注释)。

SQL> WITH
2     cust_item (item, line_no, link_line_no)
3     AS
4  -- sample data
5        (SELECT 100, 1, NULL FROM DUAL
6         UNION ALL
7         SELECT 200, 2, 1 FROM DUAL
8         UNION ALL
9         SELECT 300, 3, NULL FROM DUAL),
10     temp
11     AS
12  -- find main and linked items
13        (SELECT a.item,
14                a.line_no,
15                (SELECT MIN (b.item)
16                   FROM cust_item b
17                  WHERE b.link_line_no = a.line_no) linked_item,
18                (SELECT MIN (c.item)
19                   FROM cust_item c
20                  WHERE c.line_no = a.link_line_no) main_Item
21           FROM cust_item a)
22  -- final result
23    SELECT t.item,
24           t.line_no,
25           CASE
26              WHEN t.linked_item IS NOT NULL THEN 'Main'
27              WHEN t.main_item IS NOT NULL THEN 'Linked'
28              ELSE 'Independent'
29           END TYPE,
30           COALESCE (t.linked_item, t.main_item) ref_item
31      FROM temp t
32  ORDER BY t.line_no
33  /

---

ITEM    LINE_NO TYPE          REF_ITEM
---------- ---------- ----------- ----------
100          1 Main               200
200          2 Linked             100
300          3 Independent
SQL>

然而，如果有更多的项目"链接"，结果会是什么?对彼此?

Answer 2

select tab1.item, tab1.line_no, nvl(rel.type, 'Independent'), rel.to_item from tab1
left join
(
select t1.item, 'Main' as Type, t2.item to_item from tab1 t1, tab1 t2 where t1.LINE_NO = t2.LINK_LINE_NO
union
select t2.item, 'Linked' Type, t1.item from_item from tab1 t1, tab1 t2 where t1.LINE_NO = t2.LINK_LINE_NO
) rel
on tab1.item = rel.item

你可以在这里查看

Answer 3

下面没有放入"main"的引用，因为不清楚如果有多个匹配会发生什么。

其余部分可以使用:

select ci.*,
(case when ci.link_line_no is not null then 'linked'
when cil.line_no is not not null then 'main'
else 'independent'
end) as type,
cil.item as ref_item,
cil.item as ref_line_no
from cust_item ci left join
cust_item cil
on ci.link_line_no = cil.line_no;