我有下面提到的 4 个表并尝试提取值ACC_NUMBER来自子查询,请帮助我优化正确的语法
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 表中不存在 join,您将
从未获得该特定优惠的 ACC_NUMBER?
如果您准备对当前语法进行一些更改,您可以
使用连接来获取查询结果中的 ACC_NUMBER:
Example:
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)