1) Sql Group by ....
之前是这么写的,因为DateTime是YYYY-MM-DD HH:mm:SS 模式,我只想group 日期。这种写法再mysql,sqlserver、oracle都没问题。
var startTime = new DateTime(start.Year, start.Month, start.Day);
var endTime = new DateTime(end.Year, end.Month, end.Day, 23, 59, 59, 999);
var count = DetachedCriteria.For<User>("user")
.SetProjection(
Projections.ProjectionList()
.Add(Projections.SqlGroupProjection("year(CreateTime)", "year(CreateTime)", new[] { "year" }, new IType[] { NHibernateUtil.Int32 }))
.Add(Projections.SqlGroupProjection("Month(CreateTime)", "Month(CreateTime)", new[] { "Month" }, new IType[] { NHibernateUtil.Int32 }))
.Add(Projections.SqlGroupProjection("day(CreateTime)", "day(CreateTime)", new[] { "day" }, new IType[] { NHibernateUtil.Int32 }))
.Add(Projections.RowCount())
)
.Add(Restrictions.Gt(Projections.Property<User>(s => s.CreateTime), startTime))
.Add(Restrictions.Le(Projections.Property<User>(s => s.CreateTime), endTime))
.GetExecutableCriteria(this.CurrentSession)
.List();
Dictionary<DateTime, int> dictionary = new Dictionary<DateTime, int>();
foreach (object[] objects in count)
{
dictionary.Add(new DateTime((int) objects[0], (int) objects[1], (int) objects[2]), (int) objects[3]);
}
return dictionary;
后来发现还有个可以做就是 date 方法
Projections.SqlFunction("date", NHibernateUtil.Date, Projections.Property(_=>_.CreateTime);
2) 一对多查询
例如