SqlSugar子查询

1、基础教程

1.1 API目录

*****只查一列*****
//First: SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)      
//max例1: SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Max(s => s.Id)
//max例2: SqlFunc.Subqueryable<School>()..Where(s=>s.Id==st.Id).Select(s=>SqlFunc.AggregateMax(s.Id))      
//min:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Min(s => s.Id)
//avg:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Avg(s => s.Id)
//count:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Count()
//sum:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Sum(s => s.num)
//去重后汇总数量
//SqlFunc.Subqueryable<School>().Where(s => z.Id == st.Id).Select(s=>SqlFunc.AggregateDistinctCount(s.Name))
//逗号分割列 
//SqlFunc.Subqueryable<Order>().Where(z=>z.Id==it.Id).SelectStringJoin(z => z.Name, ",")
//完整用例：看标题8
  
  
*****返回List或者实体*****
新功能：5.1.3.36
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList()
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(it=>new Class(){ Name=it.Name})
//更多看标题4
 
 
*****返回单个实体*****
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).First() 
//更多看标题9
 
 
*****IN、Not in、Exists、Not Exists*****
//看标题2

1.2 where中使用

var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id) )
.Where(st => st.Id == SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id))
.ToList();
      
/*生成的MYSQL语句，如果是SqlServer就是TOP 1
SELECT `st`.`ID`,`st`.`SchoolId`,`st`.`Name`,`st`.`CreateTime` 
     FROM `STudent` st Left JOIN `School` sc ON ( `st`.`ID` = `sc`.`Id` )  
      WHERE ( `st`.`ID` =(SELECT `Id` FROM `School` WHERE ( `Id` = `st`.`ID` ) limit 0,1))
*/

1.3 在select中使用

var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id ))
.Select(st => new{
          name = st.Name,
          id = SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)
       }).ToList();

2、IN和NOT IN的操作

2.1 Exists

Exists 和 in 区别在于 Exists 扩展性更强，支持多个字段（简单的说In能实现的Exists都能实现）

//等同于IN
var getAll7 = db.Queryable<Student>()
.Where(it => SqlFunc.Subqueryable<School>().Where(s =>s.Id==it.Id).Any()).ToList();
 
/*生成的SQL(等于同于it.id in(select id from school)只是写法不一样
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it 
WHERE (EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) )) 
*/
 
 
//等同于NOT IN
var getAll8 = db.Queryable<Student>().Where(it => 
SqlFunc.Subqueryable<School>().Where(s => s.Id == it.Id).NotAny()).ToList();
 
/*生成的SQL
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it  
WHERE (NOT EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) ))
*/
 
//搜索关键词：exits

2.2 In和 Not In (5.0.7.5)

在有GroupBy的情况下==自动转成 in

db.Queryable<Order>()
.Where(it=>it.Id==SqlFunc.Subqueryable<Custom>().GroupBy(z=>z.OrderId).Select(z=>z.OrderId)).ToList()
 
//Sql
//SELECT [Id],[Name] FROM [Order]  
// WHERE  [Id] in ((SELECT [OrderId] FROM [Custom] GROUP BY [OrderId]))

2.3 分页In

db.Queryable<Order>().In(it=>it.Id,db.Queryable<Order>().Skip(1).Take(100).Select(it=>it.Id)).ToList();

3、子查询中使用Sql

4、ToList()集合对象

方式1：简单方式(5.1.3.38)

直接在Select中进行ToList ,

性能: 大于循环数倍，大部分情况性能小于ThenMapper方式和导航，写法简单性能也说的过去

//请升级到 5.1.3.36+ 
 db.Queryable<Order>()
 .Select(it => new
         {
           CustomId = it.CustomId,
           OrderId = it.Id,
           OrderName = it.Name,
           CustomList = SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList()
         })
        .ToList();
 //也可以DTO
 SqlFunc.Subqueryable<Custom>()
           .Where(c=> c.Id == it.CustomId).
            ToList(c=> new Class(){ Name=c.Name}) //转成DTO
 //注意：where  ToList里面 c 的别名要一致
  
  
//只查List<string>这种集合 
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(c=>c.Name)//最新版本
 
 
//自动DTO 5.1.4.70
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList<DTO>()
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(it=>new DTO(){},true);//和Queryable类似

方式2：性能、功能、跨库

该功能在同类框架中性能比较好不会生成垃圾SQL ，在现有结果进行一对多操作

var treeRoot=db.Queryable<Tree>().ToList();//可以联表查询，只要返回List中有关联字段就可以
 
//填充子对象 
//原理： 数据库一次读取 In (list[0].id,list[1].id,list[2].id...) 内存分配到对应Child
//所以无论集合多少条记录  ThenMapper 只会操作一次库  
db.ThenMapper(treeRoot, item =>
 {   
   //用例1：简单填充  
   //参数解释：子表的ParentId和主表的Id 进行关联查询（有Select写到SetContext前面）
   //可以额外加Where写SetContext前面，但是不能带有item对象,item要写SetContext里面
   item.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => item.Id, item).ToList();
     
    
   //用例2：可以多属性
   //item.Child=xxxx
   //item.Parent=xxxx
    
     
   //用例3：类型转换类转成DTO （注意：Select中的关联字段要赋值）
   //item.Child = db.Queryable<Tree>()
   //         .Select(it=>new ViewTree(){ParentId=x.ParentId,Id=i.Id,Name=it.Name }) 
   //         .SetContext(x => x.ParentId, () => item.Id, item).ToList();
    
   //用例4：联表填充
   //item.Child=db.Queryable<Tree>()
   //.LeftJoin<Tree>((i,y)=>i.ParentId==y.Id)
   //.Select((i,y)=>new viewmodel(){ Parentid=i.Parentid..........})
   //.SetContext(x => x.ParentId, () => item.Id, item).ToList();
 });
  
 
  
  
