我需要将此自连接转换为 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 语句,而不是自连接。
Edit 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)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)