MySql-分组查询

分组查询

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 SIGNED) AS `Sum`,MAX(`Users`.`Age`) AS `Max`,MIN(`Users`.`Age`) AS `Min`,AVG(`Users`.`Age`) AS `Avg` 
   FROM `Users` AS `Users` 
   WHERE `Users`.`Id` > 0 
   GROUP BY `Users`.`Age` 
   HAVING (`Users`.`Age` > 1 AND COUNT(1) > 0)
 */