//异步
var treeRoot=await db.Queryable<Tree>().ToListAsync(); 
await db.ThenMapperAsync(treeRoot, async item =>
{   
   //给集合赋值  参数解释  子表的ParentId和主表的Id 进行关联查询
   item.Child =await db.Queryable<Tree>().SetContextAsync(x=>x.ParentId,()=>item.Id, item);
});
 
 
//无限层级
var treeRoot=db.Queryable<Tree>().Where(it => it.Id == 1).ToList();
//第一层
db.ThenMapper(treeRoot, item =>
{
    item.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => item.Id, item).ToList();
});
//第二层
db.ThenMapper(treeRoot.SelectMany(it=>it.Child), it =>
{
    it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList();
});
 
//跨库 
var mydb=db.GetConnection(1);
mydb.ThenMapper(root,item=>{...});

方式3：导航方式

这种已经不属于子查询了，属于另一个查询体系，详细功能：

https://www.donet5.com/Home/Doc?typeId=1188

5、子查询在Join

5.0.5.4+

var list = db.Queryable<Order>().Where(it =>
            SqlFunc.Subqueryable<A>() 
             .LeftJoin<B>((i,y)=>i.ItemId==y.ItemId)
             .InnerJoin<C>((i,y,z) => i.ItemId == z.ItemId)
             .Where((i,y,z)=>i.ItemId==1)
              .Any()
            ).ToList();
//新版本支持了Subquery.join使用 Where<T1,T2>((x,y)=> x.id==y.id)

6、子查询使用别名

如果点不出AS请升级

SqlFunc.Subqueryable<A>().AS("A01") //这样查询的就是 A01表

7、字符串拼接

5.1.1-preview14 逗号拼接

 //sqlerver : FOR XML PATH
 //mysql sqlite : group_concat
 //oracle : listagg
 //pgsql  :string_agg
 var list= db.Queryable<Order>()
   .Select(it => new
            { 
             //names="名字1,名字2,名字3"
              names=SqlFunc.Subqueryable<custom>().
                               Where(z=>z.oid==it.id)
                               .SelectStringJoin(z => z.Name, ",")
            })
           .ToList();
//多字段 不能有null相加 , 字段有null需要加上 z.Name??""
.SelectStringJoin(z => SqlFunc.MergeString(z.Name,"-",z.Id.ToString()), ",")

8、只查一列（top 1）

//First:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)        
//max例1:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Max(s => s.Id)
//max例2:SqlFunc.Subqueryable<School>().Where(s=>s.Id==st.Id).Select(s=>SqlFunc.AggregateMax(s.Id))       
//min:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Min(s => s.Id)
//avg:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Avg(s => s.Id)
//count:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Count()
//sum:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Sum(s => s.num)
//去重后汇总数量
//SqlFunc.Subqueryable<School>().Where(s => z.Id == st.Id).Select(s=>SqlFunc.AggregateDistinctCount(s.Name))
//逗号分割列 
//SqlFunc.Subqueryable<Order>().Where(z=>z.Id==it.Id).SelectStringJoin(z => z.Name, ",")
 
 
//用例
Select(it=>
    new{ 
     id=it.id,
     schoolname=SqlFunc.Subqueryable<School>().Where(s=>s.Id==it.ShoolId).Select(s=>s.Name)  
     //对应的Sql:  
     //schoolname=(SELECT `Name` FROM `School` `s` WHERE (`s`.`Id`=`it`.`ShoolId`)limit 0,1)
   })
    
//如果要查整个对象：看标题9

9、子查询 First()整个对象

//需要升级5.1.4.59
var list= db.Queryable<Order>()
 .Select(it => new
         {
           CustomId = it.CustomId,
           OrderId = it.Id,
           OrderName = it.Name,
           CustomList = SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).First()
         })
        .ToList();
//可以转DTO       
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).First(c=>new xxx(){ yy=c.id })
 
//如果只查一个字段：标题8

posted @ 2024-01-16 15:07 yswenli 阅读(1940) 评论(0) 编辑收藏举报

刷新页面返回顶部

yswenli

http://www.cnblogs.com/yswenli/

SqlSugar子查询

1、基础教程

1.1 API目录

1.2 where中使用

1.3 在select中使用

2、IN和NOT IN的操作

2.1 Exists

2.2 In和 Not In (5.0.7.5)

2.3 分页In

3、子查询中使用Sql

4、ToList()集合对象

方式1：简单方式(5.1.3.38)

方式2：性能、功能、跨库

方式3：导航方式

5、子查询在Join

6、子查询使用别名

7、字符串拼接

8、只查一列（top 1）

9、子查询 First()整个对象

公告

yswenli

http://www.cnblogs.com/yswenli/

SqlSugar子查询

1、基础教程

1.1 API目录

1.2 where中使用

1.3 在select中使用

2、IN和NOT IN的操作

2.1 Exists

2.2 In和 Not In (5.0.7.5)

2.3 分页In

3、子查询中使用Sql

4、ToList()集合对象

方式1：简单方式(5.1.3.38)

方式2：性能、功能、跨库

方式3：导航方式

5、子查询在Join

6、子查询使用别名

7、字符串拼接

8、只查一列 （top 1）

9、子查询 First()整个对象

公告

8、只查一列（top 1）