分组查询、去重,GroupBy以后出现 列名无效 返回

SqlSugar
11 190

image.png

image.png

image.png

热忱回答11

  • 把3个实体类发一下

    0 回复
  • @fate sta

     [SugarTable("laws_regulations", "法律法规表")]

        public class LawsRegulations : EntityBase

        {

            /// <summary>

            /// 分类 (0目录 1文件)

            /// </summary>

            public int type { get; set; }

            /// <summary>

            /// 父Id

            /// </summary>

            public long pid { get; set; }

            /// <summary>

            /// 名称

            /// </summary>

            public string title { get; set; }

            /// <summary>

            /// 状态(0失效 1正常)

            /// </summary>

            public int status { get; set; }

            /// <summary>

            /// 法规学习规则表Id

            /// </summary>

            public long LawsStudyRuleId { get; set; }

            /// <summary>

            /// 失效时间

            /// </summary>

            public DateTime? invalidTime { get; set; }

            /// <summary>

            /// (过期时间)失效时间

            /// </summary>

            public DateTime? expireTime { get; set; }

            /// <summary>

            /// 是否新法速递

            /// </summary>

            public Boolean IsNewLaw { get; set; }

            /// <summary>

            /// 是否系统设定,禁止修改

            /// </summary>

            public Boolean IsSystem { get; set; }

            /// <summary>

            /// 排序

            /// </summary>

            public int orderNo { get; set; }

            /// <summary>

            /// 备注

            /// </summary>

            public string? remark { get; set; }



            [Navigate(NavigateType.OneToMany, nameof(LawsRegulationsContent.lawsRegulationsId))]

            public List<LawsRegulationsContent> LawsRegulationsContentList { get; set; }


            [Navigate(NavigateType.OneToMany, nameof(LawsReadUser.LawsRegulationsId))]

            public List<LawsReadUser> LawsReadUserList { get; set; }


            [Navigate(NavigateType.OneToOne, nameof(LawsStudyRuleId))]

            public LawsStudyRule LawsStudyRule { get; set; }


            [Navigate(NavigateType.OneToMany, nameof(LawsStudyUser.LawsRegulationsId))]

            public List<LawsStudyUser> LawsStudyUserList { get; set; }

        }


     [SugarTable("laws_study_role", "角色学习法规需要时长表")]

        public class LawsStudyRole : EntityBaseId

        {

            /// <summary>

            /// 法规学习规则表Id

            /// </summary>

            public long LawsStudyRuleId { get; set; }

            /// <summary>

            /// 角色Id

            /// </summary>

            public long RoleId { get; set; }

            /// <summary>

            /// 学习所需时长(分钟)

            /// </summary>

            public int NeedStudyLength { get; set; }



            [Navigate(NavigateType.OneToMany, nameof(SysUserRole.RoleId), nameof(RoleId))]

            public List<SysUserRole> SysUserRoleList { get; set; }

        }



    [SugarTable(null, "系统用户角色表")]

    [SystemTable]

    public class SysUserRole : EntityBaseId

    {

        /// <summary>

        /// 用户Id

        /// </summary>

        [SugarColumn(ColumnDescription = "用户Id")]

        public long UserId { get; set; }


        /// <summary>

        /// 角色Id

        /// </summary>

        [SugarColumn(ColumnDescription = "角色Id")]

        public long RoleId { get; set; }


        /// <summary>

        /// 角色

        /// </summary>

        [SugarColumn(IsIgnore = true)]

        [Navigate(NavigateType.OneToOne, nameof(RoleId))]

        public SysRole SysRole { get; set; }

    }


    0 回复
  • 用最新版本简单写了个DEMO没有重现

                var xx=db.Queryable<ORdER>().Select(it => new { id1 = it.Id1 })
                    .MergeTable()
                    .GroupBy(it => it.id1)
                    .Select(it => new { id1 = it.id1 })
                    .ToList();

    生成的SQL

    SELECT  [id1] AS [id1]  FROM  (SELECT  [id] AS [id1]  FROM [ORdER] ) MergeTable  GROUP BY [id1]


    0 回复
  • c#代码也发一下

    0 回复
  • 发文本

    0 回复
  • @fate sta


     var NeedStudyUserCountList = _lawsRegulationsRep.AsQueryable()

                .InnerJoin<LawsStudyRole>((a, b) => a.LawsStudyRuleId == b.LawsStudyRuleId)

                .InnerJoin<SysUserRole>((a, b, c) => b.RoleId == c.RoleId)

                .Where((a, b, c) => a.status == SqlFunc.ToInt32(StatusEnum.Enable) && a.type == 1)

                .Select((a, b, c) => new

                {

                    rindex = SqlFunc.RowNumber($"{b.NeedStudyLength} desc ", $"{a.Id},{c.UserId}"),

                    Id = a.Id,

                    LawsStudyRuleId = a.LawsStudyRuleId,

                    UserId = c.UserId,

                    NeedStudyLength = b.NeedStudyLength

                })

                .MergeTable()

                .Where(u => u.rindex == 1)

                .GroupBy(u => u.LawsStudyRuleId)

                .Select(u => new { LawsStudyRuleId = u.LawsStudyRuleId, NeedStudyUserCount = SqlFunc.AggregateCount(u.UserId) })

                .ToList();


    0 回复
  • @fate sta


    image.png

    image.png




        这么写可以查出数据,我估计是因为MergeTable之前没有Select,字段没有别名,所以Groupby的字段还是数据库字段,可以识别


    0 回复
  •  可以重现了,匿名对象进了AOP 


         if (!p.EntityName.StartsWith("<>f__AnonymousType"))

                         {

                             if (!p.DbColumnName.Contains("_"))

                                 p.DbColumnName = UtilMethods.ToUnderLine(p.DbColumnName);//ToUnderLine驼峰转下划线方法

                         }



    0 回复
  • 后面版本AOP我将过滤匿名对象

    0 回复
  • 你先手动处理一下,加  if (!p.EntityName.StartsWith("<>f__AnonymousType"))

    0 回复
  • @fate sta:好的,非常感谢。新年快乐🌷🌷

    0 回复