SQL查询重复值问题：子查询正常主查询结果异常

我编写了如下主查询：

SELECT
    facturas.total AS facturas_total,
    notas_credito.total AS notas_credito_total,
    facturas.total - notas_credito.total AS total_dinero
FROM
    (SELECT
         main_venta.periodo AS periodo,
         SUM(main_venta.total) AS total
     FROM  
         main_contribuyente
     LEFT JOIN 
         main_venta ON main_venta.emisor_id = main_contribuyente.id
     LEFT JOIN 
         main_documentotipo ON main_venta.dte_id = main_documentotipo.id
     WHERE 
         main_contribuyente.id = 1
         AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
         AND main_documentotipo.codigo IN (30, 32, 33, 34, 110, 901, 904, 909, 910)
     GROUP BY 
         main_venta.periodo) AS facturas,
    (SELECT
         main_venta.periodo AS periodo,
         SUM(main_venta.total) AS total
     FROM 
         main_contribuyente
     LEFT JOIN 
         main_venta ON main_venta.emisor_id = main_contribuyente.id
     LEFT JOIN 
         main_documentotipo ON main_venta.dte_id = main_documentotipo.id
     WHERE 
         main_contribuyente.id = 1
         AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
         AND main_documentotipo.codigo IN (112, 60, 61)
     GROUP BY 
         main_venta.periodo) AS notas_credito

单独执行第一个子查询：

SELECT
    main_venta.periodo AS periodo,
    SUM(main_venta.total) AS total
FROM 
    main_contribuyente
LEFT JOIN 
    main_venta ON main_venta.emisor_id = main_contribuyente.id
LEFT JOIN 
    main_documentotipo ON main_venta.dte_id = main_documentotipo.id
WHERE 
    main_contribuyente.id = 1
    AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
    AND main_documentotipo.codigo IN (30, 32, 33, 34, 110, 901, 904, 909, 910)
GROUP BY 
    main_venta.periodo

结果中periodo和SUM值均正常；单独执行第二个子查询：

SELECT
    main_venta.periodo as periodo,
    SUM(main_venta.total) as total
FROM 
    main_contribuyente
LEFT JOIN 
    main_venta ON main_venta.emisor_id = main_contribuyente.id
LEFT JOIN 
    main_documentotipo ON main_venta.dte_id = main_documentotipo.id
WHERE 
    main_contribuyente.id = 1
    AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
    AND main_documentotipo.codigo IN (112, 60, 61)
GROUP BY 
    main_venta.periodo

结果同样正常，但执行主查询时出现了重复值问题，请问该如何解决？

问题原因 #

主查询中两个子查询facturas和notas_credito之间未指定连接条件，触发了笛卡尔积——即每个facturas的行都会和每个notas_credito的行配对，从而产生大量重复数据。

解决方法 #

方法1：通过periodo字段关联子查询 #

在主查询中添加基于periodo的JOIN条件，确保同一期间的记录匹配：

SELECT
    facturas.total AS facturas_total,
    COALESCE(notas_credito.total, 0) AS notas_credito_total,
    facturas.total - COALESCE(notas_credito.total, 0) AS total_dinero
FROM
    (SELECT
         main_venta.periodo AS periodo,
         SUM(main_venta.total) AS total
     FROM  
         main_contribuyente
     LEFT JOIN 
         main_venta ON main_venta.emisor_id = main_contribuyente.id
     LEFT JOIN 
         main_documentotipo ON main_venta.dte_id = main_documentotipo.id
     WHERE 
         main_contribuyente.id = 1
         AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
         AND main_documentotipo.codigo IN (30, 32, 33, 34, 110, 901, 904, 909, 910)
     GROUP BY 
         main_venta.periodo) AS facturas
LEFT JOIN
    (SELECT
         main_venta.periodo AS periodo,
         SUM(main_venta.total) AS total
     FROM 
         main_contribuyente
     LEFT JOIN 
         main_venta ON main_venta.emisor_id = main_contribuyente.id
     LEFT JOIN 
         main_documentotipo ON main_venta.dte_id = main_documentotipo.id
     WHERE 
         main_contribuyente.id = 1
         AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
         AND main_documentotipo.codigo IN (112, 60, 61)
     GROUP BY 
         main_venta.periodo) AS notas_credito
ON facturas.periodo = notas_credito.periodo;

这里用LEFT JOIN保证即使某期间无信用票据，也能保留发票记录；COALESCE将NULL值转为0，避免计算时出现异常。

方法2：合并子查询，用条件聚合简化SQL #

将两个子查询合并为一个，通过CASE语句在聚合时区分发票和信用票据，减少表扫描次数：

SELECT
    main_venta.periodo AS periodo,
    SUM(CASE WHEN main_documentotipo.codigo IN (30, 32, 33, 34, 110, 901, 904, 909, 910) THEN main_venta.total ELSE 0 END) AS facturas_total,
    SUM(CASE WHEN main_documentotipo.codigo IN (112, 60, 61) THEN main_venta.total ELSE 0 END) AS notas_credito_total,
    SUM(CASE WHEN main_documentotipo.codigo IN (30, 32, 33, 34, 110, 901, 904, 909, 910) THEN main_venta.total ELSE 0 END) 
    - SUM(CASE WHEN main_documentotipo.codigo IN (112, 60, 61) THEN main_venta.total ELSE 0 END) AS total_dinero
FROM  
    main_contribuyente
LEFT JOIN 
    main_venta ON main_venta.emisor_id = main_contribuyente.id
LEFT JOIN 
    main_documentotipo ON main_venta.dte_id = main_documentotipo.id
WHERE 
    main_contribuyente.id = 1
    AND EXTRACT(YEAR FROM to_date(to_char(main_venta.periodo, '999999'), 'YYYYMM')) = 2023
GROUP BY 
    main_venta.periodo;

这种方式性能更优，同时从根源上避免了笛卡尔积问题。

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