基于customers表credit_limit关联credit_ratings获取客户评级的SQL问题

现有两张表customers和credit_ratings，需根据customers表的credit_limit字段确定客户对应的rating评级，期望输出包含cust_id、credit_limit、rating的结果集。最初尝试用JOIN关联表并使用表别名，但不知道如何设置类似c.credit_limit > 0 AND c.credit_limit <=1000 对应Excellent的条件逻辑，当前编写的SQL存在问题，寻求正确实现方法。

SELECT c.cust_id, 
        c.credit_limit, 
        cred.rating
FROM customers AS c, credit_ratings AS cred
WHERE IF(c.credit_limit);

根据credit_ratings表的实际用途，分两种场景给出解决方案：

场景1：评级规则固定，无需依赖credit_ratings表 #

如果评级的区间规则是固定的（比如Excellent对应0-1000、Good对应1001-5000等），直接用CASE WHEN语句实现映射即可：

SELECT 
    c.cust_id,
    c.credit_limit,
    CASE
        WHEN c.credit_limit > 0 AND c.credit_limit <= 1000 THEN 'Excellent'
        WHEN c.credit_limit > 1000 AND c.credit_limit <= 5000 THEN 'Good'
        WHEN c.credit_limit > 5000 AND c.credit_limit <= 10000 THEN 'Fair'
        WHEN c.credit_limit > 10000 THEN 'Poor'
        ELSE 'No Rating' -- 处理credit_limit为0、NULL或超出所有区间的情况
    END AS rating
FROM customers AS c;

场景2：credit_ratings表存储评级区间规则 #

如果credit_ratings表中预定义了评级区间（比如表结构包含min_limit、max_limit、rating字段，每条记录对应一个评级的区间范围），则通过JOIN关联区间匹配：

SELECT 
    c.cust_id,
    c.credit_limit,
    cred.rating
FROM customers AS c
LEFT JOIN credit_ratings AS cred 
    ON c.credit_limit > cred.min_limit 
    AND c.credit_limit <= cred.max_limit;

• 使用LEFT JOIN可以保留所有客户记录，即使没有匹配到对应的评级（此时rating字段为NULL）；
• 若只需保留有对应评级的客户，将LEFT JOIN替换为INNER JOIN即可。

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