【分组去重】.NET开源 ORM 框架 SqlSugar 系列
💥 .NET开源 ORM 框架 SqlSugar 系列 🎉🎉🎉
- 【开篇】.NET开源 ORM 框架 SqlSugar 系列
- 【入门必看】.NET开源 ORM 框架 SqlSugar 系列
- 【实体配置】.NET开源 ORM 框架 SqlSugar 系列
- 【Db First】.NET开源 ORM 框架 SqlSugar 系列
- 【Code First】.NET开源 ORM 框架 SqlSugar 系列
- 【数据事务】.NET开源 ORM 框架 SqlSugar 系列
- 【连接池】.NET开源 ORM 框架 SqlSugar 系列
- 【查询目录】.NET开源 ORM 框架 SqlSugar 系列
- 【查询基础】.NET开源 ORM 框架 SqlSugar 系列
- 【排序用法】.NET开源 ORM 框架 SqlSugar 系列
- 【分组去重】.NET开源 ORM 框架 SqlSugar 系列
- 【联表查询】.NET开源 ORM 框架 SqlSugar 系列
- 【导航查询】.NET开源 ORM 框架 SqlSugar 系列
- 【子查询】.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] desc3.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)//获取季.NET开源 ORM 框架 SqlSugar 系列
- 【开篇】.NET开源 ORM 框架 SqlSugar 系列
- 【入门必看】.NET开源 ORM 框架 SqlSugar 系列
- 【实体配置】.NET开源 ORM 框架 SqlSugar 系列
- 【Db First】.NET开源 ORM 框架 SqlSugar 系列
- 【Code First】.NET开源 ORM 框架 SqlSugar 系列
- 【数据事务】.NET开源 ORM 框架 SqlSugar 系列
- 【连接池】.NET开源 ORM 框架 SqlSugar 系列
- 【查询目录】.NET开源 ORM 框架 SqlSugar 系列
- 【查询基础】.NET开源 ORM 框架 SqlSugar 系列
- 【排序用法】.NET开源 ORM 框架 SqlSugar 系列
- 【分组去重】.NET开源 ORM 框架 SqlSugar 系列
- 【联表查询】.NET开源 ORM 框架 SqlSugar 系列
- 【导航查询】.NET开源 ORM 框架 SqlSugar 系列
- 【子查询】.NET开源 ORM 框架 SqlSugar 系列
- 【嵌套查询】.NET开源 ORM 框架 SqlSugar 系列
