在JPQL查询中使用汇总函数中的参数

Question

我在Springboot项目中使用JPQL查询。我有一个具有计数函数的查询。查询是：

@Query(value =
    "SELECT new ua.edu.chdtu.deanoffice.service.course.selective.statistics.StudentsRegistrationOnCoursesPercent(" +
            "2021 - scsd.studentDegree.studentGroup.creationYear + scsd.studentDegree.studentGroup.realBeginYear, " +
            "COUNT(DISTINCT scsd.studentDegree.id)) " +
            "FROM SelectiveCoursesStudentDegrees AS scsd " +
            "GROUP BY scsd.selectiveCourse.studyYear, scsd.studentDegree.specialization.degree.id, " +
            "scsd.studentDegree.active, " +
            "2021 - scsd.studentDegree.studentGroup.creationYear + scsd.studentDegree.studentGroup.realBeginYear " +
            "having scsd.selectiveCourse.studyYear = :studyYear AND " +
            "scsd.studentDegree.specialization.degree.id = :degreeId AND " +
            "scsd.studentDegree.active = true")
List<StudentsRegistrationOnCoursesPercent> findPercentStudentsWhoChosenSelectiveCourse(@Param("studyYear") int studyYear,
                                                                                           @Param("degreeId") int degreeId);

而且工作正常。 但是，当我将文字2021更改为jpql查询参数：咖喱（仅替代参数，没有别的）时，我会遇到错误：

2022-04-13 15:10:17 [XNIO-2 task-1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: column "studentgro2_.creation_year" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 11
2022-04-13 15:10:17 [XNIO-2 task-1] ERROR u.e.c.d.a.g.ExceptionHandlerAdvice - ERROR
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:503)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:209)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)

我该如何修复它？ 更改的查询如下：

@Query(value =
    "SELECT new ua.edu.chdtu.deanoffice.service.course.selective.statistics.StudentsRegistrationOnCoursesPercent(" +
            ":currYear - scsd.studentDegree.studentGroup.creationYear + scsd.studentDegree.studentGroup.realBeginYear, " +
            "COUNT(DISTINCT scsd.studentDegree.id)) " +
            "FROM SelectiveCoursesStudentDegrees AS scsd " +
            "GROUP BY scsd.selectiveCourse.studyYear, scsd.studentDegree.specialization.degree.id, " +
            "scsd.studentDegree.active, " +
            ":currYear - scsd.studentDegree.studentGroup.creationYear + scsd.studentDegree.studentGroup.realBeginYear " +
            "having scsd.selectiveCourse.studyYear = :studyYear AND " +
            "scsd.studentDegree.specialization.degree.id = :degreeId AND " +
            "scsd.studentDegree.active = true")
List<StudentsRegistrationOnCoursesPercent> findPercentStudentsWhoChosenSelectiveCourse(@Param("studyYear") int studyYear,
                                                                                           @Param("degreeId") int degreeId,
                                                                                           @Param("currYear") int currYear);

Answer 1

参数替代和
：咖喱-SCSD.STUDENTDEGREE.STUDENTGROUP.CREATIONYEAR + SCSD.STUDENTDEGREE.STUDENTGROUP.REALBEGINYEAR
在SELECT和组中不被识别为相同的“列”，

您可以尝试使用组1，如 sql子句“ by 1”是什么意思？