首页 > SqlSugar

嵌套查询使用group后问题返回

SqlSugar 沟通中

 3  459

冬天 发布于2024/6/11

悬赏：0 飞吻

var query = Context.Queryable<KpiExamineDetail>()

.InnerJoin<KpiExamineMain>((it, main) => it.MasterId == main.Id)

.InnerJoin<KpiOperateCompany>((it, main, company) => it.Company_Uuid == company.Uuid)

.InnerJoin<KpiCompanyOneIndexSet>((it, main, company, one) => it.OneKpiId == one.Id)

.InnerJoin<KpiCompanyTwoIndexSet>((it, main, company, one, two) => it.TwoKpiId == two.Id)

.LeftJoin<SysOrg>((it, main, company, one, two, org) => two.KpiOrgId == org.Id)

.GroupBy((it, main, company, one, two, org) => new { company.Uuid, company.Simple_Name, company.List_Order, main.KpiName, one.Id, one.IndexKpiName, one.ScoreWeight })

.OrderBy((it, main, company, one, two, org) => company.List_Order)

.OrderBy((it, main, company, one, two, org) => company.Simple_Name)

.OrderBy((it, main, company, one, two, org) => one.SortCode)

.Select((it, main, company, one, two, org) => new KpiExamineDetail

{

KpiName = main.KpiName,

Company_Uuid = company.Uuid,

Simple_Name = company.Simple_Name,

OneKpiId = one.Id,

IndexKpiName=one.IndexKpiName,

ScoreWeight = one.ScoreWeight,

KpiScore = SqlFunc.AggregateSum(it.KpiScore),

Score = SqlFunc.AggregateSum(it.Score),

RealScore = SqlFunc.AggregateSum(it.Score * one.ScoreWeight / 100),

});

var list = Context.Queryable(query).GroupBy(it => new { it.Company_Uuid, it.Simple_Name })

.Select(it => new KpiExamineDetail

{

Company_Uuid = it.Company_Uuid,

Simple_Name = it.Simple_Name,

RealScore = SqlFunc.AggregateSum(it.RealScore),

}).ToList();

生成的语句如下：

SELECT "company_uuid" AS "Company_Uuid" , "Simple_Name" AS "Simple_Name" , SUM("RealScore") AS "RealScore" FROM (SELECT * FROM (SELECT "main"."KpiName" AS "KpiName" , "company"."uuid" AS "Company_Uuid" , "company"."simple_name" AS "Simple_Name" , "one"."Id" AS "OneKpiId" , "one"."IndexKpiName" AS "IndexKpiName" , "one"."ScoreWeight" AS "ScoreWeight" , SUM("it"."KpiScore") AS "KpiScore" , SUM("it"."Score") AS "Score" , SUM((( "it"."Score" * "one"."ScoreWeight" ) / 100 )) AS "RealScore" FROM "kpi_examine_detail" "it" Inner JOIN "kpi_examine_main" "main" ON ( "it"."MasterId" = "main"."Id" ) Inner JOIN "kpi_operate_company" "company" ON ( "it"."company_uuid" = "company"."uuid" ) Inner JOIN "kpi_company_one_index_set" "one" ON ( "it"."OneKpiId" = "one"."Id" ) Inner JOIN "kpi_company_two_index_set" "two" ON ( "it"."TwoKpiId" = "two"."Id" ) Left JOIN "sys_org" "org" ON ( "two"."KpiOrgId" = "org"."Id" ) WHERE ( "it"."MasterId" = N'548860691107973' )GROUP BY "company"."uuid","company"."simple_name","company"."list_order","main"."KpiName","one"."Id","one"."IndexKpiName","one"."ScoreWeight" ORDER BY "company"."list_order" ASC,"company"."simple_name" ASC,"one"."SortCode" ASC ) MergeTable ) t GROUP BY "company_uuid","Simple_Name"

pgsql中，上述语句出现错误了，具体错误为

将红框中的字段改成首字母大写即可。如果不嵌套，直接query.Tolist()则正常

热忱回答（3）

冬天 VIP0
2024/6/11

