如何在层次结构中使用oracle查询获得此结果

Question

首先，一个抵押品，你可以从银行多次借款。这意味着一个collateral_num有多个loan_num。

第二，你可以为一笔贷款提供多个抵押品。这意味着一个loan_num有多个collateral_num。

有这样一个名为LOAN的表

<>之前Collateral_num | loan_num-------------------------C1 | a5C2 | a5C2 | a1C3 | a1C3 | a3[qh][au:] a7之前

我想从LOAN获得最小collateral_num，如下表所示。我将解释最小值cloateral_num。第一张桌子上有两组。其中一个是从第一行到第五行。然后，我们可以在第一组中找到类似于C1→a5→c2→a1→c3→a3的关系。我们去掉包含a的节点。因此c1是c1 c2 c3中的最小抵押品数。(因为c1的个数最少)

第二组(c6→a7→c7)最小抵押品数为c6。

<>之前Collateral_num | minimum_collateral_num | loan_num--------------------------------------------------C1 | C1 | a5C2 | c1 | a5C2 | c1 | a1C3 | c1 | a1C3 | c1 | a3C6 | C6 | a7C7 | c6 | a7之前

我如何使SQL查询使结果我已经告诉。任何意见都会对我有帮助。谢谢。

Answer 1

我想我已经设法用解析函数做到了这一点(不优雅，但尽管如此)-

with
grps as(
    select marker
    from(
        select collateral_num,
               loan_num,
               rownum as marker,
               case when lead(loan_num,1) over (order by collateral_num) <> loan_num
                         and lead(collateral_num,1) over (order by collateral_num) <> collateral_num
                    then 1
                    when lead(loan_num,1) over (order by collateral_num) is null
                    then 1
                    else null end as grp
          from  loan
         order by collateral_num
        )
    where grp is not null
       )
, marked as(
    select loan.*, grps.*, row_number() over(partition by marker order by collateral_num) as splitter
    from loan, grps
           )
select  collateral_num,
        min(collateral_num) over (partition by marker) as min_collateral_num,
        loan_num
from    marked y
where   splitter <= marker
    and (splitter > (select max(x.marker) from marked x where x.marker < y.marker)
     or marker = (select min(marker) from marked))

参见sqlfiddle at http://sqlfiddle.com/#!4/cfb1a7/40/0