将自联接重写为JPQL [英] Rewrite self join to JPQL
本文介绍了将自联接重写为JPQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要将此自联接转换为JPQL:
I need to convert this self join to 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语句,而不是自我连接.
Instead of a self-join I used an extra select statement on lastUpdateDate.
,这是一个更好的解决方案,因为它还包含NULL值.唯一的缺点是我不能使用TypedQuery.
Edit 2: this is a better solution since it also includes NULL values. The only downside is that I cannot use a 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)
这篇关于将自联接重写为JPQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文