在带有聚合函数的JPQL查询中使用参数

Question

我在SpringBoot项目中使用JPQL查询。我有一个带有COUNT函数的查询。查询是：

@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查询参数currYear(仅用参数替换文字，没有其他内容(时，我会得到一个错误：

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

参数替换可能存在一些问题:currYear - scsd.studentDegree.studentGroup.creationYear + scsd.studentDegree.studentGroup.realBeginYear不被识别为相同的"；列"；按部件进行选择和分组

您可以尝试使用CCD_ 2，如在WhatdosSQL子句"；GROUP BY 1"；意思是