SQLite-分组查询
分组查询
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 INTEGER) AS [Sum],CAST(MAX([Users].[Age]) AS INTEGER) AS [Max],CAST(MIN([Users].[Age]) AS INTEGER) AS [Min],CAST(AVG([Users].[Age]) AS REAL) AS [Avg]
FROM [Users] AS [Users] WHERE [Users].[Id] > 0
GROUP BY [Users].[Age]
HAVING ([Users].[Age] > 1 AND COUNT(1) > 0)
*/