SQL虛擬表作統計實際應用 ~ 單一訂單 各分類的商品銷售數量統計

資料來源: 自己+AI:[https://chatgpt.com/c/6a2baf5a-de2c-83e8-843e-5662a2fc1b9a]

自己寫錯的盲點:

    COUNT()：算筆數
    SUM(item_count)：算銷售數量(統計)
    GROUP BY category_name, category_sid：依分類分組

相關資料表與相關欄位紀錄

    product_category
        SID
        category_code
        category_name
        display_flag
        
    product_category_relation
        category_sid
        product_sid
        
    product_data
        SID
        product_code
        
    order_content_data
        order_no
        item_type [P/T]
        item_sid
        item_count
        del_flag

撰寫SQL過程記錄:

找出會顯示的產品類別和下面所屬產品SID列表

    SELECT a.category_name,b.category_sid,b.product_sid

    FROM product_category AS a JOIN product_category_relation AS b ON a.SID=b.category_sid

    WHERE a.display_flag='Y'

找出會顯示的產品類別和下面所屬產品SID列表 變成虛擬表

    SELECT x.* FROM (
        SELECT a.category_name,b.category_sid,b.product_sid

        FROM product_category AS a JOIN product_category_relation AS b ON a.SID=b.category_sid

        WHERE a.display_flag='Y'
    ) AS x

//SQL查詢結果
20260612-0001 P 101 2 3296 
20260612-0001 P 104 1 3295 
20260612-0001 P 204 1 3265

====

找出單一訂單 所有銷售商品列表  

    SELECT a.order_no,a.item_type,a.item_code,a.item_count,a.item_sid

    FROM order_content_data AS a

    WHERE a.order_no='20260612-0001' AND (a.item_type='P' OR a.item_type='T') AND a.del_flag='N'

找出單一訂單 所有銷售商品列表 變成虛擬表

    SELECT y.* FROM (
        SELECT a.order_no,a.item_type,a.item_code,a.item_count,a.item_sid

        FROM order_content_data AS a

        WHERE a.order_no='20260612-0001' AND (a.item_type='P' OR a.item_type='T') AND a.del_flag='N'
    ) AS y

//SQL查詢結果
原茶 105 3295 
原茶 105 3296 
奶茶系列 33 3265

**********

自己寫合併兩張虛擬表(錯誤版)

    SELECT x.category_name,x.category_sid,COUNT(y.item_count) AS All_Count FROM (
        SELECT a.category_name,b.category_sid,b.product_sid

        FROM product_category AS a JOIN product_category_relation AS b ON a.SID=b.category_sid

        WHERE a.display_flag='Y'
    ) AS x JOIN (
        SELECT a.order_no,a.item_type,a.item_code,a.item_count,a.item_sid

        FROM order_content_data AS a

        WHERE a.order_no='20260612-0001' AND (a.item_type='P' OR a.item_type='T') AND a.del_flag='N'
    ) AS y
    ON x.product_sid=y.item_sid

//SQL查詢結果
原茶 105 3

——-

AI修正會動版本

SELECT
    x.category_name,
    x.category_sid,
    SUM(y.item_count) AS All_Count
FROM (
    SELECT
        a.category_name,
        b.category_sid,
        b.product_sid
    FROM product_category a
    JOIN product_category_relation b
        ON a.SID=b.category_sid
    WHERE a.display_flag='Y'
) AS x
JOIN (
    SELECT
        a.order_no,
        a.item_count,
        a.item_sid
    FROM order_content_data a
    WHERE a.order_no='20260612-0001'
      AND a.item_type IN ('P','T')
      AND a.del_flag='N'
) AS y
ON x.product_sid=y.item_sid
GROUP BY
    x.category_name,
    x.category_sid;    

//SQL查詢結果
原茶      105    3
奶茶系列   33    1