贷款未启动客户识别及10期EMI还款情况SQL查询需求

数据老陈

2026-5-20

解决方案：贷款客户分类查询（无窗口函数）

Hey there! Let's break down your two loan customer identification needs with simple SQL that sticks to your constraints (no window functions like PARTITION/OVER/LAG, only UNION, JOIN, LEFT JOIN, INNER JOIN). I'll also address the potential gaps in your initial query for unstarted customers.

需求1：识别未启动客户（未偿还第一期EMI）

Unstarted customers are those who haven't made their first EMI payment—this includes two scenarios: loans with no payments at all, or loans where later EMIs were paid but the first was skipped (a rare edge case, but we need to cover it).

Your initial query might have missed loans with zero payment records if you used an INNER JOIN instead of a LEFT JOIN. Here's a robust version:

SELECT l.LoanID
FROM Loans l
LEFT JOIN EMIPayments p 
  ON l.LoanID = p.LoanID 
  AND p.EMINumber = 1 -- 仅关联第一期还款记录
WHERE p.LoanID IS NULL; -- 无匹配的第一期还款记录，即未启动

逻辑说明：

用LEFT JOIN保留所有贷款记录，哪怕没有任何还款记录
通过AND p.EMINumber = 1精准筛选第一期还款的关联条件
当p.LoanID为NULL时，说明该贷款要么从未还款，要么没有还第一期，完全覆盖未启动客户的范围

需求2：识别两类客户：已偿还10期EMI的客户 + 存在未还款的客户

我们将分两部分实现，再用UNION合并为带分类标签的结果集。

2.1 已偿还10期EMI的客户

通过分组统计每个贷款的已还期数，确认达到10期：

SELECT l.LoanID, '已偿还10期' AS CustomerType
FROM Loans l
INNER JOIN EMIPayments p 
  ON l.LoanID = p.LoanID
GROUP BY l.LoanID
HAVING COUNT(DISTINCT p.EMINumber) = 10;

2.2 存在未还款的客户

要找出有逾期未还的贷款，需要对比应还期数和实际还款记录。这里需要一个辅助的Numbers表（存储1到最大可能EMI期数的整数，比如1到100）来生成每个贷款的所有应还期数：

SELECT DISTINCT l.LoanID, '存在未还款' AS CustomerType
FROM Loans l
INNER JOIN Numbers n 
  ON n.Number <= l.TotalEMIs -- 关联该贷款的所有应还期数
LEFT JOIN EMIPayments p 
  ON l.LoanID = p.LoanID 
  AND n.Number = p.EMINumber -- 检查该期是否已还款
WHERE p.LoanID IS NULL; -- 存在应还但未还的期数

合并两类客户

用UNION将两个结果合并，得到统一的客户分类列表：

-- 已偿还10期的客户
SELECT l.LoanID, '已偿还10期' AS CustomerType
FROM Loans l
INNER JOIN EMIPayments p 
  ON l.LoanID = p.LoanID
GROUP BY l.LoanID
HAVING COUNT(DISTINCT p.EMINumber) = 10

UNION

-- 存在未还款的客户
SELECT DISTINCT l.LoanID, '存在未还款' AS CustomerType
FROM Loans l
INNER JOIN Numbers n 
  ON n.Number <= l.TotalEMIs
LEFT JOIN EMIPayments p 
  ON l.LoanID = p.LoanID 
  AND n.Number = p.EMINumber
WHERE p.LoanID IS NULL;

小提示

如果没有现成的Numbers表，可以用UNION ALL临时生成：SELECT 1 AS Number UNION ALL SELECT 2 ... UNION ALL SELECT 100
未还款查询中的DISTINCT是为了避免同一贷款因多期未还而重复输出

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

贷款未启动客户识别及10期EMI还款情况SQL查询需求

需求1：识别未启动客户（未偿还第一期EMI） #

逻辑说明： #

需求2：识别两类客户：已偿还10期EMI的客户 + 存在未还款的客户 #

2.1 已偿还10期EMI的客户 #

2.2 存在未还款的客户 #

合并两类客户 #

小提示 #