Oracle查询排序端口和标签 [英] Oracle query sorting ports and labels

问题描述

我不知道如何进行oracle查询以执行以下操作:

I can't figure out how to make an oracle query to do the following:

仅索引端口ID，所有端口左右连接. 在开始时，仅知道PORTID_A数字1，因此通过一个简单的查询，我找到了PORTID_B. 但是端口有时从A开始，有时从B开始.如下所示:

Only the port id's are indexed, all ports are connected left and right. In the beginning only PORTID_A number 1 is known, so by a simple query i find PORTID_B. But the ports sometimes start on A and sometimes on B. Like below:

PORTID_A    PORTID_B    LABEL
1           2           label-01
2           4           label-02
3           4           label-03
6           3           label-04
5           6           label-05  

如何获取创建以下内容的查询结果:

How can get a query result that creates the following:

1 <-- label-01 --> 2 <-- label-02 --> 4 <-- label-03 --> 3   etc.....

为了在下面的表中提供更好的示例，对PORTID_A和PORTID_B进行了索引.

To give a better example from the tabel below, PORTID_A and PORTID_B are indexed.

1. 通过对PORTID_A = 7742140的查询，我可以找到PORTID_B，但是下一个查询PORTID_B = 4298763， 得到2条结果行，其中一条已在第一个查询中找到，而新条已经在新查询中找到.
2. 我如何才能获得唯一的搜索结果，就像通过al循环一样，以正确的顺序获得唯一的portlabel，例如 就像结果"示例

1. Via a query on PORTID_A = 7742140, i can find PORTID_B, but the next query PORTID_B = 4298763, get 2 result lines, one which already found on the first query and the new one.
2. How can i get unique search results as a kind of loop through al to get unique portlabels in correct order like like the Result example

Table:
------------------------------------------------------------------------------------------  
PORTID_A    PORTID_B    PORTLABEL_A                         PORTLABEL_B
7742140     4298763     00005-ERS10-001/00-0/10-04/1Rx      00005-ODF03-006/06-0/08-0/08
4298763     6853090     00005-ODF03-006/06-0/08-0/08        00005-ODF03-004/02-0/08-0/11
6853497     6853090     00034-ODF03-002/07-0/08-0/11        00005-ODF03-004/02-0/08-0/11
1381299     6853497     00034-ODF03-002/05-0/06-0/03        00034-ODF03-002/07-0/08-0/11
1374115     1381299     00034-ODF03-005/05-0/06-0/03        00034-ODF03-002/05-0/06-0/03
1374115     186966      00034-ODF03-005/05-0/06-0/03        00034-ODF03-005/07-0/04-0/07
24919318    186966      20998-ODF05-002/00-0/01-0/03        00034-ODF03-005/07-0/04-0/07
24919351    24919318    20998-ESU02-001/00-0/00-25/1Tx      20998-ODF05-002/00-0/01-0/03


Result example:
------------------------------------------------------------------------------------------
00005-ERS10-001/00-0/10-04/1Rx
00005-ODF03-006/06-0/08-0/08
00005-ODF03-004/02-0/08-0/11
00034-ODF03-002/07-0/08-0/11
00034-ODF03-002/05-0/06-0/03
00034-ODF03-005/05-0/06-0/03
00034-ODF03-005/07-0/04-0/07
20998-ODF05-002/00-0/01-0/03
20998-ESU02-001/00-0/00-25/1Tx

推荐答案

要连接它们，我首先要解决portB可以低于portA的问题:

To connect them, I would first solve the problem that portB can be lower than portA:

select
  least(PortID_A, PortID_B) as PortID_A,
  greatest(PortID_A, PortID_B) as PortID_B,
  Label
from
  YourTable

之后，您可以通过端口ID连接它们:

After that, you can connect them by port id:

select
  l.PortID_A,
  l.PortID_B,
  l.Label
from 
  ThatQueryFromAbove l
start with
  l.PortID_A = 1
connect by
  l.PortID_A = prior l.PortID_B;

最后，您可以将其汇总以获得结果:

Finally, you can aggregate that to get your result:

with 
  VW_LABELS as
    (select
      least(PortID_A, PortID_B) as PortID_A,
      greatest(PortID_A, PortID_B) as PortID_B,
      Label
    from
      YourTable),

  VW_CHAINLINKS as
    (select
      l.PortID_A,
      l.PortID_B,
      l.Label
    from 
      VW_LABELS l
    start with
      l.PortID_A = 1
    connect by
      l.PortID_A = prior l.PortID_B)

select
  '1' || LISTAGG (
      ' <-- ' || c.Label || ' --> ' || c.PortID_B,
      ''
  ) WITHIN GROUP (order by c.PortID_A)
from
  VW_CHAINLINKS c

当然，您可以结合使用以下两个步骤来进行较短的查询，但是要了解不同的短钉，最好将它们分开. with语句很容易编写与上一个查询的结果一起使用的简单，简短的查询.

Of course you can combine a couple of these steps to make a shorter query, but to understand the different staps it might be a good idea to keep them separate. The with statement is handy to write simple, short queries that work with the result of a previous query.

这篇关于Oracle查询排序端口和标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！