Oracle SQL查询，连接四个表，只获得一行的多个条目

Question

我的表是这样设置的:

Table 1            Table 2                            Table 3        
+-------+-----+    +-------+-------+-------+-----+    +-------+-----+
| ID    | ... |    | ID    | T1_ID | T3_ID | ... |    | ID    | ... |
+-------+-----+    +-------+-------|-------|-----|    |-------|-----|
|  101  | ... |    |  202  |  101  |  301  | ... |    |  300  | ... |
|  102  | ... |    |  203  |  101  |  302  | ... |    |  302  | ... |
|  104  | ... |    |  204  |  101  |  302  | ... |    |  314  | ... |
+-------+-----+    |  205  |  101  |  302  | ... |    +-------+-----+
                   |  206  |  104  |  327  | ... |                   
                   +-------+-------+-------+-----+                   

我想构造一个子查询语句，对于表1的给定id(如果是table2)，它将只选择表2的一行。表3中存在T3_id

重要的一点是，表2中可能存在多个具有相同t3_id的行。因此，表2和表3之间的外键关系不是唯一的，或者根本不存在。

我的想法是这样的:

inner join 
(
    SELECT * 
    FROM (
        SELECT t3_id, t1_id, id 
        FROM table2 
        WHERE EXISTS 
        (
            SELECT id 
            FROM table3
        )
    ) 
    WHERE ROWNUM=1
) tb2 ON tb1.id = tb2.t1_id

该语句返回多行，但我只需要一行。我该怎么做呢?

Answer 1

没有测试，但应该做你需要的

SELECT *
FROM table1 t1 JOIN table2 t2 
ON ( t1.id = t2.t1_id
     AND EXISTS ( SELECT 'x'
                  FROM table3 t3
                  WHERE t2.t3_id = t3.id
                 )
     AND NOT EXISTS ( SELECT 'a'
                      FROM table2 t22 
                      WHERE t22.t1_id = t2.t1_id
                      AND t22.id < t2.id
                     )
   )

Answer 2

您可以通过使用row_number()枚举它们然后只选择一个值来获得一行多个条目。下面是一个例子:

select . . .
from table1 t1 join
     (select t2.*, row_number() over (partition by t.id order by t2.id) as seqnum
      from table2 t2
     ) t2
     on t2.t1_id = t1.id and t2.seqnum = 1;

编辑:

对于所有三个表，您希望执行row_number()所有连接:

select . . .
from (select . . ., row_number() over (partition by t1_id order by id) as seqnum
      from table1 t1 join
           table2 t2
           on t2.t1_id = t1.id join
           table3 t3
           on t2.t3_id = t3.id
     ) t
where seqnum = 1;