SQL 中階・訂單三表的連接與彙總
三表連接、LEFT JOIN 條件放在 ON、先彙總再連接、類別營收占比。
這一頁是題幹試閱;登入/註冊後每題可以在瀏覽器裡執行、對答案,第一次交對 +10 XP。
- 01
每張訂單金額
中等三表連接對帳要列出每張有效訂單的金額與客戶姓名。有效訂單=
status不是 'cancelled';沒有任何明細的訂單不列。訂單金額=該訂單所有明細的
quantity * unit_price加總。輸出欄位依序為:
order_id、客戶姓名、order_date、訂單金額。依訂單金額由高到低排序;金額相同時依訂單編號由小到大。資料表
customers(客戶):customer_id客戶編號(文字,主鍵)、name姓名(文字)、city城市(文字,沒填為 NULL)、signup_date註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。資料表
orders(訂單):order_id訂單編號(整數,主鍵)、customer_id客戶編號(文字)、order_date下單日(文字,格式 'YYYY-MM-DD')、status狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。資料表
order_items(訂單明細,一張訂單可有多列):order_id訂單編號(整數)、product商品名稱(文字)、category類別(文字)、quantity數量(整數)、unit_price單價(整數,元)。同一張訂單裡不會出現重複的商品。本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。
作答登入後可作答 - 02
每位客戶消費含零
中等LEFT JOIN要列出每一位客戶的有效訂單數與消費總額,沒有有效訂單的客戶也要列出、數字顯示 0。有效訂單=
status不是 'cancelled'(沒有明細的有效訂單也算一張,金額 0)。消費總額=有效訂單所有明細的
quantity * unit_price加總。沒有消費的客戶請用COALESCE(SUM(...), 0)顯示整數 0(不要用TOTAL(),它會回傳 0.0)。輸出欄位依序為:
customer_id、name、有效訂單數、消費總額。依消費總額由高到低排序;相同時依客戶編號由小到大。資料表
customers(客戶):customer_id客戶編號(文字,主鍵)、name姓名(文字)、city城市(文字,沒填為 NULL)、signup_date註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。資料表
orders(訂單):order_id訂單編號(整數,主鍵)、customer_id客戶編號(文字)、order_date下單日(文字,格式 'YYYY-MM-DD')、status狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。資料表
order_items(訂單明細,一張訂單可有多列):order_id訂單編號(整數)、product商品名稱(文字)、category類別(文字)、quantity數量(整數)、unit_price單價(整數,元)。同一張訂單裡不會出現重複的商品。本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。
作答登入後可作答 - 03
平均每單金額
中等先彙總再連接要算每位客戶「平均一張訂單花多少錢」。只看有效訂單(
status不是 'cancelled');沒有明細的有效訂單金額算 0,也算一張。注意:直接把訂單和明細接起來再取 AVG,平均的會是「每一列明細」而不是「每一張訂單」。要先算出每張訂單的金額,再以訂單為單位求平均。
只列出至少有一張有效訂單的客戶。輸出欄位依序為:
customer_id、有效訂單數、平均每單金額(ROUND(..., 1))、最大一張訂單的金額。依平均每單金額由高到低排序;相同時依客戶編號由小到大。資料表
customers(客戶):customer_id客戶編號(文字,主鍵)、name姓名(文字)、city城市(文字,沒填為 NULL)、signup_date註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。資料表
orders(訂單):order_id訂單編號(整數,主鍵)、customer_id客戶編號(文字)、order_date下單日(文字,格式 'YYYY-MM-DD')、status狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。資料表
order_items(訂單明細,一張訂單可有多列):order_id訂單編號(整數)、product商品名稱(文字)、category類別(文字)、quantity數量(整數)、unit_price單價(整數,元)。同一張訂單裡不會出現重複的商品。本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。
作答登入後可作答 - 04
類別營收占比
中等占比要看各商品類別在有效訂單(
status不是 'cancelled')的營收裡各占多少。類別營收=該類別明細的
quantity * unit_price加總;占比(百分比)=類別營收 × 100.0 ÷ 全部類別營收合計,ROUND(..., 1)。另外列出這個類別出現在幾張不同的有效訂單裡。輸出欄位依序為:
category、出現的訂單數、類別營收、占比。依類別營收由高到低排序;營收相同時依類別由小到大。資料表
customers(客戶):customer_id客戶編號(文字,主鍵)、name姓名(文字)、city城市(文字,沒填為 NULL)、signup_date註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。資料表
orders(訂單):order_id訂單編號(整數,主鍵)、customer_id客戶編號(文字)、order_date下單日(文字,格式 'YYYY-MM-DD')、status狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。資料表
order_items(訂單明細,一張訂單可有多列):order_id訂單編號(整數)、product商品名稱(文字)、category類別(文字)、quantity數量(整數)、unit_price單價(整數,元)。同一張訂單裡不會出現重複的商品。本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。
作答登入後可作答

送件鴿飛出去了