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

🕗 发布于 2024-12-02 11:31 .net ORM SqlSugar 数据库

.NET开源 ORM 框架 SqlSugar 系列

💦万丈高楼平地起，做开发想要技术精进，必须要有扎实的基础功底。基础SQL查询语法一定要牢记于心，才能应对后面更为复杂的形势。

一、分组查询和使用

1.1 基础语法

💥注意事项：只有在聚合对象需要筛选的时候才会用到 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) }

🤖温馨提示：分组查询可以进行 汇总查询 、平均值、最大值、最小值 等操作

1.2 去空值（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 排序统计列

💥注意事项： 需要加 MergeTable才能排序统计过的列

  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 数据库通用写法

注意：该写法只能支持获取1条，如果想分组获取1条以上看 3.2

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

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//只取日期
                   //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)

五、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{}

八、分组取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)
  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}")

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

十二、按季分组

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

原文地址：https://blog.csdn.net/mss359681091/article/details/144163270

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：算法训练营day22(二叉树08:二叉搜索树的最近公共祖先,插入,删除)
下一篇：C#使用ExcelDataReader读取Xlsx文件为DataTable对象

.NET(C#) 如何配置用户首选项及保存用户设置
.NET(C#) 如何配置用户首选项及保存用户设置
阅读更多2024-12-14
【最新】北大数字普惠金融指数数据集-省市县（2011-2023年）
郭峰,王靖一,王芳,孔涛,张勋,程志云.测度中国数字普惠金融发展:指数编制与空间特征[J].经济学(季刊),2020,19(04):1401-1418.时间跨度：省级和城市级指数时间跨度为2011-2
阅读更多2024-12-14
GESP202412 四级【Recamán】题解（AC）
a11ak−1−kkakak−1−kak−1k小杨想知道 Recamán 数列的前n项从小到大排序后的结果。手动计算非常困难，小杨希望你能帮他解决这个问题。
阅读更多2024-12-14
IDEA遇到EasyConnect中的网络资源无法访问的问题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。原文链接：https://blog.csdn.net/wanshanyu_/article/de
阅读更多2024-12-14
双目摄像头标定方法
此时已经完成标定，左下角为反投影误差，右边为外参可视化。将双目左右目拍的图像上传（左右目最好不少于20张）此时回到主页面，即可看到成功导出。把这些误差大的删除即可。
阅读更多2024-12-14
Servlet、omcat服务器架构与工作原理
Servlet是运行在服务器端的Java程序，它的主要职责之一是接收并处理来自客户端（如浏览器）的HTTP请求。当客户端发送一个请求到服务器时，Servlet可以解析请求中的信息，例如请求的URL路径
阅读更多2024-12-14
Vue生命周期钩子函数：深入解析与实践
作为高级Vue前端开发人员，对Vue组件的生命周期钩子函数有着深刻的理解是至关重要的。生命周期钩子函数是指在Vue组件的创建、更新、销毁等过程中，Vue自动调用的一系列方法。通过这些钩子函数，我们可以
阅读更多2024-12-14
安卓开发--使用android studio发布APP
app发布
阅读更多2024-12-14
数据结构与算法学习笔记----拓扑排序
@ author: 明月清了个风。
阅读更多2024-12-14
python 将数据保存到现有的Excel文件的新工作表
out_file = ‘query.xlsx’df1 = pd.DataFrame(out_data)若直接写入：df1.to_excel(out_file, index=False, sheet_n
阅读更多2024-12-14