MySQL（Doctrine）统计结果重复，疑因分组问题求排查方案

我碰到过一模一样的问题！这是典型的多表一对多关联产生笛卡尔积导致的统计值重复——当你同时关联invoice_items（一个发票对应多个明细）和invoice_payments（一个发票对应多个付款记录）时，两个表的行会交叉组合，比如一个发票有2个明细、3个付款，关联后会生成6行，这时候计算SUM(invoice_items.amount)就会被重复计算3次，SUM(invoice_payments.amount)被重复计算2次，自然就错了。

纯MySQL解决方案 #

核心思路是先对每个一对多的表做聚合子查询，拿到每个发票的总金额和总付款，再和主表关联，避免笛卡尔积：

SELECT 
    i.id,
    i.invoice_number,
    COALESCE(ii.total_amount, 0) AS total_amount,
    COALESCE(ip.total_paid, 0) AS total_paid,
    (COALESCE(ii.total_amount, 0) - COALESCE(ip.total_paid, 0)) AS outstanding_balance
FROM invoices i
-- 子查询预计算每个发票的总金额
LEFT JOIN (
    SELECT invoice_id, SUM(amount) AS total_amount
    FROM invoice_items
    GROUP BY invoice_id
) ii ON i.id = ii.invoice_id
-- 子查询预计算每个发票的总付款
LEFT JOIN (
    SELECT invoice_id, SUM(amount) AS total_paid
    FROM invoice_payments
    GROUP BY invoice_id
) ip ON i.id = ip.invoice_id
-- 过滤未付款或未全额付款的发票
WHERE (COALESCE(ii.total_amount, 0) - COALESCE(ip.total_paid, 0)) > 0
   OR COALESCE(ip.total_paid, 0) = 0;

这里用COALESCE处理没有明细或没有付款的情况，避免出现NULL值影响计算。

Doctrine ORM解决方案 #

方式一：QueryBuilder/DQL子查询 #

和纯SQL思路一致，用子查询预聚合后再关联：

use Doctrine\ORM\EntityManagerInterface;

public function getOutstandingInvoices(EntityManagerInterface $em)
{
    $qb = $em->createQueryBuilder();

    // 子查询：计算每个发票的总金额
    $itemSubquery = $qb->createQueryBuilder()
        ->select('ii.invoice, SUM(ii.amount) as totalAmount')
        ->from('App\Entity\InvoiceItem', 'ii')
        ->groupBy('ii.invoice')
        ->getDQL();

    // 子查询：计算每个发票的总付款
    $paymentSubquery = $qb->createQueryBuilder()
        ->select('ip.invoice, SUM(ip.amount) as totalPaid')
        ->from('App\Entity\InvoicePayment', 'ip')
        ->groupBy('ip.invoice')
        ->getDQL();

    // 主查询
    $query = $qb->select('i, iiSub.totalAmount, ipSub.totalPaid')
        ->from('App\Entity\Invoice', 'i')
        ->leftJoin('(' . $itemSubquery . ')', 'iiSub', 'WITH', 'i = iiSub.invoice')
        ->leftJoin('(' . $paymentSubquery . ')', 'ipSub', 'WITH', 'i = ipSub.invoice')
        ->where($qb->expr()->gt(
            $qb->expr()->diff(
                $qb->expr()->coalesce('iiSub.totalAmount', 0),
                $qb->expr()->coalesce('ipSub.totalPaid', 0)
            ),
            0
        ))
        ->orWhere($qb->expr()->eq($qb->expr()->coalesce('ipSub.totalPaid', 0), 0))
        ->getQuery();

    return $query->getResult();
}

方式二：实体类用@ORM\Formula自动计算 #

如果你的业务场景不需要频繁修改统计逻辑，可以在Invoice实体里直接定义聚合字段，Doctrine会自动生成子查询：

// src/Entity/Invoice.php
namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="App\Repository\InvoiceRepository")
 */
class Invoice
{
    // ... 其他字段和关联

    /**
     * @ORM\Formula("(SELECT SUM(ii.amount) FROM invoice_items ii WHERE ii.invoice_id = id)")
     * @var float|null
     */
    private $totalAmount;

    /**
     * @ORM\Formula("(SELECT SUM(ip.amount) FROM invoice_payments ip WHERE ip.invoice_id = id)")
     * @var float|null
     */
    private $totalPaid;

    // 对应的getter方法
    public function getTotalAmount(): ?float
    {
        return $this->totalAmount;
    }

    public function getTotalPaid(): ?float
    {
        return $this->totalPaid;
    }

    public function getOutstandingBalance(): ?float
    {
        return ($this->getTotalAmount() ?? 0) - ($this->getTotalPaid() ?? 0);
    }
}

之后查询时直接过滤getOutstandingBalance() > 0或者getTotalPaid() == 0即可，完全不用手动处理关联逻辑。

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