餐厅系统嵌套MySQL子查询获取有效商品价格报错求助

开发餐厅管理软件时，需统计订单商品数据，同时关联商品的有效价格（价格表tbl_precios_productos中bit_activo=1的记录为当前有效价格，调价时将旧价格设为bit_activo=0并新增新记录）。

单独查询单商品（如ID=10）有效价格的SQL可正常运行：

SELECT 
`tbl_precios_productos`.`dbl_precio`
    FROM `tbl_precios_productos` 
    left join `tbl_prods_x_orden` on `tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`
    where `tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`
    and `tbl_precios_productos`.`bit_activo` = 1
    And `tbl_precios_productos`.`int_tipo_precio` = 1
    and `tbl_prods_x_orden`.`int_producto_id` = 10 /* 商品ID */
    GROUP BY `tbl_precios_productos`.`dbl_precio`

但将该查询嵌套进订单商品统计主查询时，触发**“子查询返回多于一个结果”**错误，主SQL如下：

SELECT   
ANY_VALUE( `tbl_productos`.`id_producto`) AS `ID Prod`,  
ANY_VALUE( `tbl_productos`.`chr_nombre_prod`) AS `Producto`,  
SUM(ANY_VALUE( `tbl_prods_x_orden`.`int_cantidad`)) AS `Cantidad`, 
(SELECT 
`tbl_precios_productos`.`dbl_precio`
    FROM `tbl_precios_productos` 
    left join `tbl_prods_x_orden` on `tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`
    where `tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`
    and `tbl_precios_productos`.`bit_activo` = 1
    And `tbl_precios_productos`.`int_tipo_precio` = 1
    and `tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`
    GROUP BY `tbl_precios_productos`.`dbl_precio`) AS `Precio`,
ANY_VALUE( `tbl_tipos_precios`.`id_tipo_precio`) AS `Tipo Precio`,  
ANY_VALUE( `tbl_tipos_precios`.`chr_nombre_precio`) AS `CHRTipoPrecio`,  
ANY_VALUE( `tbl_ordenes_cerradas`.`int_forma_pago`) AS `ID TPago`,  
ANY_VALUE( `tbl_formas_pago`.`chr_forma_pago`) AS `Tipo Pago`,  
ANY_VALUE( `tbl_productos`.`fl_ordenar`) AS `Ordenar`  
From  `tbl_prods_x_orden` 
LEFT JOIN `tbl_productos` ON `tbl_prods_x_orden`.`int_producto_id` = `tbl_productos`.`id_producto` 
LEFT JOIN `tbl_precios_productos` ON (((`tbl_prods_x_orden`.`int_producto_id` = `tbl_precios_productos`.`id_producto`) And ( `tbl_precios_productos`.`int_tipo_precio` = 1)))
LEFT JOIN `tbl_precio_tipo_ordenes` ON `tbl_prods_x_orden`.`int_orden_id` = `tbl_precio_tipo_ordenes`.`id_orden`  
LEFT JOIN `tbl_tipos_precios` ON `tbl_tipos_precios`.`id_tipo_precio` = `tbl_precio_tipo_ordenes`.`id_tipo_precio`  
LEFT JOIN `tbl_ordenes_cerradas` ON `tbl_ordenes_cerradas`.`id_orden_id` = `tbl_prods_x_orden`.`int_orden_id`  
left join `tbl_formas_pago` ON `tbl_ordenes_cerradas`.`int_forma_pago` = `tbl_formas_pago`.`id_forma_pago`  
WHERE  `tbl_productos`.`int_activo` = 1  
And `tbl_prods_x_orden`.`bool_activo` = '1'  
and `tbl_ordenes_cerradas`.`int_forma_pago` = 3
And `tbl_ordenes_cerradas`.`id_control_fecha` >= 101  
And `tbl_ordenes_cerradas`.`id_control_fecha` <= 101
group BY `ID Prod`, `Tipo Precio`, `ID TPago`  
order by `Ordenar`

需求：通过商品ID关联对应有效价格，解决子查询报错问题，补充tbl_precios_productos结构：id、id_producto、valid（对应bit_activo）、date_set。

1. 子查询核心问题 #

嵌套的子查询未与主查询的商品ID关联，会返回所有商品的有效价格集合，而非当前行对应的单个价格，因此触发“返回多于一个结果”错误。此外，子查询中冗余关联tbl_prods_x_orden完全没必要，直接从价格表按商品ID过滤即可。

2. 修正方案 #

方案一：关联主查询商品ID，确保子查询返回单行 #

修改嵌套子查询，添加与主查询tbl_prods_x_orden.int_producto_id的关联条件，同时简化逻辑：

SELECT   
ANY_VALUE( `tbl_productos`.`id_producto`) AS `ID Prod`,  
ANY_VALUE( `tbl_productos`.`chr_nombre_prod`) AS `Producto`,  
SUM(`tbl_prods_x_orden`.`int_cantidad`) AS `Cantidad`, -- 无需嵌套ANY_VALUE，直接SUM聚合数量
(SELECT 
`tbl_precios_productos`.`dbl_precio`
    FROM `tbl_precios_productos` 
    where `tbl_precios_productos`.`id_producto` = `tbl_prods_x_orden`.`int_producto_id` -- 关联主查询当前商品ID
    and `tbl_precios_productos`.`bit_activo` = 1
    And `tbl_precios_productos`.`int_tipo_precio` = 1
    LIMIT 1) AS `Precio`, -- 加LIMIT 1确保返回单行，避免同一商品多条有效价格的异常