/// <summary>
/// 公司KPI考评打分总分明细表
///</summary>
[SugarTable("kpi_examine_detail", TableDescription = "公司KPI考评打分总分明细表")]
[Tenant(SqlsugarConst.DB_Default)]
//普通索引
[SugarIndex("Pk_kpi_examine_detail_Id", nameof(Id), OrderByType.Asc)]
[SugarIndex("Pk_kpi_examine_detail_MasterId", nameof(MasterId), OrderByType.Desc)]
[SugarIndex("Pk_kpi_examine_detail_Company_Uuid", nameof(Company_Uuid), OrderByType.Desc)]
[SugarIndex("Pk_kpi_examine_detail_OneKpiId", nameof(OneKpiId), OrderByType.Desc)]
[SugarIndex("Pk_kpi_examine_detail_TwoKpiId", nameof(TwoKpiId), OrderByType.Desc)]
public class KpiExamineDetail
{
/// <summary>
/// 唯一Id
/// </summary>
[SugarColumn(ColumnName = "Id", ColumnDescription = "唯一Id(非主键)", Length = 50)]
public string Id { get; set; }
/// <summary>
/// 主表Id
///</summary>
[SugarColumn(ColumnName = "MasterId", Length = 50, ColumnDescription = "主表Id", IsPrimaryKey = true)]
public string MasterId { get; set; }
/// <summary>
/// (查询映射)考核表名称
///</summary>
[SugarColumn(IsIgnore = true)]
public string KpiName { get; set; }
/// <summary>
/// 企业uuid
/// </summary>
/// <returns></returns>
[SugarColumn(ColumnName = "company_uuid", ColumnDescription = "企业uuid", Length = 50, IsPrimaryKey = true)]
public string Company_Uuid { get; set; }
/// <summary>
/// (查询映射)企业简称
/// </summary>
/// <returns></returns>
[SugarColumn(IsIgnore = true)]
public string Simple_Name { get; set; }
/// <summary>
/// 一级指标Id
///</summary>
[SugarColumn(ColumnName = "OneKpiId", ColumnDescription = "一级指标Id", Length = 50, IsPrimaryKey = true)]
public string OneKpiId { get; set; }
/// <summary>
/// (查询映射)一级指标名称
///</summary>
[SugarColumn(IsIgnore = true)]
public string IndexKpiName { get; set; }
/// <summary>
/// (查询映射)分值权重
///</summary>
[SugarColumn(IsIgnore = true)]
public int? ScoreWeight { get; set; }
/// <summary>
/// 二级指标Id(考核部门Id)
///</summary>
[SugarColumn(ColumnName = "TwoKpiId", ColumnDescription = "二级指标Id(考核部门Id)", Length = 50, IsPrimaryKey = true)]
public string TwoKpiId { get; set; }
/// <summary>
/// (查询映射)考核部门Id
///</summary>
[SugarColumn(IsIgnore = true)]
public string KpiOrgId { get; set; }
/// <summary>
/// (查询映射)考核部门名称
///</summary>
[SugarColumn(IsIgnore = true)]
public string KpiOrgName { get; set; }
/// <summary>
/// 考核分值
///</summary>
[SugarColumn(ColumnName = "KpiScore", ColumnDescription = "考核分值", IsNullable = false)]
public decimal? KpiScore { get; set; } = 0;
/// <summary>
/// 考核目标和任务说明
/// </summary>
[SugarColumn(ColumnName = "KpiPlanDescription", ColumnDescription = "考核目标和任务", ColumnDataType = StaticConfig.CodeFirst_BigString, IsNullable = true)]
public string KpiPlanDescription { get; set; }
/// <summary>
/// 考核细则说明
/// </summary>
[SugarColumn(ColumnName = "KpiDescription", ColumnDescription = "考核细则说明", ColumnDataType = StaticConfig.CodeFirst_BigString, IsNullable = true)]
public string KpiDescription { get; set; }
/// <summary>
/// 排序号
///</summary>
[SugarColumn(ColumnName = "SortCode", ColumnDescription = "排序号", IsNullable = false)]
public int? SortCode { get; set; } = 1;
/// <summary>
/// 是否可见
/// </summary>
[SugarColumn(ColumnName = "Visible", ColumnDescription = "是否可见", IsNullable = true)]
public bool Visible { get; set; } = true;
/// <summary>
/// 打分分值
///</summary>
[SugarColumn(ColumnName = "Score", ColumnDescription = "打分分值", IsNullable = true)]
public decimal? Score { get; set; } = 0;
/// <summary>
/// 权重分值
///</summary>
[SugarColumn(IsIgnore = true)]
public decimal? RealScore { get; set; } = 0;
/// <summary>
/// 打分说明
/// </summary>
[SugarColumn(ColumnName = "ScoreDescription", ColumnDescription = "打分说明", ColumnDataType = StaticConfig.CodeFirst_BigString, IsNullable = true)]
public string ScoreDescription { get; set; }
/// <summary>
/// 是否已录入
/// </summary>
[SugarColumn(ColumnName = "IsEnter", ColumnDescription = "是否已录入", IsNullable = true)]
public bool IsEnter { get; set; } = false;
/// <summary>
/// 是否已确认
/// </summary>
[SugarColumn(ColumnName = "IsOk", ColumnDescription = "是否已确认", IsNullable = true)]
public bool IsOk { get; set; } = false;
/// <summary>
/// 确认时间
/// </summary>
[SugarColumn(ColumnDescription = "确认时间", IsNullable = true)]
public DateTime? OkTime { get; set; }
/// <summary>
/// 软删除
/// </summary>
[SugarColumn(ColumnName = "IsDelete", ColumnDescription = "软删除", IsNullable = true)]
public bool IsDelete { get; set; } = false;
/// <summary>
/// 创建时间
/// </summary>
[SugarColumn(ColumnDescription = "创建时间", IsNullable = true)]
public DateTime? CreateTime { get; set; }
/// <summary>
/// 更新时间
/// </summary>
[SugarColumn(ColumnDescription = "更新时间", IsNullable = true)]
public DateTime? UpdateTime { get; set; }
/// <summary>
/// 创建者Id
/// </summary>
[SugarColumn(ColumnDescription = "创建者Id", Length = 36, IsNullable = true)]
public string CreateUserId { get; set; }
/// <summary>
/// 修改者Id
/// </summary>
[SugarColumn(ColumnDescription = "修改者Id", Length = 36, IsNullable = true)]
public string UpdateUserId { get; set; }
/// <summary>
/// (查询映射)创建人
/// </summary>
[SugarColumn(IsIgnore = true)]
public string CreateUser { get; set; }
/// <summary>
/// (查询映射)更新人
/// </summary>
[SugarColumn(IsIgnore = true)]
public string UpdateUser { get; set; }
/// <summary>
/// 备注
/// </summary>
[SugarColumn(ColumnName = "Description", ColumnDescription = "备注", ColumnDataType = StaticConfig.CodeFirst_BigString, IsNullable = true)]
public string Description { get; set; }

}

