只有在聚合对象需要筛选的时候才会用到Having,一般分组查询用不到可以去掉
var list = db.Queryable<Student>() .GroupBy(it => new { it.Id, it.Name }) //可以多字段 .Where(it=>it.Id>0)//普通过滤 //.Having(it => SqlFunc.AggregateCount(it.Id) > 0)//聚合函数过滤 .Select(it => new { idAvg = SqlFunc.AggregateAvg(it.Id??0), count = SqlFunc.AggregateCount(it.Id), name = it.Name }) .ToList(); // SELECT // AVG([Id]) AS[idAvg], // Count(it.Id) // [Name] AS[name] // // FROM[Student] GROUP BY[Name],[Id] Where Id > 0 //Count用法 //SqlFunc.AggregateCount(it.Id) //单个字段用法 (多个单个也能叠加) .GroupBy(it =>SqlFunc.SubString(it.Name,0,1)) .GroupBy(it =>it.Id) //新版本支持了分组带函数 .GroupBy(it=>new { it.Id, name= SqlFunc.ToString(it.Name) }
分组查询可以 进行汇总查询 、平均值、最大、最小等操作
库中存在null如果不处理那么avg和sum将查询不了数据
SqlFunc.AggregateSumNoNull(it.num) //等于 sum(isnull(num,0)) //5.1.4.108-preview31+ SqlFunc.AggregateAvgNoNull(it.num) //等于 avg(isnull(num,0)) //nullable类型也可以用??去除null SqlFunc.AggregateSum(it.num??0)// avg(isnull(num,0)) //原始用法 SqlFunc.AggregateSum(SqlFunc.Isnull(it.num,0))// avg(isnull(num,0))
var list = db.Queryable<Student>() .GroupBy(it => new { it.Id, it.Name }) .Where(it=>it.Id>0) .Select(it => new { idAvg = SqlFunc.AggregateAvg(it.Id??0), count = SqlFunc.AggregateCount(it.Id), name = it.Name }) .MergeTable()//需要加MergeTable才能排序统计过的列 .OrderBy(it=>it.count) .ToList();
一般用来指定字段去重复,查询不重复的值,去重字段
var list = db.Queryable<Student>().Distinct().Select(it => new { it.Name }).ToList(); //SELECT DISTINCT [Name] AS [Name] FROM [STudent]
注意:升级较新版本兼容了rownumber冲突
var list=db.Queryable<Order>() .GroupBy(it => it.Name)//MergeTable之前不要有OrderBy .Select(it => new { name = it.Name, id = SqlFunc.AggregateMax(it.Id) }) .MergeTable() .LeftJoin<Order>((a, b) => a.id == b.Id) //OrderBy((a,b)=a.Id) .Select((a, b) => b).ToList(); // SELECT [b].* // FROM // (SELECT*FROM(SELECT [Name]AS[name],MAX([Id]) AS [id] FROM [Order] GROUP BY [Name]) MergeTable )[a] // Left JOIN // [Order] [b] ON ( [a].[id] = [b].[Id] )
该写法只能支持获取1条,如果想分组获取1条以上看 3.2
新版本才支持 5.1.1
支持数据库:SqlServer、MySql8.0+、Oracle 、PgSql、达梦、金仓 等数据库支持
说明: partition by name 就等于 group by name
var test48 = db.Queryable<Order>().Select(it => new { index2 = SqlFunc.RowNumber(it.Id,it.Name),//order by id partition by name //多字段排序 order by id asc ,name desc //SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc ",$"{it.Name}") price=it.Price, date=it.CreateTime }) .MergeTable()//将结果合并成一个表 .Where(it=>it.index2==1) //相同的name只取一条记录 //前20条用Where(it=>it.index2=<=20) .ToList(); //SELECT * FROM // (SELECT //row_number() over( partition by [Name] order by [Id]) AS [index2], //[Price] AS [price] , //[CreateTime] AS [date] FROM [Order] // ) MergeTable WHERE ( [index2] = 1 ) //多个字段 5.1.2-preview01 SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc " , $"{it.Id},{it.Name}") //partition by [id],[Name] order by [Id] asc,[name] desc
//1.个别库独有实现 //像Oracle 、SqlServer 语法糖 db.Queryable<Order>().Take(1).PartitionBy(it=>it.Name).ToList() db.Queryable<Order>().OrderBy(it=>it.id,OrderByType.Desc).Take(1).PartitionBy(it=>it.Name).ToList()
例子1 : 年月分好组简写
var students = db.Queryable<Order>() .GroupBy(it=>it.CreateTime.ToString("yyyy-MM")) .Select(it=>new { Time=it.CreateTime.ToString("yyyy-MM"), Count=SqlFunc.AggregateCount(it.name) }) //如果想在后面OrderBy //.MergeTable().OrderBy(it=>it.Count) .ToList();
例子2: 根据年月日进行分组
var getOrderBy = db.Queryable<Order>().Select(it=>new { Id=it.Id, Name=it.Name,//这儿不能写聚合函数,因没分组 CreateTime=it.CreateTime.Date//只取日期 //DateTime?类型 it.CreateTime.Value.Date }) .MergeTable()//将查询结果转成一个表 .GroupBy(it=>it.CreateTime) .Select(it=>new { id =SqlFunc.AggregateMax(it.Id),crate=it.CreateTime }) .ToList();
例子3:使用SQL语句分组
.GroupBy(it => SqlFunc.MappingColumn(default(string), " CONVERT(varchar(10),t.F_OutTime, 120)")) //生成的Sql如下 //GROUPBY CONVERT(varchar(10),t.F_OutTime, 120)
db.Queryable<Order>().Select(it=>SqlFunc.AggregateDistinctCount(it.Id)).ToList()//最新版本支持 db.Queryable<Order>().Select<int>("count(distinct id)").ToList();
语法更新:
//新语法 5.1.4.64 SqlFunc.MappingColumn<string>("'a'") //生成的Sql是 'a' ,不会是参数化对象 SqlFunc.MappingColumn<int>("1") //生成的Sql是1 //老版本语法 SqlFunc.MappingColumn(default(string),"'a'") SqlFunc.MappingColumn(default(int),"1")
一般解决GroupBy参数名不同引起的分组失败
例如:Group里面是参数@p1=1 Select中是参数 @p2 =1 ,只因参数名不同引起了分组失败
//改之前 var list = db.Queryable<Order>() .GroupBy(it =>it.Name.Substring(0,1)) .Select(it => new { name=it.Name.Substring(0,1)) }) .First(); //改之后 var list = db.Queryable<Order>() .GroupBy(it => it.Name.Substring( SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("1"))) .Select(it => new { name=it.Name.Substring( SqlFunc.MappingColumn<int>("0"), SqlFunc.MappingColumn<int>("1")) }) .First(); //这样生成的Sql就不会有参数化对象了 //SELECT SUBSTRING(`Name`,1 + 0,1) AS `name` FROM `Order` // GROUP BY SUBSTRING(`Name`,1 + 0,1) LIMIT 0,1
db.Queryable<Student>() .LeftJoin<Book>((it,b)=>it.id==b.studentid) .GroupBy((it,b)=> new { it.Id, it.Name }) //可以多字段 .Having((it,b)=> SqlFunc.AggregateAvg(it.Id) > 0)//不是聚合函数用Where就可以了 .Select((it,b)=> new {idAvg=SqlFunc.AggregateAvg(it.Id),name=it.Name})//Select写最后 .ToList(); //GroupBy用到b表那就应该写成 (it,b)=>new {} //没用到b表可以写成这样 it=>new{}
联表查询用法:https://www.donet5.com/Home/Doc?typeId=1185
请升级到 5.1.4.66
//List<T>集合 var list = db.Queryable<Order>() .Where(it=>it.Id>0) .GroupBy(it=>it.CustomId)//根据CustomId分组 .Select(it => new { cusid=it.CustomId, list=SqlFunc.Subqueryable<Order>().Where(s=>s.CustomId==it.CustomId).ToList() }).ToList(); //List<string>集合 var list = db.Queryable<Order>() .Where(it=>it.Id>0) .GroupBy(it=>it.CustomId)//根据CustomId分组 .Select(it => new { cusid=it.CustomId, ids=SqlFunc.Subqueryable<Order>().Where(s=>s.CustomId==it.CustomId).ToList(s=>s.Id) }).ToList();
是group的高级应用 ,他不依赖group可以分组,并且可以多个使用而不需写group
count = SqlFunc.RowCount(),// count (1) over() max= SqlFunc.RowMax(it.num??0),// max(isnull(num,0)) over() min= SqlFunc.RowMin(it.num??0),// min(isnull(num,0)) over() avg= SqlFunc.RowAvg(it.num??0),// avg(isnull(num,0)) over() index = SqlFunc.RowNumber(it.Id), // row_number() over( order by a.`Id`) index = SqlFunc.RowNumber(it.Id,it.Name)// row_number() over( partition by name order by a.`Id`) index = SqlFunc.RowNumber(SqlFunc.Desc(it.Id),it.Name)// row_number() over( partition by name order by a.`Id` desc) index = SqlFunc.Rank //和rownumber类似用法 //多字段看3.1 //多字段排序 //order by id asc ,name desc //partition by name,id //SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc ",$"{it.Name},{it.Id}")
通过sum加三元实现Count加条件统计,Sum(1)等于同Count, 把不想要的改成0这样Sum就是统计的想要的
var list = db.Queryable<Student>() .GroupBy(it => new { it.Name }) .Select(it => new { count= SqlFunc.AggregateSum(it.Id>10?1:0), }) .ToList();
更多看标题9
count = SqlFunc.RowCount(),// count (1) over()
SqlFunc.DateValue(DateTime.Now,DateType.Quarter)//获取季
2016 © donet5.comApache Licence 2.0