SQL查询结构调整：启用注释行并保留月分组格式列

select REPLACE(SUBSTR(p.prod_desc,20,200),'size XXXL','size 14.00') ||':PRODUCT_ID='||p.PROD_ID as "PRODUCT_DESCRIPTION",
       CASE WHEN t.CALENDAR_WEEK_NUMBER = 13 THEN 'First week'
            WHEN t.CALENDAR_WEEK_NUMBER = 14 THEN 'Second week'
            WHEN t.CALENDAR_WEEK_NUMBER = 15 THEN 'Third week'
            WHEN t.CALENDAR_WEEK_NUMBER = 16 THEN 'Fourth week'
            WHEN t.CALENDAR_WEEK_NUMBER = 17 THEN 'Fifth week'
       END as "WEEK_IN_MONTH",
       sum(s.AMOUNT_SOLD) as TOTAL_AMOUNT_SOLD   
from TIMES t 
join SALES s on t.TIME_ID = s.TIME_ID
join PRODUCTS p on p.PROD_ID = s.PROD_ID
where t.CALENDAR_MONTH_DESC = '2000-04'
      and p.PROD_ID in (300,10,540)
group by t.CALENDAR_MONTH_DESC, t.CALENDAR_WEEK_NUMBER, p.PROD_ID, p.prod_desc
HAVING sum(s.AMOUNT_SOLD) > 0;

调整说明 #

1. 移除第一列前的注释符--，启用产品描述列的计算逻辑
2. 完整保留原查询的WEEK_IN_MONTH和TOTAL_AMOUNT_SOLD两列
3. 更新GROUP BY子句：添加t.CALENDAR_MONTH_DESC维持按月份分组的维度，同时补充p.PROD_ID和p.prod_desc，满足SQL分组规则（所有非聚合列必须出现在GROUP BY中）

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