分组查询、重复、去重

分组查询和使用

只有在聚合对象需要筛选的时候才会用到Having，一般分组查询用不到可以去掉

  var list = db.Queryable<Student>()
             .GroupBy(it => new { it.Id, it.Name }) //可以多字段
             .Having(it => SqlFunc.AggregateAvg(it.Id) > 0)//不是聚合函数用Where就可以了
             .Select(it => new { 
                          idAvg = SqlFunc.AggregateAvg(it.Id),
                          count = SqlFunc.AggregateCount(it.Id),  
                           name = it.Name })
             .ToList();
             
//      SELECT     
//           AVG([Id]) AS[idAvg], 
//           [Name] AS[name]  
//               
//               FROM[Student] GROUP BY[Name],[Id] HAVING(AVG([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) }

分组查询可以 进行汇总查询 、平均值、最大、最小等操作

技巧1 ：SqlFunc.AggregateSumNoNull 可以替换 SqlFunc.AggregateSum

原因 ：Sum有null需要加SqlFunc.IsNull(it.id,0)这样就比较麻烦了

AggregateSumNoNull  直接内部处理了IsNull

Distinct使用

一般用来指定字段去重复,查询不重复的值,去重字段

var list = db.Queryable<Student>().Distinct().Select(it => new { it.Name }).ToList();
//SELECT  DISTINCT  [Name] AS [Name]  FROM [STudent]

注意：升级较新版本兼容了rownumber冲突

分组获取第一条（或几条）

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()

2. 开窗函数语法实现（较多库支持）

新版本才支持  5.1.1 

支持数据库：SqlServer、MySql8.0 、Oracle 、PgSql 等数据库支持

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) 
            //前10条用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：根据年和月进行分组

var list = db.Queryable<Order>()
    .Select(it=> new {
        name=it.Name,
        year=it.CreateTime.Year,
        month=it.CreateTime.Month
    })
    .MergeTable()//将查询出来的结果合并成一个新表
    .GroupBy(it => new { it.year, it.month })//对新表进行分组
    .Select(it=>new {
        date=SqlFunc.MergeString(it.year.ToString(),"-",it.month.ToString()),
        count=SqlFunc.AggregateCount(it.name)
    }).
    ToList();

生成的SQL如下：

SELECT 
       CAST([year] AS NVARCHAR(MAX))+@MethodConst0+CAST([month] AS NVARCHAR(MAX)) AS [date] ,
       COUNT([name]) AS [count]
         
              FROM( SELECT [Name] AS [name] , 
                   DateName(Year,[CreateTime]) AS [year] , 
                  DateName(Month,[CreateTime]) AS [month] FROM [Order] ) MergeTable 
              
       GROUP BY [year],[month]

例子2： 根据年月日进行分组

var getOrderBy = db.Queryable<Order>().Select(it=>new  {
                 Id=it.Id,
                 Name=it.Name,//这儿不能写聚合函数，因没分组
                 CreateTime=it.CreateTime.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)

 Count(distinct 字段)

db.Queryable<Order>().Select(it=>SqlFunc.AggregateDistinctCount(it.Id)).ToList()//最新版本支持

db.Queryable<Order>().Select<int>("count(distinct  id)").ToList();

强制不参数化

解决GroupBy参数名不同引起的分组失败

防止出现@p1 @p2 这种变量

//改之前
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(default(int), "'0'"),
                  SqlFunc.MappingColumn(default(int), "'1'")))
                            
        .Select(it => new {
            name=it.Name.Substring(
                  SqlFunc.MappingColumn(default(int), "'0'"),
                   SqlFunc.MappingColumn(default(int), "'1'"))
        })
.First();

//如果用参数化 Group中就会生成 @p1 @p2   Select就会生成 @p3 @p4虽然 p1和p2 值一样，不过Group不认

联表中GroupBy用法

 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

分组取ID+集合的方式

请升级到 5.1.3.38

 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();