Ora SQL查询:没有引用的联接 [英] Ora SQL Query: joining without references

问题描述

我正在尝试通过使用简单查询并感觉被卡在Oracle上来实现一些逻辑.问题是我无法使用PL-SQL，这让我有些头疼.

I am trying to achieve some logic on Oracle by using simple query and feeling stuck on it. The thing is that I cannot use PL-SQL and this is giving me some headached.

我有三个表且值均低于

我正在尝试获得类似的东西:

I am trying to get something like:

SELECT T1.CODE,T2.CODE,T3.VALUE
FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.REF = T2.CODE
JOIN TABLE3 T3 ON T2.REF = T3.CODE
WHERE T1.CODE = XXXXX

XXXX = 98的结果

Result for XXXX = 98

98,2，CCC

只要参数XXXXX是99,98,96,95，它就会返回我期望的值，但是我需要的逻辑不适用于97.

Whenever the parameter XXXXX is 99,98,96,95 it returns what I was expecting but the logic I need doesnt work for 97.

我的要求是，万一我在Table2中找不到链接，那么我应该在Table3中始终使用DEF并将未链接的值保留为NULL.像这样:

My requirement says that in case i cannot find a link in Table2 then I should use always DEF in Table3 and leave unlinked values as NULL. Something like:

XXXX = 97的结果

Result for XXXX = 97

97，NULL，AAA

97,NULL,AAA

我认为可以通过使用CASE语句以一种不太干净"的方式来实现，但这是一个示例，其中显示的列数非常少.在我的真实情况下，它要大得多.因此，我想避免使用CASE语句，因为这会大大增加它的复杂性.

I think it could be achieved in a not very "clean" way by using CASE statements but this is an example in which the number of columns shown is very minimal. In my real case it is extremelly bigger... So I want to try to avoid using CASE statements as it would raise the complexity of it a lot.

我尝试了不同的方法，但是我对Oracle的低水平经验不足:)

I tried with different methods but my low experience on Oracle cannot deep so much :)

是否可以在不使用PLSQL的情况下实现这些功能?

Any way to achieve this without using PLSQL neither those CASE?

推荐答案

如果我正确理解，则需要使用outer join.如果T2.REF为NULL，则可以使用COALESCE返回与"DEF"关联的值:

If I'm understanding correctly, you need to use an outer join instead. You can then use COALESCE to return the value associated with "DEF" if T2.REF is NULL:

SELECT T1.CODE,
       T2.CODE, 
       T3.VALUE
FROM TABLE1 T1
    LEFT JOIN TABLE2 T2 ON T1.REF = T2.CODE
    LEFT JOIN TABLE3 T3 ON COALESCE(T2.REF,'DEF') = T3.CODE 
WHERE T1.CODE = XXXXX

这篇关于Ora SQL查询:没有引用的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！