Spring Data中SUM(DISTINCT e.discount)报unexpected token错误求助

你遇到的问题是JPQL解析SUM(DISTINCT e.discount)时抛出unexpected token: DISTINCT错误，移除DISTINCT后计算结果错误，但原生SQL在数据库控制台能正常执行。这是因为JPQL的语法规范或JPA实现（比如Hibernate）对关联查询中的SUM(DISTINCT)支持有限，而原生SQL直接由数据库引擎处理，不受JPQL解析限制。

下面提供两种可行的解决方案：

方案一：使用原生SQL替代JPQL #

直接将查询改为原生SQL，通过nativeQuery = true参数指定，这样就能完全复用你在数据库中验证过的SQL语句，同时保留SUM(DISTINCT)逻辑：

@Query(value = "SELECT EXTRACT(MONTH FROM e.estimate_date) AS MONTH, " +
        "EXTRACT(YEAR FROM e.estimate_date) AS YEAR, " +
        "e.status_id, " +
        "ROUND((SUM((i.square_feet*i.depth)*i.price)-SUM(DISTINCT e.discount)), 2) AS TOTAL " +
        "FROM estimate e " +
        "INNER JOIN item i " +
        "ON i.estimate_id = e.id " +
        "WHERE e.seller_id = :sellerId " +
        "AND EXTRACT(YEAR FROM e.estimate_date) = :year " +
        "GROUP BY MONTH, YEAR, e.status_id " +
        "ORDER BY EXTRACT(YEAR FROM e.estimate_date), EXTRACT(MONTH FROM e.estimate_date) DESC",
        nativeQuery = true)

注意：需要将JPQL中的实体属性名替换为数据库表的实际字段名（比如e.estimateDate改为e.estimate_date，e.status.id改为e.status_id）。

方案二：拆分查询，用子查询计算折扣总和 #

如果不想使用原生SQL，可以将折扣的SUM(DISTINCT)计算拆分为子查询，先按分组条件算出每个组的折扣总和，再关联Item的汇总结果：

@Query("SELECT EXTRACT(MONTH FROM e.estimateDate) AS MONTH, " +
        "EXTRACT(YEAR FROM e.estimateDate) AS YEAR, " +
        "e.status.id, " +
        "ROUND((SUM((i.squareFeet*i.depth)*i.price) - ed.discountSum), 2) AS TOTAL " +
        "FROM Estimate e " +
        "INNER JOIN Item i ON i.estimate.id = e.id " +
        "INNER JOIN (" +
        "   SELECT EXTRACT(MONTH FROM ee.estimateDate) AS MONTH, " +
        "          EXTRACT(YEAR FROM ee.estimateDate) AS YEAR, " +
        "          ee.status.id AS statusId, " +
        "          SUM(DISTINCT ee.discount) AS discountSum " +
        "   FROM Estimate ee " +
        "   WHERE ee.seller.id = :sellerId " +
        "   AND EXTRACT(YEAR FROM ee.estimateDate) = :year " +
        "   GROUP BY MONTH, YEAR, ee.status.id" +
        ") ed ON EXTRACT(MONTH FROM e.estimateDate) = ed.MONTH " +
        "   AND EXTRACT(YEAR FROM e.estimateDate) = ed.YEAR " +
        "   AND e.status.id = ed.statusId " +
        "WHERE e.seller.id = :sellerId " +
        "AND EXTRACT(YEAR FROM e.estimateDate) = :year " +
        "GROUP BY MONTH, YEAR, e.status.id, ed.discountSum " +
        "ORDER BY EXTRACT(YEAR FROM e.estimateDate), EXTRACT(MONTH FROM e.estimateDate) DESC")

这个方案通过子查询单独处理Estimate的折扣汇总，避免了在主关联查询中使用SUM(DISTINCT)，让JPQL解析器能够正确处理语法。

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