联表分组

• 从联表分组查询获取数据组件
• 依赖sql联表分组或逻辑联表分组
• 本组件用来处理sql的SELECT语句
• 本组件是对ShadowSql.Core同名组件的扩展

1. 接口

• ISelect
• IGroupBySelect

2. 基类

• SelectFieldsBase

3. 类

GroupByMultiSelect

4. ToSelect

创建GroupByMultiSelect

4.1 ToSelect扩展方法

• 从sql联表分组获取
• 依赖sql联表分组
• 筛选的默认字段为当前分组字段

GroupByMultiSelect ToSelect(this GroupByMultiSqlQuery groupBy);

var select = _db.From("Employees")
    .SqlJoin(_db.From("Departments"))
    .OnColumn("DepartmentId", "Id")
    .Root
    .SqlGroupBy("Manager")
    .ToSelect();
// SELECT [Manager] FROM [Employees] AS t1 INNER JOIN [Departments] AS t2 ON t1.[DepartmentId]=t2.[Id] GROUP BY [Manager]

4.2 ToSelect重载扩展方法

• 从逻辑分组获取
• 依赖逻辑联表分组
• 筛选的默认字段为当前分组字段

GroupByMultiSelect ToSelect(this GroupByMultiQuery groupBy);

CommentAliasTable c = new("c");
PostAliasTable p = new("p");
var select = c.SqlJoin(p)
    .On(c.PostId, p.Id)
    .Root
    .SqlGroupBy(p.Id)
    .ToSelect();
// SELECT p.[Id] FROM [Comments] AS c INNER JOIN [Posts] AS p ON c.[PostId]=p.[Id] GROUP BY p.[Id]

5. SelectAggregate

5.1 SelectAggregate方法

• 聚合筛选
• 从别名表聚合

GroupByMultiSelect SelectAggregate<TAliasTable>(string tableName, Func<TAliasTable, IAggregateFieldAlias> select)
        where TAliasTable : IAliasTable;
GroupByMultiSelect SelectAggregate<TAliasTable>(string tableName, Func<TAliasTable, IEnumerable<IAggregateFieldAlias>> select)
        where TAliasTable : IAliasTable;

var select = new CommentAliasTable("c")
    .SqlJoin(new PostAliasTable("p"))
    .On(c => c.PostId, p => p.Id)
    .SqlGroupBy((c, p) => [c.PostId])
    .ToSelect()
    .SelectGroupBy()
    .SelectAggregate<CommentAliasTable>("c", c => c.Pick.SumAs("PickTotal"));
// SELECT c.[PostId],SUM(c.[Pick]) AS PickTotal FROM [Comments] AS c INNER JOIN [Posts] AS p ON c.[PostId]=p.[Id] GROUP BY c.[PostId]

5.2 SelectAggregate重载方法

• 聚合筛选
• 从表聚合,先定位再聚合

GroupByMultiQuery Apply<TTable>(string tableName, Func<TTable, IColumn> select, Func<IColumn, IAggregateField> aggregate, Func<Logic, IAggregateField, Logic> query)
        where TTable : ITable;

var select = new CommentTable()
    .SqlJoin(new PostTable())
    .On(c => c.PostId, p => p.Id)
    .SqlGroupBy((c, p) => [c.PostId])
    .ToSelect()
    .SelectGroupBy()
    .SelectAggregate<CommentTable>("Comments", c => c.Pick, Pick => Pick.SumAs("PickTotal"));
// SELECT t1.[PostId],SUM(t1.[Pick]) AS PickTotal FROM [Comments] AS t1 INNER JOIN [Posts] AS t2 ON t1.[PostId]=t2.[Id] GROUP BY t1.[PostId]

6. 其他相关功能

• 参看获取简介
• 参看sql联表分组
• 参看逻辑联表分组
• 参看聚合
• 参看ShadowSqlCore相关文档