如何使用 NHibernate 表达加入分组子查询？

Question

我试图使用 NHibernate 的 Criteria API 来表达 SQL 查询，但我遇到了困难，因为我以数据库为中心的方式思考，而 NHibernate 是以对象为中心的。

SQL（效果很好）：

select outerT.id, outerT.col1, outerT.col2, outerT.col3
from tbl outerT
inner join
    (select max(innerT.id)
     from tbl innerT
     group by innerT.col1) grpT
on outerT.id = grpT.id

本质上，这是一个表与其自身子集的自连接。我想我可以尝试将自连接变成限制：

select outerT.id, outerT.col1, outerT.col2, outerT.col3
from tbl outerT
where outerT.id in (select max(innerT.id) from tbl innerT group by innerT.col1)

但我也不知道如何使用 NHibernate 来表达这一点；我正在与 DetachedCriteria 的 ProjectionList 进行斗争，并希望在按 col1 分组时仅选择 max(id) 。

非常感谢您的建议！

Answer 1

我不知道是否应该将其发布为新答案或将其添加为对原始问题的评论，但我认为我已经解决了此线程中的类似问题：

使用 Critieria API 在 NHibernate 中选择子查询

Answer 2

AFAIK 您根本无法在 NHibernate 中加入子查询，但您可以重新组织查询以使用 EXISTS 或 IN 子句来复制相同的功能。

我意识到这个问题要求使用 Criteria API 来完成此操作，但我想我会发布一个 HQL 版本，这可能会给其他人一些想法。

var results = session.CreateQuery("from Product p where p.Id in (
    select max(p2.id)
from Product p2
group by p2.col1
)")

我还发现这个 JIRA 问题围绕 Criteria API，并且在选择中不包括按列分组。目前看来，使用 Criteria API 根本无法实现您想要的效果。

按属性分组，而不将其添加到 select 子句

更新
使用 Monkey Coders 帖子中的示例看起来您可以执行以下操作：

var subquery = DetachedCriteria.For<Product>("p")
.SetProjection(Projections.ProjectionList()
    .Add(Projections.GroupProperty("p.Col1"))
.Add(Restrictions.EqProperty("p2.Id", Projections.Max("p.Id"));

var query = DetachedCriteria.For<Product>("p2")
.Add(Subqueries.Exists(subquery));

这将生成以下 SQL

select *
from Product p2
where exists (
    select p.col1
    from Product p
    group by p.col1
    having p2.Id=max(p.Id)
)