Oracle Having子句问题：如何对比CUST_CREDIT_LIMIT*500与sum(amount_sold)

你的SQL存在两个核心问题：

• 分组逻辑错误：GROUP BY里包含了s.AMOUNT_SOLD，这会让每条销售记录单独成组，导致sum(s.amount_sold)失去聚合意义（结果等于单条记录的金额），无法得到客户的总销售额。
• HAVING条件错误：你用了明细字段s.amount_sold和信用额度计算值对比，但需求是对比客户总销售额和CUST_CREDIT_LIMIT*500，必须使用聚合后的sum(s.amount_sold)。

修正后的SQL语句 #

select (c.CUST_FIRST_NAME ||' '||c.CUST_LAST_NAME) as "CUSTOMER_NAME",
       to_number(substr(c.CUST_INCOME_LEVEL, -7, 8),'999999999') as "UPPER_INCOM_LEVEL",
       sum(s.amount_sold) as "TOTAL_AMOUNT",
       (case when c.CUST_CREDIT_LIMIT <=1500 then 'Low limit'
             when c.CUST_CREDIT_LIMIT >1500 then 'High limit'
        end) as "CREDIT_LIMIT_LEVEL",
       c.CUST_VALID
from sh.CUSTOMERS c JOIN sh.SALES s
on s.CUST_ID = c.CUST_ID
where CUST_VALID = 'A'
and CUST_INCOME_LEVEL like '%-%'
GROUP BY c.CUST_FIRST_NAME, c.CUST_LAST_NAME, c.CUST_INCOME_LEVEL, c.CUST_CREDIT_LIMIT, c.CUST_VALID
having sum(s.amount_sold) > c.CUST_CREDIT_LIMIT*500

关键修正点说明 #

• 移除GROUP BY中的s.AMOUNT_SOLD：分组维度聚焦在客户本身的属性（姓名、收入等级、信用额度、有效状态），这样才能按客户聚合所有销售记录，得到正确的总销售额。
• HAVING子句替换为sum(s.amount_sold) > c.CUST_CREDIT_LIMIT*500：直接使用聚合后的总销售额和信用额度的500倍做对比，符合你的需求逻辑。

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