HQL内连接在同一个表上 [英] HQL Inner join on same table

问题描述

我今天问了一个关于GORM的问题：如何通过最大日期和组在同一时间获取grails中的记录然而，有人认为使用HQL可以轻松实现。但是使用HQL我得到意外令牌错误。当我研究这个时，我发现HQL不允许 INNER JOINS ，除非两个实体之间存在关联： HQL，左连接在同一张表上

I asked an earlier question today which was about GORM: How to fetch records in grails by max date and group at same time However, someone suggested it can be easily achieved using HQL. But using HQL I'm getting unexpected token error. When I researched this I found out that HQL doesn't allow INNER JOINS unless there is an association between two entities: HQL, left join on the same table

失去了。首先，我很沮丧，为什么GORM不支持这样一个简单的查询，现在使用HQL，我的问题是：如何在子集上执行INNER JOIN？

So, I'm lost. To begin with, I'm frustrated why such a simple query isn't supported by GORM and now with HQL my question is: How do I perform an INNER JOIN on a subset?

我曾尝试过：

What I've tried:

意外标记：（靠近第1行，第16列[选择c从（选择
名称，max（dateCreated）作为maxTime从com.mine.Color
group by名称）作为t内联接色c上c.name = t.name和
c.dateCreated = t.maxTime]

unexpected token: ( near line 1, column 16 [ select c from ( select name, max(dateCreated) as maxTime from com.mine.Color group by name ) as t inner join Color c on c.name = t.name and c.dateCreated = t.maxTime ]

我怀疑第二个 Color 没有被检测到，因为包名没有被自动添加到前缀中。所以阅读其他答案我试过了：

I suspected that the second instance of Color was not being detected since the package name didn't get automatically prefixed to it. So reading other answers I tried:

意想不到的标记：（靠近第1行，第16列[选择c从（选择
name，max（dateCreated）作为maxTime从com.mine.Color
group by name）as t，com.mine .Color as c on c.name =
t.name and c.dateCreated = t.maxTime]

unexpected token: ( near line 1, column 16 [ select c from ( select name, max(dateCreated) as maxTime from com.mine.Color group by name ) as t , com.mine.Color as c on c.name = t.name and c.dateCreated = t.maxTime ]

推荐答案

在这里你可以：

Here you go:

Color.executeQuery("""    
    Select c
    From Color c
    where c.dateCreated = (select max(b.dateCreated) from Color b where b.name = c.name)
    """)

这篇关于HQL内连接在同一个表上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！