sql - 将自联接重写为 JPQL

我需要将此自联接转换为 JPQL:

SELECT s1.*
FROM site AS s1
JOIN (SELECT site_type, MAX(last_update_date) AS LastUpdate
      FROM site
      WHERE site.last_update_date > "2011-02-27 16:57:53"
      GROUP BY site_type) AS s2
ON s1.site_type = s2.site_type
AND s1.last_update_date = s2.LastUpdate
ORDER BY s1.last_update_date DESC

编辑:解决方案:

SELECT s1
FROM Site s1
WHERE s1.lastUpdateDate = ( SELECT MAX(s2.lastUpdateDate)
                            FROM Site s2
                            WHERE s1.siteType = s2.siteType)
AND s1.lastUpdateDate > "2011-02-27 16:57:53"
ORDER BY s1.lastUpdateDate DESC

我在 lastUpdateDate 上使用了一个额外的 select 语句，而不是自连接。

编辑 2: 这是一个更好的解决方案，因为它也包含 NULL 值。唯一的缺点是我不能使用 TypedQuery。

SELECT s.id, s.url, s.siteType, MAX(s.lastUpdateDate) as maxLastUpdateDate
FROM Site s
GROUP BY s.siteType
HAVING (maxLastUpdateDate < "2011-02-27 16:57:53" OR maxLastUpdateDate IS NULL)

最佳答案

SELECT s.id, s.url, s.siteType, MAX(s.lastUpdateDate) as maxLastUpdateDate
FROM Site s
GROUP BY s.siteType
HAVING (maxLastUpdateDate < "2011-02-27 16:57:53" OR maxLastUpdateDate IS NULL)

关于sql - 将自联接重写为 JPQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9533068/