简单分组查询
new DBHelper<DTStudentModel>()
.Select(p => new
{
p.StuCode,
TotalScore = DBMethod.Sum(p.Score)
})
.Select<DTStudentModel>(p => new { p.StuName })
.Join<DTScoreModel, DTStudentModel>(DBJoinType.Left, (k, v) => k.StuCode == v.StuCode)
.GroupBy(p => p.StuCode)
.GroupBy<DTStudentModel>(p => p.StuName)
.ToEntityList<DTStudentModel>();
-- 对应 SQL
select [T_Score].[StuCode],Sum([T_Score].[Score]) as [TotalScore],[T_Student].[StuName] from [T_Score] left join [T_Student] on ([T_Score].[StuCode] = [T_Student].[StuCode]) group by [T_Score].[StuCode],[T_Student].[StuName]
使用 Having 语句,查询总成绩大等于 200 分的学生
new DBHelper<DTStudentModel>()
.Select(p => new
{
p.StuCode,
TotalScore = DBMethod.Sum(p.Score)
})
.Select<DTStudentModel>(p => new { p.StuName })
.Join<DTScoreModel, DTStudentModel>(DBJoinType.Left, (k, v) => k.StuCode == v.StuCode)
.GroupBy(p => p.StuCode)
.GroupBy<DTStudentModel>(p => p.StuName)
.Having(p => DBMethod.Sum(p.Score) >= 200)
.ToEntityList<DTStudentModel>();
-- 对应 SQL
select [T_Score].[StuCode],Sum([T_Score].[Score]) as [TotalScore],[T_Student].[StuName] from [T_Score] left join [T_Student] on ([T_Score].[StuCode] = [T_Student].[StuCode]) group by [T_Score].[StuCode],[T_Student].[StuName] having (Sum([T_Score].[Score]) >= 200)