Nhibernate Criteria Group By 子句并选择其他未分组的数据

发布于 2024-09-03 06:52:05 字数 476 浏览 0 评论 0原文

我有一个亲子关系，比如说班级和孩子。每个孩子都属于一个班级并有一个年级。我需要选择每班成绩最低的孩子（或孩子的 id）。

session.CreateCriteria(typeof(Classs))
 .CreateAlias("Children", "children")
 .SetProjection(Projections.ProjectionList()
     .Add(Projections.Min("children.Grade"))
     .Add(Projections.GroupProperty("Id"))
 )
 .List<Object[]>();

此查询返回每个班级的最低成绩，但我不知道哪个孩子获得了该成绩。当我将孩子的 ID 添加到组中时，组是错误的，每个孩子都会被返回。

我希望我们能得到这些孩子的 ID，而不需要对他们进行分组。如果这是不可能的，那么也许有一种方法可以通过子查询来解决这个问题？

原文

I have a parent child relationship, let's say class and children. Each child belongs to a class and has a grade. I need to select the children (or the ids of the children) with the lowest grade per class.

session.CreateCriteria(typeof(Classs))
 .CreateAlias("Children", "children")
 .SetProjection(Projections.ProjectionList()
     .Add(Projections.Min("children.Grade"))
     .Add(Projections.GroupProperty("Id"))
 )
 .List<Object[]>();

This query returns me the lowest grade per class, but I don't know which child got the grade. When I add the children's Id to the group, the group is wrong and every child gets returned.

I was hoping we could just get the id's of those childs without grouping them. If this is not possible, then maybe there is a way to solve this with subqueries?

分享到QQ

分享到微博

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

发布评论

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

浅浅淡淡 2024-09-10 06:52:05

如果最低成绩是唯一的：

select (select id from child c where c.classs_id = classs.id and c.grade = Min(classs.grade)) as child_id, classs.id from classs join child group by classs.id

转换为标准

subquery = detachedCriteria.For<Child>("this")
  .Add(Restrictions.EqProperty("this.Classs", "classs.Id"))
  .Add(Restrictions.EqProperty("this.Grade", Projections.Min("classs.Grade")))
  .SetProjection(Projections.Id())


session.CreateCriteria(typeof(Classs), "classs")
  .CreateAlias("Children", "children")
  .SetProjection(Projections.ProjectionList()
     .Add(Projections.Alias(Projections.SubQuery(subquery)), "child_id")
     .Add(Projections.GroupProperty("Id"))
  )
  .List<object[]>()     // contains child_id and id (classs)

我现在无法测试它，但你应该明白

if min grade is unique:

select (select id from child c where c.classs_id = classs.id and c.grade = Min(classs.grade)) as child_id, classs.id from classs join child group by classs.id

transalted to Criteria

subquery = detachedCriteria.For<Child>("this")
  .Add(Restrictions.EqProperty("this.Classs", "classs.Id"))
  .Add(Restrictions.EqProperty("this.Grade", Projections.Min("classs.Grade")))
  .SetProjection(Projections.Id())


session.CreateCriteria(typeof(Classs), "classs")
  .CreateAlias("Children", "children")
  .SetProjection(Projections.ProjectionList()
     .Add(Projections.Alias(Projections.SubQuery(subquery)), "child_id")
     .Add(Projections.GroupProperty("Id"))
  )
  .List<object[]>()     // contains child_id and id (classs)

i cant test it right now, but you should get the idea

回复收藏 0 原文

~没有更多了~