MySQL：如何按customer_id分组处理不同行数的列

你的问题出在没有用聚合函数配合CASE语句。当你按customer_id分组时，MySQL会随机选取每组中某一行的CASE结果，导致第三、四列无法正确对应不同贷款周期的状态，只会显示单一重复值。

修正后的查询语句 #

SELECT customer_id,
       MAX(CASE WHEN LoanCycleAgainstPartner = 1 AND dpd > 0 THEN 'not paid in time' ELSE 'paid in time' END) AS `First Loan Payment`,
       MAX(CASE WHEN LoanCycleAgainstPartner = 2 AND dpd > 0 THEN 'not paid in time' ELSE 'paid in time' END) AS `Second Loan Payment`,
       MAX(CASE WHEN LoanCycleAgainstPartner = 3 AND dpd > 0 THEN 'not paid in time' ELSE 'paid in time' END) AS `Third Loan Payment`
FROM database1.`2022.12.29_loan_details` 
WHERE partnername = 'Uber B.V.'
GROUP BY customer_id;

关键修正说明 #

1. 用聚合函数包裹CASE：使用MAX()（或MIN()，因为每个用户的单个贷款周期只会对应一种状态），确保分组后能精准提取每个贷款周期的还款状态，而不是随机取某一行的值。
2. 移除多余的DISTINCT：GROUP BY customer_id已经保证了每个用户只出现一次，无需再用DISTINCT(customer_id)。

可选优化（区分无贷款的情况） #

如果需要区分用户没有对应周期贷款的情况，可以调整CASE的ELSE分支，比如：

MAX(CASE WHEN LoanCycleAgainstPartner = 1 THEN 
           CASE WHEN dpd > 0 THEN 'not paid in time' ELSE 'paid in time' END 
       ELSE 'No Loan' END) AS `First Loan Payment`

内容的提问来源于stack exchange，提问作者Muhammad Abdullah Khan