訂單-商品一對多查詢：單條 SQL 同時取訂單主檔與明細檔

資料模型

orders       (order_id PK, user_id, order_time, total_amt)
order_items  (item_id PK, order_id FK, product_id, qty, price)
products     (product_id PK, name, spec, image_url)

推薦寫法：LEFT JOIN + 條件聚合

SELECT
    o.order_id,
    o.user_id,
    o.order_time,
    o.total_amt,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'item_id', oi.item_id,
            'product_id', oi.product_id,
            'product_name', p.name,
            'qty', oi.qty,
            'price', oi.price
        )
    ) AS items
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE o.order_id IN (?, ?, ...)  -- 批量查詢訂單列表
GROUP BY o.order_id, o.user_id, o.order_time, o.total_amt;

優勢

• **單次往返**：避免 N+1 查詢問題。
• **結構化輸出**：MySQL 5.7+/PostgreSQL 9.4+ 直接輸出 JSON 陣列，前端無需重組。
• **索引友好**：order_items(order_id)、products(product_id) 建立索引即可。

若不支援 JSON 函數（舊版 MySQL）

SELECT o.*, oi.item_id, oi.product_id, oi.qty, oi.price, p.name AS product_name
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE o.order_id IN (...)
ORDER BY o.order_id, oi.item_id;

應用層按 order_id 分組組裝 List<OrderItem>。

分頁查詢訂單列表（含商品數）

SELECT o.*, COUNT(oi.item_id) AS item_cnt
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id
ORDER BY o.order_time DESC
LIMIT 20 OFFSET 0;