sql查询查找包含同一Oracle表中其他记录的ID的记录

I need to run a SQL query against an Oracle 11 database that will retrieve records where one record itself has values under separate columns which hold the auto-generated ID(s) of one or more other records in the same table.

我需要针对Oracle 11数据库运行SQL查询,该数据库将检索记录,其中一个记录本身在单独的列下具有值,这些列保存同一表中一个或多个其他记录的自动生成的ID。

So, the records in the table would look something like this:

所以,表中的记录看起来像这样:

| FOREIGN_PARTY_ID | ORG_NAME | RELATED_PARTY_ID1 | RELATED_PARTY_ID2 | ...
| 1001             | null     | null              | null              | ...
| 1002             | null     | null              | null              | ...
| 1003             | null     | null              | null              | ...
| 1004             | null     | null              | null              | ...
| 1005             | ABC, INC. | 1001             | 1002              | 1003      | 1004

1 个解决方案

#1

This query will return all rows in your table, which contains an auto-generated ID from FOREIGN_PARTY_ID in at least one RELATED_PARTY_IDn field:

此查询将返回表中的所有行,其中包含至少一个RELATED_PARTY_IDn字段中来自FOREIGN_PARTY_ID的自动生成的ID:

SELECT
  t.*, 
  t1.*, /* actually, you can specify only fields you need */
  t2.*, 
  ...,
  tn.*
FROM YOUR_TABLE_NAME t
LEFT JOIN YOUR_TABLE_NAME t1 ON t1.RELATED_PARTY_ID1 = t.FOREIGN_PARTY_ID
LEFT JOIN YOUR_TABLE_NAME t2 ON t2.RELATED_PARTY_ID2 = t.FOREIGN_PARTY_ID
...
LEFT JOIN YOUR_TABLE_NAME tn ON tn.RELATED_PARTY_IDn = t.FOREIGN_PARTY_ID
WHERE
    t1.FOREIGN_PARTY_ID IS NOT NULL 
 OR t2.FOREIGN_PARTY_ID IS NOT NULL 
 OR ...
 OR tn.FOREIGN_PARTY_ID IS NOT NULL

If you want to get only the rows which contains your IDs in EVERY RELATED_PARTY_IDn field, you can change OR in the WHERE conditions by AND.

如果只想在EVERY RELATED_PARTY_IDn字段中获取包含ID的行,则可以通过AND更改WHERE条件中的OR。

#1