在where子句中使用exists [英] Use of exists in where clause

问题描述

嗨朋友，

我有2张桌子

Hi Friends,
I have 2 table

1. Account<br />
2. Closed Account

我想要一个未关闭的账户清单。



我试过以下

I want list of account which is not closed.

I have tried following

SELECT distinct  vwA.AccountID,vwA.AccountName                         
  FROM vwAccounts  vwA       
  inner JOIN RMT_RiskAssessmentPlan AP ON AP.Accountcode = VWA.AccountID                        
  WHERE AP.[Year] = 2013 AND   
        VWA.AccountID NOT IN (SELECT AccountCode FROM RMT_ClosedAccount WHERE [YEAR] = 2013)            
  order by vwA.AccountName 

如何在以下查询中将in子句替换为exists？ br $> b $ b

我厌倦了

How can i replace the in clause with exists in following query?

I tired following

SELECT distinct  vwA.AccountID,vwA.AccountName
 FROM vwAccounts  vwA
 inner JOIN RMT_RiskAssessmentPlan AP ON AP.Accountcode = VWA.AccountID
 WHERE AP.[Year] = 2013 AND not exists(SELECT AccountCode FROM RMT_ClosedAccount WHERE [YEAR] = 2013)
 order by vwA.AccountName

但它返回给我0结果



任何想法如何使用exists子句？



提前谢谢

But it returned me 0 result

Any idea how to use the exists clause?

Thanks in advance

推荐答案

基于你发布的查询，下面的查询应该给你想要的结果 -



SELECT distinct vwA.AccountID，vwA.AccountName

FROM vwAccounts vwA

内部加入RMT_RiskAssessmentPlan AP ON AP.Accountcode = VWA.AccountID

WHERE AP。[年份] = 2013年和

NOT EXISTS（SELECT * FROM RMT_ClosedAccount WHERE [YEAR] = AP。[YEAR]）

按vwA.AccountName命令

Based on the query you posted, the below query should give you the desired result-

SELECT distinct vwA.AccountID,vwA.AccountName
FROM vwAccounts vwA
inner JOIN RMT_RiskAssessmentPlan AP ON AP.Accountcode = VWA.AccountID
WHERE AP.[Year] = 2013 AND
NOT EXISTS(SELECT * FROM RMT_ClosedAccount WHERE [YEAR]=AP.[YEAR])
order by vwA.AccountName

抱歉....我错过了一个条件。尝试以下查询 -



SELECT distinct vwA.AccountID，vwA.AccountName

FROM vwAccounts vwA

inner JOIN RMT_RiskAssessmentPlan AP ON AP.Accountcode = VWA.AccountID

WHERE AP。[Year] = 2013 AND

NOT EXISTS（SELECT * FROM RMT_ClosedAccount WHERE [YEAR] = AP。 [年份]和ClosedAccount.AccountID = AP.AccountID）

按vwA.AccountName排序

Sorry .... i missed one condition. Try the below query -

SELECT distinct vwA.AccountID,vwA.AccountName
FROM vwAccounts vwA
inner JOIN RMT_RiskAssessmentPlan AP ON AP.Accountcode = VWA.AccountID
WHERE AP.[Year] = 2013 AND
NOT EXISTS(SELECT * FROM RMT_ClosedAccount WHERE [YEAR]=AP.[YEAR] and ClosedAccount.AccountID=AP.AccountID)
order by vwA.AccountName

这篇关于在where子句中使用exists的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！