MySQL通过JSON键关联商品表 计算订单总价格SQL实现

该需求可直接通过单条SELECT语句实现，原有代码的核心问题是JSON_TABLE解析逻辑未正确提取JSON键（商品ID），且路径配置存在错误。

可直接运行的SQL代码 #

SELECT
    SUM(prod_num.quantity * (p.net_price + p.tax_price)) AS total_order_price
FROM order_items oi
-- 解析products字段提取所有商品ID，生成行序号
JOIN JSON_TABLE(
    JSON_KEYS(oi.products),
    '$[*]' COLUMNS (
        row_idx FOR ORDINALITY,
        productId VARCHAR(26) PATH '$'
    )
) prod_id
-- 解析products字段提取所有商品购买数量，按相同规则生成行序号
JOIN JSON_TABLE(
    oi.products,
    '$.*' COLUMNS (
        row_idx FOR ORDINALITY,
        quantity INT PATH '$'
    )
) prod_num ON prod_id.row_idx = prod_num.row_idx
-- 关联商品表匹配对应商品的净价、税费
JOIN product p ON p.productId = prod_id.productId
-- 指定要查询的客户ID
WHERE oi.customer_id = '01G51A4EK52RHB361SMXH2D5KL';

实现逻辑说明 #

• 首先筛选order_items表中指定客户ID的所有订单条目记录
• 两次调用JSON_TABLE分别解析JSON格式的products字段：第一次遍历JSON键数组得到所有商品ID，第二次遍历JSON值数组得到所有商品购买数量，两者通过FOR ORDINALITY生成的相同规则行序号关联，得到每个订单条目下的「商品ID-购买数量」对应关系
• 关联product表，通过商品ID匹配得到每个商品的单价（即net_price + tax_price）
• 对所有商品的「购买数量 * 单价」结果求和，最终得到该客户的订单总价格

测试数据结果验证 #

针对提供的初始化测试数据，上述SQL执行返回结果为12600，计算逻辑校验正确：

• 商品01G51A4EK52RHB361SMXH2D5KH总购买量为10+30+30=70，单价为100+20=120，合计金额8400
• 商品01G51A4EK52RHB361SMXH2D5KK总购买量为20，单价为200+10=210，合计金额4200
• 订单总价格为8400+4200=12600

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