一段经典的Group by 分页程序

最新推荐文章于 2024-10-24 22:19:38 发布

lizhixiong2

最新推荐文章于 2024-10-24 22:19:38 发布

阅读量487

点赞数

分类专栏： c# 数据库

本文链接：https://blog.youkuaiyun.com/lizhixiong2/article/details/82773178

版权

c# 同时被 2 个专栏收录

6 篇文章

订阅专栏

数据库

4 篇文章

订阅专栏

        private DataTable GetDataTable(ModalAshxParams mp, out int TotalCount, bool pager = true)
        {
            var QueryParams = new
            {
                RiQiStart = mp.Request("RiQiStart"),
                RiQiEnd = mp.Request("RiQiEnd"),
                ZhouQi = mp.Request("ZhouQi")
            };
            System.Text.StringBuilder filter = new System.Text.StringBuilder();
            if (QueryParams.RiQiStart != "")
            {
                if (filter.ToString() != "")
                {
                    filter.Append(" and ");
                }
                filter.Append("统计日期 >='" + QueryParams.RiQiStart + "'");
            }
            if (QueryParams.RiQiEnd != "")
            {
                if (filter.ToString() != "")
                {
                    filter.Append(" and ");
                }
                filter.Append("统计日期 <='" + QueryParams.RiQiEnd + "'");
            }
            string fields = "'' as 统计周期,统计日期,avg(总电话量) as 总电话量,avg(已注册声纹电话量) as 已注册声纹电话量,avg(总录音数量) as 总录音数量,avg(客户满意数量) as 客户满意数量,avg(客户不满录音数量) as 客户不满录音数量,avg(坐席态度模型判断非可疑录音数) as 坐席态度模型判断非可疑录音数,avg(坐席态度模型判断可疑录音数) as 坐席态度模型判断可疑录音数,avg(客户情绪指数) as 客户情绪指数,avg(坐席态度指数) as 坐席态度指数";
            string groupby = "统计日期";
            string orderby = "统计日期 desc";
            if (QueryParams.ZhouQi == "日")
            {
                groupby = "统计日期";
            }
            else if (QueryParams.ZhouQi == "周")
            {
                fields = fields.Replace("统计日期", "datepart(ww,统计日期) as 统计日期");
                fields += ",datepart(yy,统计日期) as 统计年份";
                groupby = "datepart(ww,统计日期),datepart(yy,统计日期)";
                orderby = "datepart(yy,统计日期) desc,datepart(ww,统计日期) desc";
            }
            else
            {
                fields = fields.Replace("统计日期", "datepart(mm,统计日期) as 统计日期");
                fields += ",datepart(yy,统计日期) as 统计年份";
                groupby = "datepart(mm,统计日期),datepart(yy,统计日期)";
                orderby = "datepart(yy,统计日期) desc,datepart(mm,统计日期) desc";
            }

            //BLL.instance.WriteLog_Info("satify", filter.ToString());
            DataTable dt;

            //dt = new Entity.tblStatisticsDateIndex().Select_GroupBy_OrderBy(fields, filter.ToString(), groupby, mp.OrderField + " " + mp.Sorting).Tables[0];
            //TotalCount = dt.Rows.Count;
            string ssql = SqlClass.Util.GetSqlPage(Entity.tblStatisticsDateIndex.TN_TableName, fields, filter.ToString(), groupby, "", orderby, mp.Pageindex, mp.Pagesize);
            BLL.instance.WriteLog_Info("GetSqlPage", ssql);
            dt = new Entity.tblStatisticsDateIndex().SelectPageOutTotal(Entity.tblStatisticsDateIndex.TN_TableName, fields, filter.ToString(), groupby, "", orderby, mp.Pageindex, mp.Pagesize, out TotalCount, "", null).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                if (QueryParams.ZhouQi == "日")
                {
                    dr["统计周期"] = Convert.ToDateTime(dr["统计日期"]).ToString("yyyy-MM-dd");
                }
                else if (QueryParams.ZhouQi == "周")
                {
                    dr["统计周期"] = dr["统计年份"].ToString() + "年" + "第" + dr["统计日期"].ToString() + "周";
                }
                else
                {
                    dr["统计周期"] = dr["统计年份"].ToString() + "年" + dr["统计日期"].ToString() + "月";
                }
            }
            return dt;
        }

当group by 为聚合函数时，order by 也应该为聚合函数，如orderby = "datepart(yy,统计日期) desc,datepart(mm,统计日期) desc"，否则会提示order by 的字段不在聚合函数中