使用 Nhibernate 在子查询中仅选择不带 group by 属性的 max 子句 [英] selecting only max clause without group by properties in subquery using Nhibernate

问题描述

我有这样的 SQL 查询:

I have SQL query like this:

select * from dbo.table1 where Id in
(
    select max(id) as id from dbo.table1 group by prop1, prop2, prop3
)

我想创建能够为我执行此操作的 NHibernate 查询.我尝试使用 QueryOver 但它不起作用.你有什么建议吗?

I want to create NHibernate query which is be able to do this for me. I tried to use QueryOver but it doesn't work. Do you have any suggestions how to do it?

推荐答案

NHibernate 甚至支持这种查询.请在文档中查看更多信息:15.8.分离的查询和子查询.我们只需要将查询 (如您的 SQL 片段) 分成两部分:

NHibernate supports even this kind of queries. Please, see more in documentation: 15.8. Detached queries and subqueries. We just have to split the query (as in your SQL snippet) into two parts:

• 内选
• 带有 IN 子句的选择

假设，Questin 中的 dbo.table1 映射到 MyEntity.要创建内部选择，让我们使用 DetachedCriteria

Let's assume, that the dbo.table1 in the Questin is mapped into MyEntity. To create inner select, let's use the DetachedCriteria

EDIT(以分组方式扩展，SqlGroupProjection)

EDIT (extended with the Group by, SqlGroupProjection)

有一个SqlGroupProjection方法的摘录:

分组 SQL 投影，同时指定 select 子句和 group by子句片段

A grouping SQL projection, specifying both select clause and group by clause fragments

// inner select
DetachedCriteria innerSelect = DetachedCriteria
    .For(typeof(MyEntity))
    .SetProjection(
        Projections.ProjectionList()
         .Add(
            Projections.SqlGroupProjection(
              " MAX(ID) ",               // SELECT ... max(ID) only
              " Prop1, Prop2, Prop3",    // GROUP BY ... property1, p2...
              new string[] {"ID"},       // could be empty, while not used for  
              new IType[] { NHibernate.NHibernateUtil.Int32 } // transformation
            )
         )
    ;

注意:我什至提供了最后两个参数，但在这种情况下它们可能为空:new string[], new IType[] {}.这些仅用于转换(从数据到实体的具体化).而事实并非如此，我们只是在构建内部选择...

Note: I've provided even the last two paramters, but in this case they could be empty: new string[], new IType[] {}. These are used only for Transformation (materialization from data into entity). And this is not the case, we are just building inner select...

// the select with IN clause
var result = session.CreateCriteria(typeof(MyEntity))
    .Add(Subqueries.PropertyIn("ID", innerSelect))
    .List<MyEntity>();

也可能与 15.7 相关.预测、聚合和分组

这篇关于使用 Nhibernate 在子查询中仅选择不带 group by 属性的 max 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！