SQL 進階：單一查詢取得各店鋪最高銷售額商品明細

需求描述

產品表包含：店鋪、價錢、備註。需求：查詢每間店鋪的總銷售額、最高銷售額商品備註、該商品銷售額。

視窗函數解法（標準 SQL，效能最佳）

WITH ranked AS (
    SELECT 
        店鋪,
        備注,
        SUM(價錢) AS 價錢和,
        ROW_NUMBER() OVER (PARTITION BY 店鋪 ORDER BY SUM(價錢) DESC) AS rn
    FROM 產品
    GROUP BY 店鋪, 備注
),
shop_total AS (
    SELECT 店鋪, SUM(價錢和) AS 賣出價錢和
    FROM ranked
    GROUP BY 店鋪
)
SELECT 
    st.店鋪,
    st.賣出價錢和,
    r.備注 AS 賣出價錢最高貨物,
    r.價錢和 AS 最高價格貨物價格之和
FROM shop_total st
JOIN ranked r ON st.店鋪 = r.店鋪 AND r.rn = 1;

MySQL 8.0+ / PostgreSQL / SQL Server 通用寫法

使用 ROW_NUMBER() 視窗函數避免相關子查詢的效能問題，單次掃描完成分組排序。

舊版 MySQL 相容寫法

SELECT 
    c.店鋪,
    c.賣出價錢和,
    b.備注 AS 賣出價錢最高貨物,
    b.價錢和 AS 賣出最高價格貨物價格之和
FROM (
    SELECT 店鋪, SUM(價錢和) AS 賣出價錢和
    FROM (
        SELECT 店鋪, 備注, SUM(價錢) AS 價錢和
        FROM 產品
        GROUP BY 店鋪, 備注
    ) a
    GROUP BY 店鋪
) c
JOIN (
    SELECT 店鋪, 備注, 價錢和
    FROM (
        SELECT 
            店鋪, 備注, SUM(價錢) AS 價錢和,
            @rn := IF(@prev = 店鋪, @rn + 1, 1) AS rn,
            @prev := 店鋪
        FROM (
            SELECT 店鋪, 備注, SUM(價錢) AS 價錢和
            FROM 產品
            GROUP BY 店鋪, 備注
            ORDER BY 店鋪, 價錢和 DESC
        ) t1
        CROSS JOIN (SELECT @rn := 0, @prev := '') vars
    ) t2
    WHERE rn = 1
) b ON c.店鋪 = b.店鋪;

效能優化建議

• 在 (店鋪, 價錢) 建立複合索引加速分組聚合
• 大數據量考慮物化視圖預先聚合
• 避免 SELECT *，只查詢必要欄位