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

SqlSugar 沟通中
3 95
该叫什么 冬天 发布于5天前
悬赏: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中,上述语句出现错误了,具体错误为

image.png


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

热忱回答3

  • /// <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 回复
  • 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 回复
  • 收到,谢谢,已按照上面的方法解决

    0 回复