分组查询

分组查询

Chloe 支持分组查询以及分组后 Having 过滤和聚合查询。

IQuery<Person> q = dbContext.Query<Person>();

IGroupingQuery<Person> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age);
// g = g.AndBy(a => a.Id);  //多个字段分组
g = g.Having(a => a.Age > 1 && Sql.Count() > 0);

g.Select(a => new
{
    a.Age,
    Count = Sql.Count(),
    Sum = Sql.Sum(a.Age),
    Max = Sql.Max(a.Age),
    Min = Sql.Min(a.Age),
    Avg = Sql.Average(a.Age)
}).ToList();
/*
 * SELECT [Person].[Age] AS [Age],COUNT(1) AS [Count],CAST(SUM([Person].[Age]) AS INT) AS [Sum],MAX([Person].[Age]) AS [Max],MIN([Person].[Age]) AS [Min],CAST(AVG([Person].[Age]) AS FLOAT) AS [Avg] 
   FROM [Person] AS [Person] 
   WHERE [Person].[Id] > 0 
   GROUP BY [Person].[Age] 
   HAVING ([Person].[Age] > 1 AND COUNT(1) > 0)
 */