排除具有部分匹配条件的记录

问：

我有两个表，Person 和 Loan_Dtl，我希望我的结果只显示所有贷款都符合以下条件的人。如果此人有多种贷款，其中一些符合这些条件，而另一些则不符合这些条件，那么该人不应出现在结果中。

• 输入l.loan_type_cd （'D0'，'D1'，'D2'，'D3'，'D5'，'D6'，'D7'，'D8'，'D9'）
• 和 （l.opb_amt + l.oib_amt） > 0
• AND l.loan_status_cd = 'RP'
• 和 l.repymt_plan_type_cd IN （'C1'， 'C2'， 'C3'， 'I3'， 'I4'， 'I5'， 'IB'， 'IC'， 'IL'， 'IS'， 'J5'， 'P1'， 'PA'， 'RA'）
• l.loan_holder_cd输入 （'578'， '580'， '500'， '507'， '512'）

我下面的代码用于显示混合了一些匹配贷款的人。在示例数据中，D2'、'RP'、'I5'、250、50 是结果中唯一包含的贷款，但此人 （2544541） 根本不应包含在结果中，因为他有其他不匹配的贷款。我只需要所有贷款都匹配的人。2544542的人应该出现在结果中，因为他只有匹配的贷款。

SELECT 
  COUNT(DISTINCT p.person_id) AS Borrower_Count   
 ,SUM(l.opb_amt + l.oib_amt) as balance 
FROM person p INNER JOIN 
 loan_dtl l on p.person_id = l.brwr_person_id 
WHERE  
  NOT EXISTS  
            (SELECT 1 
              FROM loan_dtl l2 
              WHERE l.loan_id = l2.loan_id 
              AND l2.loan_type_cd NOT IN 
  ('D0','D1','D2','D3','D5','D6','D7','D8','D9')
            ) 
            
  AND NOT EXISTS  
            (SELECT 1 
              FROM loan_dtl l2 
              WHERE l.loan_id = l2.loan_id 
              AND (l.opb_amt + l.oib_amt) = 0 
            )

    AND NOT EXISTS 
            (SELECT 1 
              FROM loan_dtl l2 
              WHERE l.loan_id = l2.loan_id
              AND l.loan_status_cd NOT IN ('RP')
          ) 

 AND NOT EXISTS 
            (SELECT 1
              FROM loan_dtl l2 
              WHERE l.loan_id = l2.loan_id
              AND l.repymt_plan_type_cd NOT IN ('C1', 'C2', 'C3', 'I3', 'I4', 'I5', 
              'IB', 'IC', 'IL', 'IS', 'J5', 'P1', 'PA', 'RA')
            )
            
  AND NOT EXISTS 
            (SELECT 1
              FROM loan_dtl l2 
              WHERE l.loan_id = l2.loan_id 
              AND l.loan_holder_cd NOT IN ('578', '580', '500', '507', '512')
            )

表格和数据：

CREATE TABLE [dbo].[Loan_dtl](
[loan_id] [int] NULL,
[brwr_person_id] [int] NULL,
[loan_holder_cd] [int] NULL,
[loan_type_cd] [varchar](50) NULL,
[loan_status_cd] [varchar](50) NULL,
[REPYMT_PLAN_TYPE_CD] [varchar](50) NULL,
[OPB_AMT] [int] NULL,
[OIB_AMT] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Person](
[person_id] [int] NULL
) ON [PRIMARY]
GO


INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
[loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (124677158, 
 2544541, 755, N'CL', NULL, NULL, 25824, 3261)
 GO
 INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
 [loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (124919090, 
2544541, 755, N'CL', NULL, NULL, 37708, 4761)
GO
INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
[loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (102070274, 
2544541, 507, N'D2', N'RP', N'I5', 250, 50)
GO
INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
[loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (102070266, 
2544541, 507, N'SF', NULL, N'IB', 737, 189)
GO
INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
[loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (102070269, 
2544541, 507, N'D1', NULL, N'IB', 0, 0)
GO
INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
[loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (102070280, 
2544542, 507, N'D2', N'RP', N'I5', 950, 5)
GO
INSERT [dbo].[Person] ([person_id]) VALUES (2544541)
GO
INSERT [dbo].[Person] ([person_id]) VALUES (2544542)
GO