如何在层级结构中使用oracle查询获得此结果 [英] how to get this result using oracle query in hierarchical structure

问题描述

首先，只有一笔抵押品，您可以从银行借很多次钱.这意味着一个collat​​eral_num有很多loan_num.

First, with one collateral, you can borrow money many times from bank. It means that one collateral_num have many loan_num.

第二，您可以为一笔贷款提供许多抵押品.这意味着一个借贷编号"具有许多抵押抵押编号".

Second, you can serve many collaterals to one loan. It means that one loan_num have many collateral_num.

有一个名为LOAN的表.

There is a table like this named LOAN.

collateral_num | loan_num
-------------------------
 C1            |  A5
 C2            |  A5
 C2            |  A1
 C3            |  A1
 C3            |  A3
 C6            |  A7
 C7            |  A7

我想从贷款中获得最低的collat​​eral_num，如下表所示. 我将解释最小的clooateral_num. 第一个表中有两个组.其中之一是从第一行到第五行.然后，我们可以在第一组中找到一些关系，例如C1→a5→c2→a1→c3→a3. 并且我们丢弃包含"a"的节点. 结果，c1将是c1 c2 c3中的最小collat​​ral num.(因为c1的数字最少.)

I want to get minimum collateral_num from LOAN like this table below. I will explain minium clooateral_num. There are two group in the first table. One of them is from first row to fifth row. Then, we can find some relationship like C1→a5→c2→a1→c3→a3 in the first group. And we discard the nodes including 'a'. As a result c1 will be minimum collatral num in c1 c2 c3.(because c1 has the least number.)

在第二组(c6→a7→c7)中，最低的胶体编号为c6.

In the second group (c6→a7→c7)minimum collatral num will be c6.

collateral_num | minimum_collateral_num | loan_num
--------------------------------------------------
 C1            |   C1                   |    A5
 C2            |   C1                   |    A5
 C2            |   C1                   |    A1
 C3            |   C1                   |    A1
 C3            |   C1                   |    A3
 C6            |   C6                   |    A7
 C7            |   C6                   |    A7

如何进行SQL查询以使结果告知我.任何评论对我都会有帮助.谢谢.

How do I make SQL query to make the result I've told. Any comment will be helpful for me. Thanks.

推荐答案

我认为我已经设法通过解析函数来做到这一点(虽然不是很优雅，但仍然如此)-

I think I've managed to do this with analytic functions (not elegantly, but nonetheless) -

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，网址为 http://sqlfiddle.com/#!4/cfb1a7/40 /0

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

这篇关于如何在层级结构中使用oracle查询获得此结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！