Hibernate SQLServer2017列在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中

Question

好的，我正在使用hibernate在我的SpringBoot应用程序中运行下面的SQL本机查询，请注意这个查询在SQL Management Studio中运行良好，并返回一些结果。在我的IDE中，我可以使用JDBC驱动程序连接到我的SQL Server2017，我仍然可以运行这个查询并返回一些结果，现在是奇怪的部分，当相同的查询在应用程序中运行时，我会得到以下错误：

SELECT
        CASE WHEN (suc.transaction_time IS NOT NULL) THEN suc.transaction_time ELSE err.transaction_time END as transaction_time,
        CASE WHEN (suc.success_count IS NOT NULL) THEN suc.success_count ELSE 0 END as success_count,
        CASE WHEN (err.error_count IS NOT NULL) THEN err.error_count ELSE 0 END as error_count
FROM
  (
    SELECT
          COUNT(et.status) error_count,
          DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, et.time_in) / 1440) * 1440, 0) AS transaction_time
    FROM
          ESB_TRANSACTION et
    WHERE
          (et.status='ERROR')  AND (et.time_in BETWEEN '2019-01-01 00:00:00' AND '2019-12-12 23:59:59')
    GROUP BY DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, et.time_in) / 1440) * 1440, 0)
  ) err
  RIGHT OUTER JOIN (
    SELECT
          COUNT(et.status) success_count,
          DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, et.time_in) / 1440) * 1440, 0) AS transaction_time
    FROM
          ESB_TRANSACTION et
    WHERE
          (et.status='SUCCESS')  AND (et.time_in BETWEEN '2019-01-01 00:00:00' AND '2019-12-12 23:59:59')
    GROUP BY DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, et.time_in) / 1440) * 1440, 0)
  ) suc ON err.transaction_time = suc.transaction_time
  ORDER BY transaction_time ASC

`public List execute(final String query, final DateRange selectedRange, final int minutes) {
    return (List) repository.getEntityManager()
            .createNativeQuery(query)
            .setParameter("startTime", selectedRange.getFrom())
            .setParameter("endTime", selectedRange.getTo())
            .setParameter("periodInterval", minutes   )
            .getResultList();
  }

        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) {
          String contents = new String(Files.readAllBytes(Paths.get("C:\\Temp\\mssqlserver.sql")));
            ResultSet rs = stmt.executeQuery(contents);
            while (rs.next()) {
                System.out.println(rs.getString("transaction_time") + " =>" + rs.getInt("success_count") +" =>"+ rs.getInt("error_count"));
            }
        }
        catch (IOException | SQLException e) {
            e.printStackTrace();
        }

我实际上得到了适当的输出，那么我的春靴+冬眠有什么问题呢？

Answer 1

试试这个,

SELECT
    CASE WHEN (suc.transaction_time IS NOT NULL) THEN suc.transaction_time ELSE err.transaction_time END as transaction_time,
    CASE WHEN (suc.success_count IS NOT NULL) THEN suc.success_count ELSE 0 END as success_count,
    CASE WHEN (err.error_count IS NOT NULL) THEN err.error_count ELSE 0 END as error_count
FROM
    (
    SELECT
         COUNT(et.status) error_count, et.time_in AS transaction_time
    FROM
        (
        SELECT
            eti.status,
            DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, eti.time_in) / 1440) * 1440, 0) AS time_in
        FROM ESB_TRANSACTION eti
        WHERE
            (eti.status='ERROR')  AND (eti.time_in BETWEEN '2019-01-01 00:00:00' AND '2019-12-12 23:59:59')
        ) et    
    GROUP BY et.time_in
    ) err
RIGHT OUTER JOIN
    (
    SELECT
          COUNT(et.status) success_count, et.time_in AS transaction_time
    FROM
        (
        SELECT eti.status,
               DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, eti.time_in) / 1440) * 1440, 0) AS time_in
        FROM ESB_TRANSACTION eti
        WHERE
            (eti.status='SUCCESS')  AND (eti.time_in BETWEEN '2019-01-01 00:00:00' AND '2019-12-12 23:59:59')
        ) et    
    GROUP BY et.time_in
    ) suc  ON err.transaction_time = suc.transaction_time
ORDER BY transaction_time ASC

注意：我没有SQLServer，也没有在其他数据库上使用它。但它应该按原样运行；如果不是，那么它可能需要一些小的语法修复。然而，结果应该是预期的。无论如何，它肯定会给你一个主意。希望是好的。