select p.product_id,
product_name,
competitor_name,
total_sales_amount_of_product,
total_sales_amount_of_product-total_competitor_sales_amount_2023 sales_difference_with_competitor
from (
    select product_id, sum(quarter_1_sales_amount)+sum(quarter_2_sales_amount)+sum(quarter_3_sales_amount)+sum(quarter_4_sales_amount) total_sales_amount_of_product
    from sales_info
    group by product_id
) s 
join oppo_products_detail p on p.product_id=s.product_id
join competitor_analysis c on s.product_id=c.product_id
order by product_id

把所有计算过程都放到select主查询中时，会有一个total_competitor_sales_amount_2023不在groupby当中，要解决这个问题，可以把groupby移到子查询中，对相关列进行聚合，最后直接在主查询中完成列的计算就可以了，无需再进行聚合操作