首页 > 数据库 >SqlSugar分组查询

SqlSugar分组查询

时间:2024-01-16 15:12:18浏览次数:34  
标签:Name 查询 分组 Select new SqlFunc Id SqlSugar name

一、分组查询和使用

1.1 语法

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

  var list = db.Queryable<Student>()              .GroupBy(it => new { it.Id, it.Name }) //可以多字段              .Where(it=>it.Id>0)//普通过滤              //.Having(it => SqlFunc.AggregateAvg(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],  //           [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) }

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

1.2  去null(isnull或ifnull)

库中存在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))

1.3 排序统计列

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

 

二、Distinct使用

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

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

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

 

三、分组获取第一条(或几条)

3.1 所有数据库通用写法

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条以上看 标题2 

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

3.3 个别数据库写法

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//只取日期              })             .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();

 

六、强制不参数化 

语法更新:

//新语法 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

七、联表中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+集合的方式(ef类似)

请升级到 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)    //多字段看3.1

 

十、Count加条件

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

十一、不分组使用Count

更多看标题9

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

 

标签:Name,查询,分组,Select,new,SqlFunc,Id,SqlSugar,name
From: https://www.cnblogs.com/yswenli/p/17967706

相关文章

  • SqlSugar分页查询
    同步分页 int pagenumber= 1; // pagenumber是从1开始的不是从零开始的 int pageSize = 20; int totalCount=0; //单表分页 var page = db.Queryable<Student>().ToPageList(pagenumber, pageSize, ref totalCount); //如果SqlServer不想有Rownumb......
  • SqlSugar联表查询
    Join用法语法糖1、2和3在WhereOrderByGroupBySelect用法都一样的,他们区别就在JOIN的方式不一样,其它都一样语法糖1  优点:好理解,5个表以内的联表非常爽,支持功能全缺点: 联表超过5个以上的表后(x,b,c...) 会比较难看,语法糖2可以弥补表和表的左连接  新语法糖 5.0.......
  • SqlSugar入门
    SqlSugar入门创建对象你可以使用SqlSugarClient(new模式)或者 SqlSugarScope(单例) 对数据库进行增、删、查、改等功能注意:除了名字和使用模式不同,功能和API都一模一样SqlSugarClient(new模式)优点:性能比SqlSugarScope有5%左右提升缺点:db不能跨上下文使用,需要new......
  • GOrm多对多(关联查询)
    一、概述现有两张表,一张表代表章节(chapter)、另外一张表代表集(episode)。一个章节中包含多集,一个集中有可能有视频也有可能没有视频,视频表(video)。表结构如下:1.章节表(chapter),ps:一个章节中包含了多集2.集表(episode),集中有视频3.视频表(video) 要求:查......
  • [FAQ] Docker查询出所有的停止容器并移除
     $ dockerrm`dockercontainerls-a--filter"status=exited"|awk'{print$1}'|sed'1,1d'|xargs` Ref:phvia/dkcRef:[Shell]字符截取命令:cut,printf,awk,sedRef:使用nodejs的puppeteer库使用完关闭后,linux上面有很多chrome进程Link:https......
  • Java 将所有的数据信息查询出来 ,进行映射
    查询出所有组织结构的数据,列表显示时进行映射 该代码查询的参数是逗号相隔的多个数据//查询对应组织机构List<Map<String,Object>>deptList=businessManagerMapper.querySysDept();Map<Object,List<Map<String,Object>>>groupMap=deptList.stream().co......
  • sqlserver查询最近失败的任务
    selectjob_id,step_name,message,cast((cast(LEFT(run_date,4)ASVARCHAR)+'-'+SUBSTRING(cast(run_dateASVARCHAR),5,2)+'-'+cast(RIGHT(run_date,2)ASVARCHAR))+'......
  • map根据次数排序,同时取最后一次查询到的附件数据
    1、需求是某某供应商可以参与投标多轮;现要求存在多轮报价取最后一次的报价文件;不存在则取第一次的报价;(第一次的投标逻辑与之后的逻辑不一样,存在不同的表里)2、Hashmap是无序的;treeMap是有序的即:Map<String,Object>map=newTreeMap<String,Object>(Collections.reverseOrder(......
  • 数据库查询如何优化?
    索引优化:索引可以加速查询速度,但是索引的使用也会带来一些开销。因此,需要根据查询的情况对索引进行优化,比如对经常使用的字段进行索引,或者使用覆盖索引等。数据库设计优化:数据库的设计也会影响查询的性能。比如对于经常需要联表查询的情况,可以将需要联表的字段放在同一张表中,或......
  • openGauss学习笔记-198 openGauss 数据库运维-常见故障定位案例-分析查询效率异常降低
    openGauss学习笔记-198openGauss数据库运维-常见故障定位案例-分析查询效率异常降低的问题198.1分析查询效率异常降低的问题198.1.1问题现象通常在几十毫秒内完成的查询,有时会突然需要几秒的时间完成;而通常需要几秒完成的查询,有时需要半小时才能完成。198.1.2处理办法通......