子查询

SqlSugar在子查询上支持是非常强大,比如 子查询进行联表、IN、NOT IN和SELECT一列等 

1. 子查询查一列

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))
*/

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

同时Subquery也支持了Join

.LeftJoin<OrderItem>((cus,item)=>cus.Id==item.CustomId /* 用 && 追加条件 */)


2.IN和NOT 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` ) )) 
*/


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` ) ))
*/


3、联表子查询

当你要用一个表和一个联进行联查询的时候,或者2个联表在进行联表查询的时候都可以用这种方式实现

var query1 = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(
    JoinType.Left,st.SchoolId==sc.Id
))
.Where(st => st.Name == "jack");

var query2 = db.Queryable<DataTestInfo>();

db.Queryable(query1, query2, (p1, p2) => p1.Id == p2.Int1).Select<ViewModelStudent>().ToList();


//SELECT * FROM 
         //    (SELECT          [st].[ID],[st].[SchoolId],[st].[Name],[st].[CreateTime] 
              //                  FROM [STudent] st 
                //                Left JOIN [School] sc ON ([st].[SchoolId]=[sc].[Id] )   
                 //               WHERE ( [st].[Name] = @Name0Join0 ))  p1 
               //
             //  Inner JOIN
               
            // (SELECT          [Int1],[Int2],[String],[Decimal1],[Decimal2],
               //                [Datetime1],[Datetime2],[Image1],[Image2], 
               //               [Guid1],[Guid2],[Money1],[Money2],[Varbinary1],
            //                  [Varbinary2],[Float1],[Float2] FROM [DataTestInfo] )p2   
              
       //     ON ( [p1].[ID] = [p2].[Int1] )

高版本 最多可以支持3个queryable进行,也支持 left join F12 查看重载


4、多合一子查询

一般多表查询后 动态使用搜索条件和排序 需要使用 st sc等前缀,多表合成一后变成了单表查询 所以都不需要加别名了

var pageJoin = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
    .Select((st,sc) => new
    { 
        id = st.Id,
        name = sc.Name
    })
    .MergeTable()
    .Where(it=>it.id==1).OrderBy("name asc").ToList();
SELECT * FROM 

               (SELECT  
                    [st].[Id] AS [id] , [sc].[Name] AS [name]  
                    FROM [Student] st Left 
                    JOIN [School] sc ON ( [st].[CustomId] = [sc].[Id] )  ) MergeTable--将结果变成一个表  
           
            WHERE ( [id] = @id0 )ORDER BY name asc


5、一合一的子查询

我只是想单表外面在包一层 你可以这样实现,和MergetTable区别在于前者需要加上Select后者不需要

var listx=db.Queryable(db.Queryable<Order>()).ToList();

Sql代码如下:

SELECT t.* FROM  (SELECT [Id],[Name],[Price],[CreateTime],[CustomId] FROM [Order] ) t