优化销售报表多查询SQL：减少重复子查询性能优化求助

我编写的销售报表SQL能返回正确结果，但生成Total_Productos、Total_Servicios和Total列时存在大量重复子查询，优化空间很大。调整查询逻辑时遇到报错：Error Code: 1241. Operand should contain 1 column(s)，寻求优化建议。

原查询代码 #

SELECT f.numero, f.fecha, CONCAT(c.nombre, ' ', c.apellido) AS Cliente, 
    (SELECT GROUP_CONCAT(' x', CONVERT(fp2.cantidad, CHAR(10)), ' ', nombre) AS Product
    FROM facturas_productos fp2
    INNER JOIN productos ON productos.codigo = fp2.productos_codigo 
    WHERE fp2.facturas_numero = fp.facturas_numero) AS Productos,
    
    TRUNCATE((SELECT SUM(fp2.cantidad * productos.precio_venta) AS T_Producto
    FROM facturas_productos fp2
    INNER JOIN productos ON productos.codigo = fp2.productos_codigo 
    WHERE fp2.facturas_numero = fp.facturas_numero), 2) AS Total_Productos,
    
    (SELECT GROUP_CONCAT(CONCAT(' x', CONVERT(fs2.cantidad, CHAR(10)), ' ', nombre))
    FROM facturas_servicios fs2
    INNER JOIN servicios ON servicios.codigo = fs2.servicios_codigo 
    WHERE fs2.facturas_numero = fs.facturas_numero) AS Servicios,
    
    TRUNCATE((SELECT SUM(fs2.cantidad * servicios.precio) AS T_Servicio
    FROM facturas_servicios fs2
    INNER JOIN servicios ON servicios.codigo = fs2.servicios_codigo 
    WHERE fs2.facturas_numero = fs.facturas_numero), 2) AS Total_Servicios,
    
    TRUNCATE((SELECT SUM(fs2.cantidad * servicios.precio) AS T_Servicio
    FROM facturas_servicios fs2
    INNER JOIN servicios ON servicios.codigo = fs2.servicios_codigo 
    WHERE fs2.facturas_numero = fs.facturas_numero) + 
    (SELECT SUM(fp2.cantidad * productos.precio_venta) AS T_Producto
    FROM facturas_productos fp2
    INNER JOIN productos ON productos.codigo = fp2.productos_codigo 
    WHERE fp2.facturas_numero = fp.facturas_numero), 2) AS Total
    
FROM facturas f 
INNER JOIN clientes c ON c.ID = f.clientes_ID
INNER JOIN facturas_productos fp ON fp.facturas_numero = f.numero
INNER JOIN facturas_servicios fs ON fs.facturas_numero = f.numero
WHERE f.fecha BETWEEN "2023-01-01" AND "2023-02-04"
GROUP BY f.numero, fp.facturas_numero, fs.facturas_numero, f.fecha, c.nombre, c.apellido
ORDER BY f.numero ASC;

报错代码片段 #

(SELECT GROUP_CONCAT(CONCAT(' x', CONVERT(fs2.cantidad, CHAR(10)), ' ', nombre)), fs2.cantidad * servicios.precio AS Monto
    FROM facturas_servicios fs2
    INNER JOIN servicios ON servicios.codigo = fs2.servicios_codigo 
    WHERE fs2.facturas_numero = fs.facturas_numero) AS Servicios

报错原因解释 #

Error 1241是因为你在SELECT列表的子查询中返回了两列数据（GROUP_CONCAT的结果和Monto），但作为列级子查询，只能返回单一列，因此触发报错。

优化方案 #

核心思路是用预汇总子查询提前计算每个发票对应的产品列表、产品总额，服务列表、服务总额，彻底消除重复查询；同时改用LEFT JOIN避免遗漏只有产品或只有服务的发票。

优化后的查询：

SELECT 
    f.numero,
    f.fecha,
    CONCAT(c.nombre, ' ', c.apellido) AS Cliente,
    prod.Productos,
    TRUNCATE(COALESCE(prod.Total_Productos, 0), 2) AS Total_Productos,
    serv.Servicios,
    TRUNCATE(COALESCE(serv.Total_Servicios, 0), 2) AS Total_Servicios,
    TRUNCATE(COALESCE(prod.Total_Productos, 0) + COALESCE(serv.Total_Servicios, 0), 2) AS Total
FROM facturas f
INNER JOIN clientes c ON c.ID = f.clientes_ID
-- 预汇总所有发票的产品数据
LEFT JOIN (
    SELECT 
        fp.facturas_numero,
        GROUP_CONCAT(' x', CONVERT(fp.cantidad, CHAR(10)), ' ', p.nombre) AS Productos,
        SUM(fp.cantidad * p.precio_venta) AS Total_Productos
    FROM facturas_productos fp
    INNER JOIN productos p ON p.codigo = fp.productos_codigo
    GROUP BY fp.facturas_numero
) prod ON prod.facturas_numero = f.numero
-- 预汇总所有发票的服务数据
LEFT JOIN (
    SELECT 
        fs.facturas_numero,
        GROUP_CONCAT(' x', CONVERT(fs.cantidad, CHAR(10)), ' ', s.nombre) AS Servicios,
        SUM(fs.cantidad * s.precio) AS Total_Servicios
    FROM facturas_servicios fs
    INNER JOIN servicios s ON s.codigo = fs.servicios_codigo
    GROUP BY fs.facturas_numero
) serv ON serv.facturas_numero = f.numero
WHERE f.fecha BETWEEN '2023-01-01' AND '2023-02-04'
ORDER BY f.numero ASC;

优化点说明 #

1. 消除重复查询：通过两个子查询分别预计算产品和服务的汇总数据，主查询直接引用，避免多次重复扫描facturas_productos和facturas_servicios表。
2. 修复数据完整性：原查询用INNER JOIN同时关联产品和服务表，会过滤掉只有产品或只有服务的发票；改用LEFT JOIN保留所有符合日期条件的发票，用COALESCE将缺失值转为0，确保Total计算准确。
3. 简化分组逻辑：原查询GROUP BY包含冗余字段，优化后子查询已完成分组，主查询无需额外分组。

补充信息 #

• 数据库为正向工程创建（大学学习项目）
• 数据库EER图：
• 原查询结果：

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