ANY_VALUE( `tbl_tipos_precios`.`id_tipo_precio`) AS `Tipo Precio`,  
ANY_VALUE( `tbl_tipos_precios`.`chr_nombre_precio`) AS `CHRTipoPrecio`,  
ANY_VALUE( `tbl_ordenes_cerradas`.`int_forma_pago`) AS `ID TPago`,  
ANY_VALUE( `tbl_formas_pago`.`chr_forma_pago`) AS `Tipo Pago`,  
ANY_VALUE( `tbl_productos`.`fl_ordenar`) AS `Ordenar`  
From  `tbl_prods_x_orden` 
LEFT JOIN `tbl_productos` ON `tbl_prods_x_orden`.`int_producto_id` = `tbl_productos`.`id_producto` 
LEFT JOIN `tbl_precio_tipo_ordenes` ON `tbl_prods_x_orden`.`int_orden_id` = `tbl_precio_tipo_ordenes`.`id_orden`  
LEFT JOIN `tbl_tipos_precios` ON `tbl_tipos_precios`.`id_tipo_precio` = `tbl_precio_tipo_ordenes`.`id_tipo_precio`  
LEFT JOIN `tbl_ordenes_cerradas` ON `tbl_ordenes_cerradas`.`id_orden_id` = `tbl_prods_x_orden`.`int_orden_id`  
left join `tbl_formas_pago` ON `tbl_ordenes_cerradas`.`int_forma_pago` = `tbl_formas_pago`.`id_forma_pago`  
WHERE  `tbl_productos`.`int_activo` = 1  
And `tbl_prods_x_orden`.`bool_activo` = '1'  
and `tbl_ordenes_cerradas`.`int_forma_pago` = 3
And `tbl_ordenes_cerradas`.`id_control_fecha` >= 101  
And `tbl_ordenes_cerradas`.`id_control_fecha` <= 101
group BY `ID Prod`, `Tipo Precio`, `ID TPago`  
order by `Ordenar`

方案二：改用JOIN关联有效价格表（更高效） #

直接在主查询中关联过滤后的有效价格表，避免嵌套子查询，逻辑更清晰：

SELECT   
ANY_VALUE( `tbl_productos`.`id_producto`) AS `ID Prod`,  
ANY_VALUE( `tbl_productos`.`chr_nombre_prod`) AS `Producto`,  
SUM(`tbl_prods_x_orden`.`int_cantidad`) AS `Cantidad`, 
ANY_VALUE(`active_prices`.`dbl_precio`) AS `Precio`,
ANY_VALUE( `tbl_tipos_precios`.`id_tipo_precio`) AS `Tipo Precio`,  
ANY_VALUE( `tbl_tipos_precios`.`chr_nombre_precio`) AS `CHRTipoPrecio`,  
ANY_VALUE( `tbl_ordenes_cerradas`.`int_forma_pago`) AS `ID TPago`,  
ANY_VALUE( `tbl_formas_pago`.`chr_forma_pago`) AS `Tipo Pago`,  
ANY_VALUE( `tbl_productos`.`fl_ordenar`) AS `Ordenar`  
From  `tbl_prods_x_orden` 
LEFT JOIN `tbl_productos` ON `tbl_prods_x_orden`.`int_producto_id` = `tbl_productos`.`id_producto` 
-- 关联预先过滤的有效价格表
LEFT JOIN (
    SELECT `id_producto`, `dbl_precio`
    FROM `tbl_precios_productos`
    WHERE `bit_activo` = 1 AND `int_tipo_precio` = 1
) AS active_prices ON active_prices.id_producto = tbl_prods_x_orden.int_producto_id
LEFT JOIN `tbl_precio_tipo_ordenes` ON `tbl_prods_x_orden`.`int_orden_id` = `tbl_precio_tipo_ordenes`.`id_orden`  
LEFT JOIN `tbl_tipos_precios` ON `tbl_tipos_precios`.`id_tipo_precio` = `tbl_precio_tipo_ordenes`.`id_tipo_precio`  
LEFT JOIN `tbl_ordenes_cerradas` ON `tbl_ordenes_cerradas`.`id_orden_id` = `tbl_prods_x_orden`.`int_orden_id`  
left join `tbl_formas_pago` ON `tbl_ordenes_cerradas`.`int_forma_pago` = `tbl_formas_pago`.`id_forma_pago`  
WHERE  `tbl_productos`.`int_activo` = 1  
And `tbl_prods_x_orden`.`bool_activo` = '1'  
and `tbl_ordenes_cerradas`.`int_forma_pago` = 3
And `tbl_ordenes_cerradas`.`id_control_fecha` >= 101  
And `tbl_ordenes_cerradas`.`id_control_fecha` <= 101
group BY `ID Prod`, `Tipo Precio`, `ID TPago`  
order by `Ordenar`

3. 额外优化点 #

• 原主查询中SUM(ANY_VALUE( tbl_prods_x_orden.int_cantidad))写法冗余，直接SUM(tbl_prods_x_orden.int_cantidad)即可实现数量求和。
• 建议在tbl_precios_productos表的id_producto、bit_activo、int_tipo_precio字段上建立联合索引，提升价格查询效率。
• 若存在同一商品多条bit_activo=1的异常数据，可通过ORDER BY date_set DESC LIMIT 1确保取最新设置的价格。

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