一、背景说明
随着市场竞争的加剧和技术的发展，运营商需要频繁调整其服务套餐的价格以吸引客户或应对成本变化。假设某移动运营商决定对其提供的各种服务套餐（如流量包、语音通话包等）进行价格调整，并记录了每次价格调整的具体日期和新的价格。现在，管理层希望了解2024年9月1日当天所有服务套餐的有效价格是多少，以便分析当时的市场策略和收益情况。如果某个服务套餐在此之前从未调整过价格，则默认该套餐的初始价格为10元。
为了存储这些信息，我们将创建一个名为 ServicePackages 的表来保存每个服务套餐的价格变动历史。以下是建表语句：
CREATETABLEServicePackages ( package_idINT,-- 服务套餐IDnew_priceINT,-- 新价格change_dateDATE,-- 价格变更日期PRIMARYKEY(package_id, change_date)-- 主键：(套餐ID, 变更日期));
接下来，我们插入一些示例数据到 ServicePackages 表中，模拟几个不同套餐在不同时期的价格变化情况。
INSERTINTOServicePackages (package_id, new_price, change_date)VALUES(1,15,'2024-07-01'),-- 套餐1于2024-07-01调整至15元(2,10,'2024-01-01'),-- 套餐2于年初保持原价10元(1,20,'2024-08-01'),-- 套餐1再次于2024-08-01调价至20元(3,12,'2024-06-15');-- 套餐3于2024-06-15首次定价为12元
本题的关键点在找到 2024-09-01 前所有有改动的产品及其最新价格和没有没有修改过价格的产品。
1、先找到所有的产品
2、再找到所有 2024-09-01 前有修改的产品和他们最新的价格
3、使用 left join 将两个查询联合，如果产品没有价格，说明没有修改过，设置为 10；如果有价格，设置为最新的价格
-- 查询在指定日期前所有套餐的最新价格SELECTp.package_idAS'套餐ID',-- 显示套餐编号COALESCE(m.new_price,10)AS'价格'-- 若存在对应记录则显示新价格，否则显示默认价格10FROM(SELECTDISTINCTpackage_idFROMServicePackages) p-- 获取所有套餐IDLEFTJOIN-- 左连接确保即使没有变更记录也能返回结果(SELECTpackage_id, new_priceFROMServicePackagesWHERE(package_id, change_date)in(SELECTpackage_id,MAX(change_date)-- 找出每个套餐最近一次的价格变更日期FROMServicePackagesWHEREchange_date <='2024-09-01'-- 只考虑截止到指定日期之前的变更GROUPBYpackage_id ) ) mONp.package_id = m.package_id-- 根据package_id关联两个子查询
以上SQL脚本实现了题目要求的功能转换，并适用于移动运营商的服务套餐价格分析场景。
执行结果如下：
套餐ID 价格
1 20
2 10
3 12
