如何从子查询结果中选择值 [英] how to select the value from sub query result

问题描述

我有下面提到的以下4个表，并尝试从子查询中提取值 ACC_NUMBER ，请帮助我优化正确的语法

I have the following 4 tables mentioned below and trying to extract the value ACC_NUMBER from sub query, Please help me with optimized correct syntax

ACCOUNT TABLE
-------------------------
|ACC_NUMBER  |  ACC_NAME|
-------------------------

ACCOUNT_DETAILS TABLE
---------------------------------
|ACC_NUMBER |DEAL_NUMBER|DEAL_TYPE|
---------------------------------

DEALS TABLE
------------------------------------
|DEAL_NUMBER |DEAL_TYPE|DEP_NUMBER |
------------------------------------

DEPARTMENT TABLE
------------------------
|DEP_NUMBER  | DEP_NAME |
------------------------

SELECT 
D.DEAL_NUMBER,
D.D_TYPE,
DP.DEPARTMENT_NUMBER 
FROM 
DEALS D , DEPARTMENT DP
WHERE 
D.DEP_NUMBER = DP.DEP_NUMBER
AND D.DEAL_NUMBER NOT IN
                 (SELECT ACD.DEAL_NUMBER
                    FROM ACCOUNT_DETAILS ACD, ACCOUNT AC
                   WHERE AC.ACC_NUMBER = ACD.ACC_NUMBER
                        AND DEAL_TYPE = 'XXXX'
                         AND ACD.DEAL_NUMBER = D.DEAL_NUMBER)

推荐答案

您好，如果您使用的是D.DEAL_NUMBER NOT IN，则表示该记录 在ACCOUNT和ACCOUNT_DETAILS表中不存在联接，您将 从未获得该特定交易的ACC_NUMBER?

Hi, If you are using D.DEAL_NUMBER NOT IN that means that records join is not present in ACCOUNT and ACCOUNT_DETAILS table, You will never get ACC_NUMBER for that particular deals?

如果准备好对当前语法进行一些更改，则可以 进行联接以在查询结果中获得ACC_NUMBER:

If you are ready to do some alteration in the current syntax, You can play with joins to get ACC_NUMBER in your query result:

示例:

SELECT 
D.DEAL_NUMBER,
D.D_TYPE,
ACD.ACC_NUMBER,
DP.DEPARTMENT_NUMBER 
FROM 
DEALS D 
join DEPARTMENT DP
on D.DEP_NUMBER = DP.DEP_NUMBER
left join ACCOUNT_DETAILS ACD
on ACD.DEAL_NUMBER = D.DEAL_NUMBER
join ACCOUNT AC AC.ACC_NUMBER = ACD.ACC_NUMBER
WHERE DEAL_TYPE = 'XXXX'
             AND ACD.DEAL_NUMBER = D.DEAL_NUMBER

这篇关于如何从子查询结果中选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！