0 回复

fate sta VIP0

2024/6/11

var query = Context.Queryable<KpiExamineDetail>()
    .InnerJoin<KpiExamineMain>((it, main) => it.MasterId == main.Id)
    .InnerJoin<KpiOperateCompany>((it, main, company) => it.Company_Uuid == company.Uuid)
    .InnerJoin<KpiCompanyOneIndexSet>((it, main, company, one) => it.OneKpiId == one.Id)
    .InnerJoin<KpiCompanyTwoIndexSet>((it, main, company, one, two) => it.TwoKpiId == two.Id)
    .LeftJoin<SysOrg>((it, main, company, one, two, org) => two.KpiOrgId == org.Id)
    .GroupBy((it, main, company, one, two, org) => new { company.Uuid, company.Simple_Name, company.List_Order, main.KpiName, one.Id, one.IndexKpiName, one.ScoreWeight })
    .OrderBy((it, main, company, one, two, org) => company.List_Order)
    .OrderBy((it, main, company, one, two, org) => company.Simple_Name)
    .OrderBy((it, main, company, one, two, org) => one.SortCode)
    .Select((it, main, company, one, two, org) => new//用匿名对象 
    {
        KpiName = main.KpiName,
        Company_Uuid = company.Uuid,
        Simple_Name = company.Simple_Name,
        OneKpiId = one.Id,
        IndexKpiName=one.IndexKpiName,
        ScoreWeight = one.ScoreWeight,
        KpiScore = SqlFunc.AggregateSum(it.KpiScore),
        Score = SqlFunc.AggregateSum(it.Score),
        RealScore = SqlFunc.AggregateSum(it.Score * one.ScoreWeight / 100),
    }) 
    .MergeTable().GroupBy(it => new { it.Company_Uuid, it.Simple_Name })
    .Select(it => new KpiExamineDetail
    {
        Company_Uuid = it.Company_Uuid,
        Simple_Name = it.Simple_Name,
        RealScore = SqlFunc.AggregateSum(it.RealScore),
    }).ToList();

0 回复

冬天 VIP0
2024/6/12

收到，谢谢，已按照上面的方法解决

0 回复

首页 > SqlSugar

嵌套查询使用group后问题 返回

热忱回答（3）

嵌套查询使用group后问题返回