一、背景說(shuō)明

隨著市場(chǎng)競(jìng)爭(zhēng)的加劇和技術(shù)的發(fā)展，運(yùn)營(yíng)商需要頻繁調(diào)整其服務(wù)套餐的價(jià)格以吸引客戶或應(yīng)對(duì)成本變化。假設(shè)某移動(dòng)運(yùn)營(yíng)商決定對(duì)其提供的各種服務(wù)套餐（如流量包、語(yǔ)音通話包等）進(jìn)行價(jià)格調(diào)整，并記錄了每次價(jià)格調(diào)整的具體日期和新的價(jià)格?，F(xiàn)在，管理層希望了解2024年9月1日當(dāng)天所有服務(wù)套餐的有效價(jià)格是多少，以便分析當(dāng)時(shí)的市場(chǎng)策略和收益情況。如果某個(gè)服務(wù)套餐在此之前從未調(diào)整過(guò)價(jià)格，則默認(rèn)該套餐的初始價(jià)格為10元。

二、表結(jié)構(gòu)說(shuō)明

為了存儲(chǔ)這些信息，我們將創(chuàng)建一個(gè)名為?ServicePackages?的表來(lái)保存每個(gè)服務(wù)套餐的價(jià)格變動(dòng)歷史。以下是建表語(yǔ)句：

CREATETABLEServicePackages (? package_idINT,-- 服務(wù)套餐IDnew_priceINT,-- 新價(jià)格change_dateDATE,-- 價(jià)格變更日期PRIMARYKEY(package_id, change_date)-- 主鍵：(套餐ID, 變更日期));

三、表數(shù)據(jù)插入

接下來(lái)，我們插入一些示例數(shù)據(jù)到?ServicePackages?表中，模擬幾個(gè)不同套餐在不同時(shí)期的價(jià)格變化情況。

INSERTINTOServicePackages (package_id, new_price, change_date)VALUES(1,15,'2024-07-01'),-- 套餐1于2024-07-01調(diào)整至15元(2,10,'2024-01-01'),-- 套餐2于年初保持原價(jià)10元(1,20,'2024-08-01'),-- 套餐1再次于2024-08-01調(diào)價(jià)至20元(3,12,'2024-06-15');-- 套餐3于2024-06-15首次定價(jià)為12元

四、實(shí)現(xiàn)思路分解

本題的關(guān)鍵點(diǎn)在找到 2024-09-01 前所有有改動(dòng)的產(chǎn)品及其最新價(jià)格和沒(méi)有沒(méi)有修改過(guò)價(jià)格的產(chǎn)品。

1、先找到所有的產(chǎn)品

2、再找到所有 2024-09-01 前有修改的產(chǎn)品和他們最新的價(jià)格

3、使用 left join 將兩個(gè)查詢(xún)聯(lián)合，如果產(chǎn)品沒(méi)有價(jià)格，說(shuō)明沒(méi)有修改過(guò)，設(shè)置為 10；如果有價(jià)格，設(shè)置為最新的價(jià)格

五、SQL代碼實(shí)現(xiàn)

-- 查詢(xún)?cè)谥付ㄈ掌谇八刑撞偷淖钚聝r(jià)格SELECTp.package_idAS'套餐ID',-- 顯示套餐編號(hào)COALESCE(m.new_price,10)AS'價(jià)格'-- 若存在對(duì)應(yīng)記錄則顯示新價(jià)格，否則顯示默認(rèn)價(jià)格10FROM(SELECTDISTINCTpackage_idFROMServicePackages) p-- 獲取所有套餐IDLEFTJOIN-- 左連接確保即使沒(méi)有變更記錄也能返回結(jié)果(SELECTpackage_id,? ? ? ? new_priceFROMServicePackagesWHERE(package_id, change_date)in(SELECTpackage_id,MAX(change_date)-- 找出每個(gè)套餐最近一次的價(jià)格變更日期FROMServicePackagesWHEREchange_date <='2024-09-01'-- 只考慮截止到指定日期之前的變更GROUPBYpackage_id? ? ? ? ? ? ? )? ) mONp.package_id = m.package_id-- 根據(jù)package_id關(guān)聯(lián)兩個(gè)子查詢(xún)

以上SQL腳本實(shí)現(xiàn)了題目要求的功能轉(zhuǎn)換，并適用于移動(dòng)運(yùn)營(yíng)商的服務(wù)套餐價(jià)格分析場(chǎng)景。

執(zhí)行結(jié)果如下：

套餐ID? ?價(jià)格

1? ? 20

2? ? 10

3? ? 12