我必须使用NHibernate来实现这样的东西
DetachedCriteria maxDateQuery = DetachedCriteria.forClass(FtpStatus.class);
ProjectionList proj = Projections.projectionList();
proj.add(Projections.max("datetime"));
proj.add(Projections.groupProperty("connectionid"));
maxDateQuery.setProjection(proj);
Criteria crit = s.createCriteria(FtpStatus.class);
crit.add(Subqueries.propertiesEq(new String[] {"datetime", "connectionid"}, maxDateQuery));
List<FtpStatus> dtlList = crit.list();
(这段代码来自Hibernate条件查询,使用Max()投影键字段并按外主键分组)
我的问题是Nhibernate没有实现"子查询"。propertiesEq"
crit.add(Subqueries.propertiesEq(new String[] {"datetime", "connectionid"}, maxDateQuery));
你能建议一个解决方法吗?
这里的答案是(谈论NHibernate)使用EXISTS
而不是IN
。查询将如下所示
// the below outer query, has essential feature
// ALIAS name "outer"
// and this is used here, in the "subquery"
var maxDateQuery = DetachedCriteria.For<MyEntity>("myEntity")
.SetProjection(
Projections.ProjectionList()
.Add(Projections.GroupProperty("MyId"))
.Add(Projections.Max("MyDate"))
)
.Add(Restrictions.EqProperty(
Projections.Max("MyDate"),
Projections.Property("outer.MyDate")) // compare inner MAX with outer current
)
.Add(Restrictions.EqProperty("MyId", "outer.MyId")) // inner and outer must fit
;
// here ... the "outer" is essential, because it is used in the subquery
var list = session.CreateCriteria<Contact>("outer")
.Add(Subqueries.Exists(maxDateQuery))
... // e.g. paging
.List<MyEntity>();
将创建如下查询:
FROM [MyEntityTable] outer
WHERE exists
(
SELECT myEntity.MyId
, max(myEntity.MyDate)
FROM [MyEntityTable] myEntity
WHERE myEntity.MyId = outer.MyId // matching ID
GROUP BY myEntity.MyId
HAVING max(myEntity.MyDate) = outer.MyDate // matching max and current date
)
在QueryOver
语法中可以找到相同类型的查询(甚至更复杂):
- 查询HasMany参考