时间戳

首页 > 解决方案 > 如何将查询转换为 HQL？

sql - 如何将查询转换为 HQL？

问题描述

我在 SQL Server 中有这个查询，它使用两个参数startDate和endDate.

您能帮我将其转换为 HQL 以使其独立于数据库吗？

SELECT LeaseTable.CarMake, AVG(LeaseTable.LeaseHours)
FROM
(SELECT lease.carmake, 
    DATEDIFF(HH, case when lease.startDate <@startdate then @startdate else lease.startDate end, dateadd(DD, 1 ,case when lease.endDate > @endDate then @endDate else lease.endDate end)) as LeaseHours
    FROM lease
where 
    lease.startDate <= @endDate AND lease.endDate >= @startdate) as LeaseTable
GROUP BY LeaseTable.carMake

标签： sqlhibernatespring-data-jpahql

解决方案

由于 JPQL 不支持子查询，您必须调整查询以获得结果

您可以像这样编写查询

String query = "SELECT l.carMake ,"
         + "         AVG("
         + "           DATEDIFF("
         + "             HH,"
         + "             CASE WHEN lease.startDate <:startdate THEN :startdate ELSE lease.startDate end,"
         + "             dateadd(DD, 1 ,case WHEN lease.endDate > :endDate THEN :endDate ELSE lease.endDate end)"
         + "            )"
         + "         )"
         + " FROM lease l"
         + " WHERE l.startDate <= :endDate AND l.endDate >= :startdate"
         + " GROUP BY  l.carMake; ";

基本上，将子查询中的date_diff函数取出到AVG函数中

推荐阅读