我有一个很长的查询，我发现我的NOT并没有排除NOT后面括号中的内容。
我看到了Exclude和where not exists，但我必须重新选择，而且我已经选择的表中有太多复杂的联接表，加上一个表很大，需要很长时间才能选择我已经选择了的表，所以我不能重新选择，因为这会使查询花费太长时间。我如何让这种排斥发挥作用？
INSERT INTO #UNeedingC(id, CASEID, firstname, lastname, userid, AGEOFNOTIFICATION, DATETIMEDECISIONMADE, DELEGATESYSTEM, Person_id, request_type_id, service_place_id, status_summary, externalUserId, subject, onDate, externalPersonId, externalSystemId)
select distinct
c.id
,uc.case_id
,t_case.FIRSTNAME as first
,t_case.LASTNAME as last
,t_case.user_id as userid
,CONVERT(VARCHAR, DATEDIFF(dd, SC.status_change_date, GETDATE())) + ' Day(s) ' + CONVERT(VARCHAR, DATEDIFF(hh, SC.status_change_date, GETDATE()) % 24) + ' Hour(s) ' as [AGE OF NOTIFICATION]
,SC.status_change_date AS [DATE TIME DECISION MADE]
,[ckoltp_sys].DBO.ckfn_GetStringLocaleValue(152,9,uc.delegate_system,50,0) AS [DELEGATESYSTEM]
,c.person_id
,uc.request_type_id ------
,uc.service_place_id
,uc.status_summary
,eou.external_id
,c.tzix_id+' '+[ckoltp_sys].dbo.ckfn_GetStringLocaleValue(148,9,uc.status_summary,5,0)+' type' AS subject
,dateadd( hour,41,dateadd(day,0,datediff(d,0,sc.status_change_date)) ) AS onDate
,emd.externalId externalPersonId
,eou.system_id as externalSystemId
--,u.disable
from
#tempC t_case with (NOLOCK)
inner join dbo.org_case c with (nolock) ON t_case.Person_id=c.Person_id
INNER JOIN dbo.org_2_case uc with (NOLOCK) ON uc.case_id=c.id
inner JOIN dbo.ORG_LOS S WITH (NOLOCK) ON S.case_id = UC.case_id
inner JOIN dbo.ORG_EXTENSION SC WITH (NOLOCK) ON SC.los_id= S.id
inner join dbo.org_user u with (NOLOCK) on u.id=t_case.user_id
inner join dbo.org_person op with (NOLOCK) on op.id=c.Person_id
inner JOIN dbo.u_person_concept_value MC ON MC.CID = op.cid --this is the slow table
inner join dbo.EXTERNAL_ORG_USER_DATA eou with (NOLOCK) ON eou.org_user_id = t_case.user_id
inner join dbo.EXTERNAL_person_DATA emd with (NOLOCK) ON emd.CID = op.cid --op.id --?
WHERE
DATEDIFF(day, SC.status_change_date , GETDATE()) <= 2
AND
u.disable <> 1
AND
( --(denied/approved)
dbo.ckfn_GetStringLocaleValue(148,9,uc.status_summary,5,0) = 'Denied'
OR
(dbo.ckfn_GetStringLocaleValue(148,9,uc.status_summary,5,0) in( 'Fully Approved', 'Partially Approved'))
)
AND
(
(
ISNULL(uc.request_type_id,'') in( 12)
AND DATEDIFF(month, SC.status_change_date , GETDATE()) <= 2
)
OR
(
ISNULL(uc.request_type_id,'') in( 10,11)
)
--OR
--(
-- --exclude this
-- (
-- MC.concept_id = '501620' --general val1 (1000/1001)
-- AND
-- (C.ID in (select case_id from #CASES where str_value in ('1000','1001'))
-- AND (uc.service_place_id = 31 OR uc.service_place_id = 32))
-- ) --not
--) --or
)--AND
AND
(t_case.firstname not like '%external%' and t_case.lastname not like '%case manager%')
AND
(
C.ID in (select case_id from #CASES where concept_id='501620')--MC.concept_id = '501620'
)
--overall around AND (denied/approved)--
and DBO.ckfn_GetStringLocaleValue(152,9,uc.delegate_system,50,0) in ('AP','CA')
AND NOT --this not is not working...this appears in query results
(
--exclude these
(
MC.concept_id = '501620'
AND
(C.ID in (select case_id from #CASES where str_value in ('1000','1001'))
AND (uc.service_place_id = 31 OR uc.service_place_id = 32))
) --not
) --
select * from #UNeedingC
结果显示排除的内容：
id caseid firstname lastname userid ageofNotification Datetimedecisionmade DelegateSys Person_id request_type_id service_place_id status_summary externalUserId subject
onDate externalPersonId externalSystemId
000256200 256200 Sree Par 1234 0 Apr 5 CA
4270000 11 31 3 sparee 000256200 Fully Approved tested Ad 2021-04-06 17:00 363000 2
我的问题是：你知道为什么NOT不起作用吗？我如何在没有其他选择的情况下将其排除在外？参见"；这不起作用"；议论我在网上搜索了一下，但只找到了exclude和where not exists，这需要另一个选择，我不想要。
我想我已经想通了："NOT在一个条件下运行。若要否定两个或多个条件，请对每个条件重复NOT；从没有开始有两件事。
这似乎奏效了：
...
AND
--exclude these
(
MC.concept_id = '501620' --general val1 (1000/1001)
AND
(C.ID not in (select case_id from #CASES where str_value in ('1000','1001'))
AND (uc.service_place_id not in ('31','32')))
) --not