嵌套查询

2881099 edited this page 2024-11-09 11:11:14 +08:00

Table of Contents

• WithTempQuery
• 场景1：查询分组第一条记录
• 场景2：嵌套查询 + Join
• 场景3：分组查询嵌套
• 场景4：内存数据嵌套
• 场景5：自动分表后分页 分组聚合
• 场景6：FromQuery 多个查询，最后映射查询
• 场景7：报表（每日）
• WithParameters 参数化共享
• 子表Exists
• 子表In
• 子表Join
• 子表First/Count/Sum/Max/Min/Avg
• 子表ToList
• ToSql + WithSql
• 参考资料

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

中文 | English

WithTempQuery

需求版本：v3.2.666+

GroupBy + WithTempQuery(嵌套查询) + FromQuery + UnionAll 组合使用，会让查询功能更加强大、灵活。

注意：FromQuery 多个 WithTempQuery 匿名类型时，确保不是同一个类型（可使用任意属性区分） #1620

场景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();

提示：支持多表嵌套查询，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)

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

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

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]

场景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
    });

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

场景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
    });

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]) [sum1] 
    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'))

场景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() });

fsql.Select<UserGroup>()
    .FromQuery(fsql.Select<User1>().WithMemory(list))
    .InnerJoin((a, b) => a.Id == b.GroupId)
    .ToSql();

SELECT ...
FROM [UserGroup] a
INNER JOIN (
    SELECT ...
    UNION ALL
    SELECT ...
    UNION ALL
    SELECT ...
) b ON a.[Id] = b.[GroupId]

场景5：自动分表后分页 分组聚合

自动分表后，如果有分页的需求 或者分组聚合的需求可以参考以下代码

fsql.Select<Statistics>()
    .Where(a => a.createtime.BetweenEnd(startTime, endTime))  //时间字段定位表
    .WithTempQuery(a => new { item = a })
    .GroupBy(a => a.item.shareId)
    .Count(out var total)
    .Page(dto.page, dto.limit)
    .ToSql(g => new {
        Sid = a.Key,
        Sum1 = g.Sum(g.Value.item.field1),
        Sum2 = g.Sum(g.Value.item.field2),
    });

SELECT a.`shareId` as1, sum( a.`field1` ) as3, sum( a.`field2` ) as5 
FROM ( 
    SELECT ... 
    FROM ( 
        SELECT ... 
        FROM `Statistics_2023` a 
        WHERE (a.`createtime` >= '2022-01-01 00:00:00' AND a.`createtime` < '2023-01-14 00:00:00') 
    ) ftb 
    UNION ALL
    SELECT ... 
    FROM ( 
        SELECT ... 
        FROM `Statistics_2022` a 
        WHERE (a.`createtime` >= '2022-01-01 00:00:00' AND a.`createtime` < '2023-01-14 00:00:00') 
    ) ftb 
) a 
GROUP BY a.`shareId` 
LIMIT 0,30

场景6：FromQuery 多个查询，最后映射查询

var query2 = fsql.Select<UnitLog, LoadPlan, Instruction>()
    .InnerJoin((a, b, c) => a.LoadNo == b.LoadNo && a.UnitTransactionType == "TO")
    .InnerJoin((a, b, c) => b.InstructionNo == c.InstructionNo)
    .WithTempQuery((a, b, c) => new
    {
        a.LoadNo,
        a.SeqNoLog,
        c.DeliveryInstractionStatus,
        c.UpTime,
        RN = SqlExt.RowNumber().Over().PartitionBy(a.UnitId).OrderByDescending(a.SeqNoLog).ToValue()
    });
var query3 = fsql.Select<Unit>();

fsql.Select<UnitLog>()
    .FromQuery(query2, query3)
    .InnerJoin((a,b,c) => a.SeqNoLog == b.SeqNoLog)
    .InnerJoin((a,b,c) => a.UnitId == c.UnitId)
    .Where((a,b,c) => b.RN < 2)
    .ToSql((a,b,c) => new MB51_View
    {
        //CkassIfCation = a.CkassIfCation,
        PGI = b.DeliveryInstractionStatus,
        PGITime = b.UpTime,
        IsDelayPGI = true,
        RunNo = c.RunNo
    });

SELECT a.[CkassIfCation] as1, b.[DeliveryInstractionStatus] as2, b.[UpTime] as3, 1 as4, c.[RunNo] as5 
FROM [UnitLog] a 
INNER JOIN (SELECT a.[LoadNo], a.[SeqNoLog], c.[DeliveryInstractionStatus], c.[UpTime], row_number() over( partition by a.[UnitId] order by a.[SeqNoLog] desc) [RN] 
    FROM [UnitLog] a 
    INNER JOIN [LoadPlan] b ON a.[LoadNo] = b.[LoadNo] AND a.[UnitTransactionType] = N'TO' 
    INNER JOIN [Instruction] c ON b.[InstructionNo] = c.[InstructionNo] ) b ON a.[SeqNoLog] = b.[SeqNoLog] 
INNER JOIN [Unit] c ON a.[UnitId] = c.[UnitId] 
WHERE (b.[RN] < 2)

场景7：报表（每日）

1. 从内存创建连续的日期 List
2. 使用 FromQuery 与多个 ISelect 横向 LeftJoin

