SQL查询优化：在现有订单查询中添加产品SKU及子分类

你原有的查询已经覆盖了订单基础数据，但存在两个问题：一是SKU字段拼写错误（ProducrSKU应为ProductSKU），二是缺少产品子分类的关联逻辑。以下是修正并补充后的完整SQL语句：

SELECT
    p.order_id,
    p.order_item_id,
    p.order_item_name AS 产品名称,
    p.order_item_type,
    MAX(CASE WHEN pm.meta_key = '_product_id' THEN pm.meta_value END) AS productID,
    MAX(CASE WHEN pm.meta_key = '_qty' THEN pm.meta_value END) AS Qty,
    MAX(CASE WHEN pm.meta_key = '_product_sku' THEN pm.meta_value END) AS ProductSKU, -- 修正拼写错误
    MAX(CASE WHEN pm.meta_key = '_variation_id' THEN pm.meta_value END) AS variationID,
    MAX(CASE WHEN pm.meta_key = '_line_total' THEN pm.meta_value END) AS lineTotal,
    MAX(CASE WHEN pm.meta_key = '_line_subtotal_tax' THEN pm.meta_value END) AS subTotalTax,
    MAX(CASE WHEN pm.meta_key = '_line_tax' THEN pm.meta_value END) AS Tax,
    MAX(CASE WHEN pm.meta_key = '_tax_class' THEN pm.meta_value END) AS taxClass,
    MAX(CASE WHEN pm.meta_key = '_line_subtotal' THEN pm.meta_value END) AS subtotal,
    t.name AS 产品子分类 -- 新增子分类字段
FROM
    wp_woocommerce_order_items AS p
JOIN wp_woocommerce_order_itemmeta AS pm ON p.order_item_id = pm.order_item_id
LEFT JOIN wp_posts AS prod ON MAX(CASE WHEN pm.meta_key = '_product_id' THEN pm.meta_value END) = prod.ID
LEFT JOIN wp_term_relationships AS tr ON prod.ID = tr.object_id
LEFT JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms AS t ON tt.term_id = t.term_id
WHERE
    p.order_item_type = 'line_item'
    AND tt.taxonomy = 'product_cat' -- 筛选产品分类
    AND tt.parent != 0 -- 只取子分类（若需包含父分类可移除此条件）
GROUP BY
    p.order_item_id, p.order_id, p.order_item_name, p.order_item_type, t.name

关键说明： #

• 修正了原查询中SKU字段的拼写错误，确保字段名准确
• 通过关联wp_posts表（WooCommerce产品以自定义文章类型存储），获取产品基础信息
• 关联分类相关的三张表，筛选出产品的子分类（通过tt.parent != 0判断子分类，若需要包含父分类可删除此条件）
• 调整GROUP BY子句，适配新增的子分类字段，避免分组逻辑错误

内容的提问来源于stack exchange，提问作者works 360