为什么失败时我的查询在案例中

Question

我试图运行SQL Server案例查询，但遇到了一个错误。让我试着解释一下我为什么要做我正在做的事情，以及我在寻找最终结果。

我有两张桌子。1，它有一个机会ID(在本例中为A.ID(，第二个表有一个我感兴趣的列(B.Q3ABM_C(的帐户。在这种情况下，我要求SQL做的是，对于B.Q3ABM_C列中的每个空A.ID，都给我一个1，反之亦然。为什么会失败？

SELECT B.ID as Account_ID, B.FULL_ACCOUNT_ID__C, A.ID as Opportunity_ID,A.name, stagename,closedate,A.createddate,
OPPORTUNITY_PRODUCT__C,AMOUNT, B.Q3ABM__C, B.Drip_Campaign_Code__c,OPPORTUNITYTYPE_STRING__C,
CASE 
WHEN (SELECT A.id FROM SF_OPPORTUNITY as a 
LEFT JOIN SF_ACCOUNT as b on A.Accountid = b.ID where B.Q3ABM__C IS NULL) THEN 1
ELSE 0
END AS 'Does Not Exist in Q3DM',
CASE 
WHEN (SELECT A.id FROM SF_OPPORTUNITY as a
LEFT JOIN SF_ACCOUNT as b on A.Accountid = b.ID where B.Q3ABM__C IS NOT NULL) THEN 1
ELSE 0
END AS 'Exists in Q3DM'
FROM SF_OPPORTUNITY as a
LEFT JOIN SF_ACCOUNT as b on a.ACCOUNTID = b.ID 
WHERE A.createddate >= '7-1-2019'
AND CLOSEDATE <= '12-31-2019'

谢谢！

Answer 1

如果要选择特定时段的记录，但要检查所有记录的NULLs和NOT NULLs，则可以检查哪些ID有NULL值。如果存在NULL值，则我们可以计算第一个Does Not Exist in Q3DM列，如果不存在，则计算第二个Exists in Q3DM:

WITH DataSource AS
(
SELECT DISTINCT A.id 
FROM SF_OPPORTUNITY as a 
LEFT JOIN SF_ACCOUNT as b 
on A.Accountid = b.ID 
where B.Q3ABM__C IS NULL
)
SELECT B.ID as Account_ID
,B.FULL_ACCOUNT_ID__C
,A.ID as Opportunity_ID
,A.name
,stagename
,closedate
,A.createddate
,OPPORTUNITY_PRODUCT__C
,AMOUNT
,B.Q3ABM__C
,B.Drip_Campaign_Code__c
,OPPORTUNITYTYPE_STRING__C
,CASE DS.Id IS NOT NULL THEN  1 ELSE 0 END 'Does Not Exist in Q3DM'
,CASE DS.Id IS NULL THEN 1 ELSE 0 END 'Exists in Q3DM' 
FROM SF_OPPORTUNITY as a
LEFT JOIN SF_ACCOUNT as b 
on a.ACCOUNTID = b.ID
LEFT JOIN DataSource DS
ON A.ACCOUNTID = DS.ID
WHERE A.createddate >= '7-1-2019'
AND CLOSEDATE <= '12-31-2019';

如果您只对给定时间段的感兴趣，并且只检查当前记录的值：

SELECT B.ID as Account_ID
,B.FULL_ACCOUNT_ID__C
,A.ID as Opportunity_ID
,A.name
,stagename
,closedate
,A.createddate
,OPPORTUNITY_PRODUCT__C
,AMOUNT
,B.Q3ABM__C
,B.Drip_Campaign_Code__c
,OPPORTUNITYTYPE_STRING__C
,CASE DS.Id IS NULL THEN  1 ELSE 0 END 'Does Not Exist in Q3DM'
,CASE DS.Id IS NOT NULL THEN 1 ELSE 0 END 'Exists in Q3DM' 
FROM SF_OPPORTUNITY as a
LEFT JOIN SF_ACCOUNT as b 
on a.ACCOUNTID = b.ID
LEFT JOIN DataSource DS
ON A.ACCOUNTID = DS.ID
WHERE A.createddate >= '7-1-2019'
AND CLOSEDATE <= '12-31-2019'

Answer 2

试试这个(根据您的要求使用"Select"one_answers"Where"子句中的列(：

SELECT A.*,B.* , 
CASE WHEN B.Q3ABM__C IS NULL THEN 1 ELSE 0 END AS 'DOES NOT EXISIT IN Q3DM',
CASE WHEN B.Q3ABM__C IS NOT NULL THEN 1 ELSE 0 END AS 'EXISITS IN Q3DM'
FROM PLS.SF_OPPORTUNITY A
FULL JOIN PLS.SF_ACCOUNT B ON A.ACCOUNTID=B.ID