NHibernate +连接到派生表

发布于 2024-08-14 12:07:56 字数 740 浏览 5 评论 0原文

在每个员工存储多行的表中，我想为每个员工提取一行，代表每个员工的最新条目。这就是我手写 SQL 的情况：

SELECT [all the selected columns here]
FROM   Nominations t
    inner join 
    (select max(NominationId) mostRecentNominationId, 
        EmployeeId from Nominations group by EmployeeId) n
        on n.mostRecentNominationId = t_.NominationId

从这样的源数据：

nomination_id       employee_id
-------------------------------
1                   5
2                   5
4                   10
7                   10

这会给我这样的结果：

nomination_id       employee_id
-------------------------------
2                   5
7                   10

我一直无法弄清楚如何通过 NHibernate ICriteria 完成这种类型的查询。有什么想法吗？

原文

In a table that stores multiple rows per employee, I want to pull one row per employee that represents the most recent entry for each employee. Here's where I am with hand-written SQL:

SELECT [all the selected columns here]
FROM   Nominations t
    inner join 
    (select max(NominationId) mostRecentNominationId, 
        EmployeeId from Nominations group by EmployeeId) n
        on n.mostRecentNominationId = t_.NominationId

From source data like this:

nomination_id       employee_id
-------------------------------
1                   5
2                   5
4                   10
7                   10

That'll give me something like this:

nomination_id       employee_id
-------------------------------
2                   5
7                   10

I haven't been able to figure out how to accomplish that type of query via NHibernate ICriteria. Any thoughts?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

从﹋此江山别 2024-08-21 12:07:56

这是您需要做的：

DetachedCriteria dCriteria = DetachedCriteria.For<Nomination>("nomination")
        .SetProjection(Projections.Max("nomination.Id"))
        .Add(Restrictions.EqProperty("nomination.EmployeeId", "employee.Id"));

var nominations = Session.CreateCriteria<Nomination>("nom")
            .CreateCriteria("Employee", "employee")
            .Add(Subqueries.PropertyEq("nom.Id", dCriteria)).List<Nomination>();

这与问题中提供的 SQL 查询并不相同，但它的作用完全相同。

由上述条件查询生成的 SQL 查询是：

SELECT * 
FROM Nomination nom 
inner join Employee employee on nom.EmployeeId=employee.EmployeeId 
WHERE nom.NominationId = 
(SELECT max(nomination.NominationId) as maxID 
   FROM Nomination nomination  
   WHERE nomination.EmployeeId = employee.EmployeeId)

Here is what you need to do:

DetachedCriteria dCriteria = DetachedCriteria.For<Nomination>("nomination")
        .SetProjection(Projections.Max("nomination.Id"))
        .Add(Restrictions.EqProperty("nomination.EmployeeId", "employee.Id"));

var nominations = Session.CreateCriteria<Nomination>("nom")
            .CreateCriteria("Employee", "employee")
            .Add(Subqueries.PropertyEq("nom.Id", dCriteria)).List<Nomination>();

This is not equilevant to the SQL query providfed in the question but it does exactly the same thing.

The SQL query that is generated by the above criteria query is:

SELECT * 
FROM Nomination nom 
inner join Employee employee on nom.EmployeeId=employee.EmployeeId 
WHERE nom.NominationId = 
(SELECT max(nomination.NominationId) as maxID 
   FROM Nomination nomination  
   WHERE nomination.EmployeeId = employee.EmployeeId)

回复收藏 0 原文

~没有更多了~