为什么ORDER BY DATEDIFF使用JPA在SQL Server上引发异常? [英] Why ORDER BY DATEDIFF raise an exception on SQL server using JPA?

问题描述

这可以按预期工作:

jdbcTemplate.query(
        "select datediff(week, '2017-01-02', creation_date), count(*) from somewhere group by datediff(week, '2017-01-02', creation_date)",
        new RowMapper() {
            Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                return [rs.getObject(1), rs.getObject(2)]
            }
        }
)

但是当我在下面运行查询时，我得到了这个异常:

But I get this exception when I run query below:

SQLServerException: Column 'somewhere.creation_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

查询:

def date = new Date(117, 0, 2)
jdbcTemplate.query(
        "select datediff(week, ?, creation_date), count(*) from somewhere group by datediff(week, ?, creation_date)",
        new RowMapper() {
            Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                return [rs.getObject(1), rs.getObject(2)]
            }
        },
        date,
        date
)

JPA实现是Hibernate 5.0.11. 我正在使用Spring& Groovy用于演示，但实际代码是普通的Java + JPA Criteria，显然存在相同的问题.

JPA Implementation is Hibernate 5.0.11. I'm using Spring & Groovy for the demo but actual code is plain java + JPA Criteria, obviously with the same issue.

为什么这不起作用?我只选择Group By子句中的内容.而且日期是相同的:甚至是相同的实例！

Why this is not working? I'm selecting only things that are in Group By clause. And the date is the same: it's even the same instance!

推荐答案

由于查询使用参数标记(?)，因此SQL Optimizer无法知道两个标记将具有相同的值，因此将结果表达式视为与分组表达式不同.

Because the query uses parameter markers (?), the SQL Optimizer cannot know that both markers will have the same value, so the result expression is considered to be different from the grouping expression.

您需要在嵌套语句中计算表达式，因此分组和结果可以使用相同中间值:

You need to calculate the expression in a nested statement, so grouping and result can use the same intermediate value:

def date = new Date(117, 0, 2)
jdbcTemplate.query(
        "select weekdiff, count(*)" +
         " from ( select datediff(week, ?, creation_date) as weekdiff" +
                  " from somewhere" +
              " ) x" +
        " group by weekdiff",
        new RowMapper() {
            Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                return [rs.getObject(1), rs.getObject(2)]
            }
        },
        date
)

这篇关于为什么ORDER BY DATEDIFF使用JPA在SQL Server上引发异常?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！