SqlServer-分组查询

分组查询

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

IQuery<User> q = context.Query<User>();

IGroupingQuery<User> 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 [Users].[Age] AS [Age],COUNT(1) AS [Count],CAST(SUM([Users].[Age]) AS INT) AS [Sum],MAX([Users].[Age]) AS [Max],MIN([Users].[Age]) AS [Min],CAST(AVG([Users].[Age]) AS FLOAT) AS [Avg] 
   FROM [Users] AS [Users] 
   WHERE [Users].[Id] > 0 
   GROUP BY [Users].[Age] 
   HAVING ([Users].[Age] > 1 AND COUNT(1) > 0)
 */