SQL 多表關聯查詢：店鋪與評論表按平均價格排序分頁完整方案

經典場景：店鋪表 (一) 關聯 評論表 (多)，需根據評論表價格平均值排序分頁，且價格為 0 的記錄不參與平均計算。

標準 SQL 解決方案

SELECT 
    s.*, 
    COALESCE(r.avg_price, 0) AS avg_price
FROM shop s
LEFT JOIN (
    SELECT 
        shop_id, 
        AVG(price) AS avg_price
    FROM review
    WHERE price <> 0  -- 排除價格為 0 的記錄
    GROUP BY shop_id
) r ON s.id = r.shop_id
ORDER BY r.avg_price DESC
LIMIT 20 OFFSET 0;  -- 分頁：第 1 頁，每頁 20 條

關鍵點解析

1. 子查詢預聚合

先在評論表內部按 shop_id 分組計算平均價格，再與店鋪表關聯。避免了大表關聯後再聚合的性能開銷。

2. WHERE price <> 0 過濾

在聚合前過濾掉價格為 0 的記錄，符合「不參與平均計算」需求。注意：AVG() 會自動忽略 NULL，但 0 是有效數值會被計入，故必須顯式過濾。

3. LEFT JOIN 保留無評論店鋪

使用 LEFT JOIN 確保沒有評論的店鋪也能查出，COALESCE(r.avg_price, 0) 將無評論店鋪平均價設為 0，排在最後。

4. 分頁語法跨資料庫差異

性能優化建議

1. 索引優化

-- 評論表必須建立複合索引
CREATE INDEX idx_review_shop_price ON review(shop_id, price);

2. 物化視圖/緩存（高併發場景）

-- 定時刷新的物化視圖
CREATE MATERIALIZED VIEW mv_shop_avg_price AS
SELECT shop_id, AVG(price) AS avg_price
FROM review WHERE price <> 0 GROUP BY shop_id;

-- 查詢時直接關聯視圖，極快

3. 游標分頁（大偏移量優化）

-- 替代 OFFSET，使用上一頁最後一條 avg_price 作為游標
WHERE (r.avg_price < last_avg_price) 
   OR (r.avg_price = last_avg_price AND s.id > last_id)
ORDER BY r.avg_price DESC, s.id
LIMIT 20;

Hibernate / JPA 實現

@Query("SELECT s, COALESCE(AVG(r.price), 0) FROM Shop s " +
       "LEFT JOIN Review r ON s.id = r.shop AND r.price <> 0 " +
       "GROUP BY s.id ORDER BY AVG(r.price) DESC")
Page findShopsWithAvgPrice(Pageable pageable);

總結：子查詢預聚合 + LEFT JOIN + 索引優化 是解決此類「一對多聚合排序分頁」的標準模式。根據資料量選擇是否引入物化視圖或游標分頁進一步提升性能。