SQL查询需求：筛选所有贷款均符合指定条件的人员

现有Person与Loan_Dtl两张表，需查询所有贷款均满足以下条件的人员：

• l.loan_type_cd IN ('D0','D1','D2','D3','D5','D6','D7','D8','D9')
• (l.opb_amt + l.oib_amt) > 0
• 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 IN ('578', '580', '500', '507', '512')

当前SQL仅能筛选出存在部分符合条件贷款的人员（例如人员2544541因存在非符合条件贷款不应被纳入，但现有代码仍会统计其符合条件的贷款记录），需修正代码以仅保留所有贷款均符合条件的人员（例如人员2544542应被纳入结果）。

当前SQL代码 #

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

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 p.person_id = l2.brwr_person_id 
   AND (
     l2.loan_type_cd NOT IN ('D0','D1','D2','D3','D5','D6','D7','D8','D9')
     OR (l2.opb_amt + l2.oib_amt) <= 0
     OR l2.loan_status_cd <> 'RP'
     OR l2.repymt_plan_type_cd NOT IN ('C1', 'C2', 'C3', 'I3', 'I4', 'I5', 'IB', 'IC', 'IL', 'IS', 'J5', 'P1', 'PA', 'RA')
     OR l2.loan_holder_cd NOT IN ('578', '580', '500', '507', '512')
   )
  )

原代码的核心问题是：每个NOT EXISTS仅针对单条贷款做校验，只会过滤掉本身不符合条件的贷款记录，但无法排除那些存在其他不符合条件贷款的人员。

修正后的逻辑：

1. 以人员为单位校验，检查该人员是否存在任何一条不符合条件的贷款
2. 若不存在此类贷款，则说明该人员所有贷款均符合要求，才纳入统计
3. 将所有条件的否定用OR合并，关联条件改为人员ID而非贷款ID，确保覆盖该人员的全部贷款

用测试数据执行修正后的SQL，会得到1个借款人（2544542），余额为955（950+5），符合预期。

内容的提问来源于stack exchange，提问作者evanburen