Skip to content
FreeSql 官方文档FreeSql 官方文档
指南
扩展
服务支持
github icon
  • 指南

      • 查询
        • 分页查询
          • 单表查询
            • 多表查询
              • 嵌套查询
                • WithTempQuery
                  • 场景1:查询分组第一条记录
                    • 场景2:嵌套查询 + Join
                      • 场景3:分组查询嵌套
                        • 场景4:内存数据嵌套
                          • 子表Exists
                            • 子表In
                              • 子表Join
                                • 子表First/Count/Sum/Max/Min/Avg
                                  • ToSql + WithSql
                                  • 分组聚合
                                    • 返回数据 ✨
                                      • 延时加载
                                        • 贪婪加载 ✨
                                          • LinqToSql
                                            • WithSql
                                              • 树型查询 ✨
                                              • DB First
                                                • 表达式函数
                                                  • 事务
                                                    • 过滤器
                                                      • ADO
                                                        • AOP✨
                                                          • 读写分离
                                                            • 分表分库
                                                              • 多租户
                                                                • 性能
                                                                  • 动态操作
                                                                    • 你不知道的功能 ✨

                                                                    嵌套查询

                                                                    author iconnicyecalendar icon2022年7月23日timer icon大约 3 分钟word icon约 930 字

                                                                    此页内容
                                                                    • WithTempQuery
                                                                    • 场景1:查询分组第一条记录
                                                                    • 场景2:嵌套查询 + Join
                                                                    • 场景3:分组查询嵌套
                                                                    • 场景4:内存数据嵌套
                                                                    • 子表Exists
                                                                    • 子表In
                                                                    • 子表Join
                                                                    • 子表First/Count/Sum/Max/Min/Avg
                                                                    • ToSql + WithSql

                                                                    # 嵌套查询

                                                                    # WithTempQuery

                                                                    意见往集:https://github.com/dotnetcore/FreeSql/discussions/1192open in new window

                                                                    需求版本:v3.2.666-preview (最新)

                                                                    # 场景1:查询分组第一条记录

                                                                    fsql.Select<User1>()
                                                                        .Where(a => a.Id < 1000)
                                                                        .WithTempQuery(a => new
                                                                        {
                                                                            item = a,
                                                                            rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
                                                                        })
                                                                        .Where(a => a.rownum == 1)
                                                                        .ToList();
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    8
                                                                    9

                                                                    提示:支持多表嵌套查询,fsql.Select<User1, UserGroup1>()

                                                                    SELECT *
                                                                    FROM (
                                                                        SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
                                                                        FROM [User1] a
                                                                        WHERE a.[Id] < 1000
                                                                    ) a
                                                                    WHERE (a.[rownum] = 1)
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7

                                                                    如果数据库不支持开窗函数,可以使用分组嵌套查询解决:

                                                                    fsql.Select<User1>()
                                                                        .Where(a => a.Id < 1000)
                                                                        .GroupBy(a => a.Nickname)
                                                                        .WithTempQuery(g => new { min = g.Min(g.Value.Id) })
                                                                        .From<User1>()
                                                                        .InnerJoin((a, b) => a.min == b.Id)
                                                                        .ToList((a, b) => b);
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    SELECT b.[Id], b.[Nickname] 
                                                                    FROM ( 
                                                                        SELECT min(a.[Id]) [min] 
                                                                        FROM [User1] a 
                                                                        WHERE a.[Id] < 1000 
                                                                        GROUP BY a.[Nickname] ) a 
                                                                    INNER JOIN [User1] b ON a.[min] = b.[Id]
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7

                                                                    # 场景2:嵌套查询 + Join

                                                                    WithTempQuery + From<T2> 或 FromQuery(ISelect<T2>) 可实现无限联表

                                                                    fsql.Select<User1>()
                                                                        .Where(a => a.Id < 1000)
                                                                        .WithTempQuery(a => new
                                                                        {
                                                                            item = a,
                                                                            rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
                                                                        })
                                                                        .Where(a => a.rownum == 1)
                                                                        //.From<UserExt>() //普通联表
                                                                        .FromQuery(fsql.Select<UserExt>().Where(b => b.Id > 0)) //子查询联表
                                                                        //.FromQuery(fsql.Select<UserExt, UserGroup, xxx>() //子多表查询联表
                                                                        //    .WithTempQuery((a,b,c) => new { ... }))
                                                                        .InnerJoin((a, b) => a.item.Id == b.UserId)
                                                                        .ToList((a, b) => new
                                                                        {
                                                                            user = a.item,
                                                                            rownum = a.rownum,
                                                                            userext = b
                                                                        });
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    8
                                                                    9
                                                                    10
                                                                    11
                                                                    12
                                                                    13
                                                                    14
                                                                    15
                                                                    16
                                                                    17
                                                                    18
                                                                    19
                                                                    SELECT ... 
                                                                    FROM ( 
                                                                        SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum] 
                                                                        FROM [User1] a ) a 
                                                                    INNER JOIN ( 
                                                                        SELECT a.[UserId], a.[Remark] 
                                                                        FROM [TwoTablePartitionBy_UserExt] a 
                                                                        WHERE (a.[UserId] > 0) ) b ON a.[Id] = b.[UserId] 
                                                                    WHERE (a.[rownum] = 1)
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    8
                                                                    9

                                                                    # 场景3:分组查询嵌套

                                                                    fsql.Select<User1>()
                                                                        .WithTempQuery(a => new
                                                                        {
                                                                            user = a,
                                                                            rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
                                                                        })
                                                                        .Where(a => a.rownum == 1)
                                                                        .FromQuery(fsql.Select<UserExt>().Where(b => b.UserId > 0)
                                                                            .GroupBy(b => new { b.UserId, b.Remark })
                                                                            .WithTempQuery(b => new { b.Key, sum1 = b.Sum(b.Value.UserId) }))
                                                                        .InnerJoin((a, b) => a.user.Id == b.Key.UserId)
                                                                        .Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
                                                                        .ToList((a, b) => new
                                                                        {
                                                                            user = a.user,
                                                                            rownum = a.rownum,
                                                                            groupby = b
                                                                        });
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    8
                                                                    9
                                                                    10
                                                                    11
                                                                    12
                                                                    13
                                                                    14
                                                                    15
                                                                    16
                                                                    17
                                                                    18
                                                                    SELECT ... 
                                                                    FROM ( 
                                                                        SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum] 
                                                                        FROM [User] a ) a 
                                                                    INNER JOIN ( 
                                                                        SELECT a.[UserId], a.[Remark], sum(a.[UserId]) [rownum] 
                                                                        FROM [UserExt] a 
                                                                        WHERE (a.[UserId] > 0) 
                                                                        GROUP BY a.[UserId], a.[Remark] ) b ON a.[Id] = b.[UserId] 
                                                                    WHERE (a.[rownum] = 1) AND ((a.[Nickname] = N'name03' OR a.[Nickname] = N'name02'))
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    8
                                                                    9
                                                                    10

                                                                    # 场景4:内存数据嵌套

                                                                    假设跨数据库服务器,或者数据表被缓存过,WithMemory 便可以实现数据表与内存关联查询。

                                                                    var list = new List<User1>();
                                                                    list.Add(new User1 { Id = Guid.NewGuid() });
                                                                    list.Add(new User1 { Id = Guid.NewGuid() });
                                                                    list.Add(new User1 { Id = Guid.NewGuid() });
                                                                    
                                                                    var listSql2 = fsql.Select<UserGroup>()
                                                                        .FromQuery(fsql.Select<User1>().WithMemory(list))
                                                                        .InnerJoin((a, b) => a.Id == b.GroupId)
                                                                        .ToSql();
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    8
                                                                    9
                                                                    SELECT ...
                                                                    FROM [UserGroup] a
                                                                    INNER JOIN (
                                                                        SELECT ...
                                                                        UNION ALL
                                                                        SELECT ...
                                                                        UNION ALL
                                                                        SELECT ...
                                                                    ) b ON a.[Id] = b.[GroupId]
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    8
                                                                    9

                                                                    # 子表Exists

                                                                    fsql.Select<Topic>()
                                                                      .Where(a => fsql.Select<Topic>().As("b").Where(b => b.Id == a.Id).Any())
                                                                      .ToList();
                                                                    //SELECT a.[Id], a.[Title], a.[Clicks], a.[CreateTime], a.[CategoryId]
                                                                    //FROM [Topic] a
                                                                    //WHERE (exists(SELECT 1
                                                                    //    FROM [Topic] b
                                                                    //    WHERE (b.[Id] = a.[Id])
                                                                    //    limit 0,1))
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    8
                                                                    9

                                                                    提示:由于子查询的实体类与上层相同,使用 As("b") 指明别名,以便区分

                                                                    # 子表In

                                                                    fsql.Select<Topic>()
                                                                      .Where(a => fsql.Select<Topic>().As("b").ToList(b => b.Id).Contains(a.Id))
                                                                      .ToList();
                                                                    //SELECT a.[Id], a.[Title], a.[Clicks], a.[CreateTime], a.[CategoryId]
                                                                    //FROM [Topic] a
                                                                    //WHERE (((a.[Id]) in (SELECT b.[Id]
                                                                    //    FROM [Topic] b)))
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7

                                                                    # 子表Join

                                                                    v1.8.0+ string.Join + ToList 实现将子查询的多行结果,拼接为一个字符串,如:"1,2,3,4"

                                                                    fsql.Select<Topic>().ToList(a => new {
                                                                      id = a.Id,
                                                                      concat = string.Join(",", fsql.Select<StringJoin01>().ToList(b => b.Id))
                                                                    });
                                                                    //SELECT a.[Id], (SELECT group_concat(b.[Id] separator ',') 
                                                                    //    FROM [StringJoin01] b) 
                                                                    //FROM [Topic] a
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7

                                                                    # 子表First/Count/Sum/Max/Min/Avg

                                                                    fsql.Select<Category>().ToList(a => new  {
                                                                      all = a,
                                                                      first = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).First(b => b.Id),
                                                                      count = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Count(),
                                                                      sum = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Sum(b => b.Clicks),
                                                                      max = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Max(b => b.Clicks),
                                                                      min = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Min(b => b.Clicks),
                                                                      avg = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Avg(b => b.Clicks)
                                                                    });
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    8
                                                                    9

                                                                    # ToSql + WithSql

                                                                    这是早期提供的嵌套查询方法

                                                                    var sql = fsql.Select<User1>()
                                                                        .Where(a => a.Id < 1000)
                                                                        .ToSql(a => new
                                                                        {
                                                                            item = a,
                                                                            rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
                                                                        }, FieldAliasOptions.AsProperty);
                                                                    
                                                                    fsql.Select<User1>()
                                                                        .WithSql(sql)
                                                                        .Where("a.rownum = 1")
                                                                        .ToList();
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    8
                                                                    9
                                                                    10
                                                                    11
                                                                    12
                                                                    SELECT a.[Id], a.[Nickname] 
                                                                    FROM (
                                                                        SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
                                                                        FROM [User1] a
                                                                        WHERE a.[Id] < 1000
                                                                    ) a
                                                                    WHERE (a.rownum = 1)
                                                                    
                                                                    1
                                                                    2
                                                                    3
                                                                    4
                                                                    5
                                                                    6
                                                                    7
                                                                    edit icon在 GitHub 上编辑此页open in new window
                                                                    上次编辑于: 2022/8/10 00:05:58
                                                                    贡献者: 2881099,igeekfan,luoyunchong
                                                                    上一页
                                                                    多表查询
                                                                    下一页
                                                                    分组聚合
                                                                    Copyright © 2018-present nicye
                                                                    Copyright © 2022 nicye

                                                                    该应用可以安装在你的 PC 或移动设备上。这将使该 Web 应用程序外观和行为与其他应用程序相同。它将在出现在应用程序列表中,并可以固定到主屏幕,开始菜单或任务栏。此 Web 应用程序还将能够与其他应用程序和你的操作系统安全地进行交互。

                                                                    详情