【分组去重】.NET开源 ORM 框架 SqlSugar 系列

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

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]

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

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

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

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

.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")

//改之前
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{}

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

  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}")

  var list = db.Queryable<Student>().GroupBy(it => new { it.Name  }) .Select(it => new { count= SqlFunc.AggregateSum(it.Id>10?1:0),}).ToList();

 count = SqlFunc.RowCount(),// count (1) over()

 SqlFunc.DateValue(DateTime.Now,DateType.Quarter)//获取季