NHibernate聚合子查询

对于HQL世界,一个查询就能解决问题。..

var maxScore = session.CreateQuery(@"select sum(game.Score) 
                                     from Game game 
                                     group by game.Match
                                     order by sum(game.Score) desc")
                      .SetMaxResults(1)
                      .UniqueResult<long>();

希望这能有所帮助。。

更新:对于Criteria世界,可能有更好的结果转换,但这只是有效的:)

var max = (int)session.CreateCriteria<Game>("game")
    .SetProjection(Projections.ProjectionList()
                       .Add(Projections.GroupProperty("game.Match"))
                       .Add(Projections.Sum("game.Score"), "total"))
    .AddOrder(Order.Desc("total"))
    .SetMaxResults(1)
    .SetResultTransformer(Transformers.AliasToEntityMap)
    .UniqueResult<IDictionary>()["total"];

实际上,我会在SQL中这样做:

select top 1 Match.ID, sum(Game.Score) as total 
     from Game inner join Match
     on Game.Match_ID = Match.ID
     group by Match.ID order by total desc

group-by在Criteria/HQL中总是很棘手:因为group-by子句只能返回分组的列和任何其他列的聚合。因此,不可能从group by子句返回整个实体,只返回您分组和聚合的ID。

ISQLQuery sqlQuery1 = NHibernateSessionManager.Instance.GetSession().CreateSQLQuery("select Match.ID, sum(Game.Score) as total from Game inner join Match on Game.Match_ID = Match.ID group by match.ID order by total desc");
sqlQuery1.AddScalar("id", NHibernateUtil.Int32); //
sqlQuery1.AddScalar("total", NHibernateUtil.Int32);
sqlQuery1.SetMaxResults(1);    
var result = sqlQuery1.List();