如何在Oracle SQL中选择相关的项目组

I have some data of the form

我有一些表格的数据

Key    ID      Link       
1      MASTER  123
2      AA      123
3      AA      123
4      BB      123
5      MASTER  456
6      CC      456

I would like to be able to select in the same select all linked items matching the selection criteria, plus the linked master. For example, if I have an ID of 'AA', I want the rows with ID = 'AA' to be returned, plus the row with ID = 'MASTER' and a link of 123:

我希望能够在同一个选择中选择与选择标准匹配的所有链接项目以及链接主文件。例如,如果我的ID为'AA',我希望返回ID ='AA'的行,加上ID ='MASTER'的行和123的链接:

1      MASTER  123
2      AA      123
3      AA      123

I'm using Oracle 10.2g, so if any special Oracle syntax will make this easier, then that would be ok.

我正在使用Oracle 10.2g,所以如果任何特殊的Oracle语法会使这更容易,那就没关系。

4 个解决方案

#1

Here's one method.

这是一种方法。

SELECT DISTINCT key, id, link
  FROM the_table
  START WITH id = 'AA'
  CONNECT BY id = 'MASTER' and link = PRIOR link and 'AA' = PRIOR ID

#2

SELECT * FROM table_name WHERE ID=your_id UNION ALL SELECT * FROM table_name WHERE ID='MASTER' AND link = (SELECT link FROM table_name WHERE ID=your_id)

SELECT * FROM table_name WHERE ID = your_id UNION ALL SELECT * FROM table_name WHERE ID ='MASTER'AND link =(SELECT link FROM table_name WHERE ID = your_id)

That should answer to the question I understood ;)

这应该回答我理解的问题;)

#3

What i understand is that you what all the records that have the same "link" as the one with id = AA firts you need the link field:

我所理解的是,你所有的记录与id = AA firts具有相同“链接”的所有记录都需要链接字段:

select i.link from table i
where i.id = YOUR_ID

now you "select" the records that have that link

现在您“选择”具有该链接的记录

select * from table 
where link in (select i.link from table i where i.id = YOUR_ID)

This select should give you the records you need...

这个选择应该给你你需要的记录......

#4

If I understand correctly, here's an example I think does what you are looking for:

如果我理解正确,这里有一个例子我认为你正在寻找的是:

select * from my_table where link in
(select link
from my_table
where id = 'AA')
and id in ('AA','MASTER')

#1