var startDate = DateTime.Parse("2024-11-1");
var endDate = DateTime.Parse("2024-12-1");
fsql.Select<object>()
    .WithMemory(
        Enumerable.Range(0, (int)endDate.Subtract(startDate).TotalDays)
            .Select(a => new { Date = startDate.AddDays(a).ToString("yyyy-MM-dd") })
            .ToList()
    )
    .FromQuery(
        fsql.Select<T1, T2>().InnerJoin((a,b) => ...)
            .Where((a,b) => a.CreateDate.BetweenEnd(startDate, endDate)
            .GroupBy((a,b) => a.CreateDate.Date.ToString("yyyy-MM-dd"))
            .WithTempQuery(g => new { Date = g.Key, Type1Total = g.Sum(g.Value.Item2.Qty1) }),
        fsql.Select<T3>()
            .Where(a => a.CreateDate.BetweenEnd(startDate, endDate)
            .GroupBy(a => a.CreateDate.Date.ToString("yyyy-MM-dd"))
            .WithTempQuery(g => new { Date = g.Key, Type2Total = g.Sum(g.Value.Qty2) }),
        //... 最多支持 16 个 ISelect 合并
    )
    .LeftJoin(t => t.t2.Date = t.t1.Date)
    .LeftJoin(t => t.t3.Date = t.t1.Date)
    .OrderByDescending(t => t.t1.Date)
    .ToList(t => new
    {
        t.t1.Date,
        Sum1 = t.t2.Type1Total,
        Sum2 t.t3.Type2Total
    });

WithParameters 参数化共享

开启参数化查询功能后，使用 WithParameters 共享参数化，避免产生相同的参数名称：

var dbpars = new List<DbParameter>();

var id1 = 1;
var id2 = 2;
var sql = fsql.Select<User1>()
    .WithParameters(dbpars)
    .Where(a => a.Id == id1)

    .FromQuery(
        fsql.Select<User1>()
            .WithParameters(dbpars)
            .Where(a => a.Id == id2)
    )
    .InnerJoin((a,b) => a.Id == b.Id)
    .ToSql();

SELECT a."Id", a."GroupId", a."Username" 
FROM (
    SELECT a."Id", a."GroupId", a."Username" 
    FROM "User1" a 
    WHERE (a."Id" = @exp_0) 
) a
INNER JOIN ( 
    SELECT a."Id", a."GroupId", a."Username" 
    FROM "User1" a 
    WHERE (a."Id" = @exp_1) ) b ON b."Id" = a."Id"

---

子表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))

提示：由于子查询的实体类与上层相同，使用 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)))

子表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

子表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)
});

子表ToList

//最多执行3次 SQL
fsql.Select<Topic>().ToList(a => new
{
    all = a,
    list1 = fsql.Select<T2>().ToList(),
    list2 = fsql.Select<T2>().Where(b => b.TopicId == a.Id).ToList()
});

//分组之后，最多执行3次 SQL
fsql.Select<Topic>()
    .GroupBy(a => new { a.Author })
    .WithTempQuery(a => new { Author = a.Key.Author, Count = a.Count() })
    .ToList(a => new {
        a.Author, a.Count,
        list1 = fsql.Select<T2>().ToList(),
        list2 = fsql.Select<T2>().Where(b => b.Author == a.Author).ToList()
    });

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

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)

参考资料

• 《多表查询》
• 《返回数据》
• 《LinqToSql》
• 《仓储层Repository》
• 《优化之：延时加载》
• 《优化之：贪婪加载》
• 《Expression 表达式函数》
• 《性能》
• 《分表、分库》
• 《租户》

Basic

• 入门 Getting Started
• 安装 How to Install
• 添加 Insert Data
  • 单条 Single
  • 批量 Batch
• 删除 Delete Data
• 修改 Update Data
  • 更新实体 Entity
  • 更新指定列 Specified Columns
• 添加或修改 Insert or Update ✨
• 查询 Query Data
  • 分页 Pagination
  • 单表 Single Table
  • 多表 Multi Tables
  • 分组聚合 Group Aggregation
  • 嵌套查询 Nested Query ✨
  • 联合查询
  • 返回数据 Return Data ✨
  • 延时加载 Lazy Loading
  • 贪婪加载 Greed Loading ✨
  • LINQ 扩展 LinqToSql
  • 树型查询 Parent-Child Relp. ✨
• 仓储层 Repository Layer
  • 工作单元 Unit of Work
  • 联级保存 Cascade Saving
  • 联级删除 Cascade Deletion
  • 工作单元管理器 ✨
  • 聚合根（实验室）
• CodeFirst
  • 实体特性✨
  • FluentApi
  • 自定义特性
  • 类型映射
  • 导航属性 Entity Relationship ✨
  • 迁移结构
• DbFirst
• 表达式函数
  • 字符串
  • 日期/时间
  • 其他
  • 自定义函数
• 事务
• 过滤器
• ADO
• AOP✨
• 读写分离
• 分表分库
• 租户
• 性能
• 动态操作 Dynamic Operations
• 你不知道的功能✨
• API参考

使用指南

• Dapper比较
• EntityFramework比较

学习与交流

• 常见问题
• 提交Issues

信息

• 服务支持
• 更新日志