本文共 22071 字，大约阅读时间需要 73 分钟。
声明：本博客没有任何技术含量，只为炫耀。
经过12个小时的长途跋涉，来到雪花啤酒工作，主要接的工作就是报表的制作。 经过多天的了解，大概知道了这个CRM系统的基本框架。前端采用MVC，后端采用WebAPI，也算是半个前后端分离吧。 因为需要经常写静态网页的报表，所以在这里的主要工作就是写sql语句。经过三天的奋斗，终于写好了第一个报表。下面来秀一秀，这可是我从原来经常操作3张以内的数据库表，瞬间剧增到6张数据库表的质变。请大家接下来慢慢膜拜吧。 本sql语句是写在WebAPI中的Controller层的SQL拼接语句。因为在家写的博客，无法给大家展示界面，所以只能讲SQL语句展现给大家。[Route("TAVDtlPageServ")] public TableModel TAVDtlPageServ(TAVQueryModel queryModel) { int cnt = 0; var pageAll = new List (); var pageList = new List (); var sqlParams = new List (); StringBuilder dataSql = new StringBuilder();//数据sql语句 StringBuilder cntSql = new StringBuilder();//数量sql语句 StringBuilder conditionSql = new StringBuilder();//条件sql语句 #region 分页终端拜访查询-明细 if (queryModel.IsLastVist == 0)//终端拜访查询-明细 { dataSql.Append("select distinct t.VARYF_DES,t.SALES_ORG_DES,t.SALES_OFFICE_DES,t.SALES_GROUP_DES,act.ORDER_ID,"); dataSql.Append(" CONVERT(varchar(100),CONVERT(datetime,(left(concat(act.DATE_FROM,act.TIME_FROM),8) + ' ' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),9,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),11,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),13,2))),21) as DATETIME_FROM,"); dataSql.Append(" CONVERT(varchar(100),CONVERT(datetime,(left(concat(act.DATE_TO,act.TIME_TO),8) + ' ' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),9,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),11,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),13,2))),21) as DATETIME_TO,"); dataSql.Append(" datediff(Minute,CONVERT(datetime,(left(concat(act.DATE_FROM,act.TIME_FROM),8) + ' ' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),9,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),11,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),13,2))),"); dataSql.Append(" CONVERT(datetime,(left(concat(act.DATE_TO,act.TIME_TO),8) + ' ' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),9,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),11,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),13,2)))) AS DURATION,"); dataSql.Append(" p.PARTNER,CONCAT(p.MC_NAME1,p.MC_NAME2) SALES_DES,T.ZPOSITION, t.ZPOSITION_TXT, "); dataSql.Append(" case when act.PROCESS_TYPE ='ZB01' then '业务员拜访' when act.PROCESS_TYPE ='ZB03' then '督导拜访' end ORDER_TYPE,"); dataSql.Append(" t.TMN_ID,t.TMN_DES, case when o1.TMN_GUID is null then '无效拜访' else '有效拜访' end as ISVALIDVISIT"); dataSql.Append(" from BO_DW_AttActivityMd act "); dataSql.Append(" left join BO_EDB_but000_person p ");//2018-03-06修改表BO_EDB_but000为BO_EDB_but000_person dataSql.Append(" on act.VISITOR_GUID = p.PARTNER_GUID ");//BO_EDB_but000_person表无client字段，去掉 AND act.CLIENT = p.CLIENT dataSql.Append(" AND (p.bu_group = 'Z001' OR p.bu_group = 'Z005' ) "); dataSql.Append(" left join BO_DW_AttTmnMdV1 t "); //dataSql.Append(" ON t.TMN_GUID = act.TMN_GUID AND act.CLIENT = '800' "); dataSql.Append(" ON t.TMN_GUID = act.TMN_GUID "); dataSql.Append(" left join BO_DW_AttOrderV1 o1 on o1.ORDER_ID = act.ORDER_ID "); conditionSql.Append(" WHERE 1=1 "); conditionSql.Append(" AND ( "); if (!string.IsNullOrEmpty(queryModel.DATE_FROM)) { sqlParams.Add(new SqlParameter("@DATE_FROM",queryModel.DATE_FROM)); conditionSql.Append(" ( act.DATE_FROM >= @Date_From "); } if (!string.IsNullOrEmpty(queryModel.DATE_TO)) { sqlParams.Add(new SqlParameter("@DATE_TO",queryModel.DATE_TO)); conditionSql.Append(" AND act.DATE_TO <= @Date_To )"); } if (!string.IsNullOrEmpty(queryModel.DATE_FROM)) { conditionSql.Append(" OR ( act.DATE_FROM >= @Date_From "); } if (!string.IsNullOrEmpty(queryModel.DATE_TO)) { conditionSql.Append(" AND act.DATE_FROM <= @Date_To AND act.DATE_TO >= @DATE_TO) "); } conditionSql.Append(" ) "); //dataSql.Append(" AND t.CLIENT='800' "); if (!string.IsNullOrEmpty(queryModel.DATE_TO)) { conditionSql.Append(" AND t.CRDAT <= @Date_To "); } conditionSql.Append(" AND ( t.XDELE != 'X' "); if (!string.IsNullOrEmpty(queryModel.DATE_FROM)) { conditionSql.Append(" or ( t.XDELE = 'X' AND t.CHDAT >= @Date_From ) "); } conditionSql.Append(" ) "); if (!string.IsNullOrEmpty(queryModel.VARYF_DES))//区域公司 { conditionSql.Append(" AND t.VARYF_DES = @VARYF_DES"); sqlParams.Add(new SqlParameter("@VARYF_DES", queryModel.VARYF_DES)); } if (!string.IsNullOrEmpty(queryModel.SALES_ORG_DES))//营销中心 { conditionSql.Append(" AND t.SALES_ORG_DES = @SALES_ORG_DES"); sqlParams.Add(new SqlParameter("@SALES_ORG_DES", queryModel.SALES_ORG_DES)); } if (!string.IsNullOrEmpty(queryModel.SALES_OFFICE_DES))//大区 { conditionSql.Append(" AND t.SALES_OFFICE_DES = @SALES_OFFICE_DES "); sqlParams.Add(new SqlParameter("@SALES_OFFICE_DES", queryModel.SALES_OFFICE_DES)); } if (!string.IsNullOrEmpty(queryModel.SALES_GROUP_DES))//销售部/办事处 { conditionSql.Append(" AND t.SALES_GROUP_DES = @SALES_GROUP_DES "); sqlParams.Add(new SqlParameter("@SALES_GROUP_DES", queryModel.SALES_GROUP_DES)); } if (!string.IsNullOrEmpty(queryModel.SALESMAN))//业务员 { sqlParams.Add(new SqlParameter("@SALESMAN", queryModel.SALESMAN)); conditionSql.Append(" and t.SALESMAN = @SALESMAN "); } dataSql.Append(conditionSql); dataSql.Append(" order by t.VARYF_DES,t.SALES_ORG_DES,t.SALES_OFFICE_DES,t.SALES_GROUP_DES, DATETIME_FROM,DATETIME_TO,p.PARTNER,t.TMN_ID"); } else if (queryModel.IsLastVist == 1)//最近一次拜访查询 { dataSql.Append("select VARYF_DES,SALES_ORG_DES,SALES_OFFICE_DES,SALES_GROUP_DES,ORDER_ID,DATETIME_FROM,DATETIME_TO, "); dataSql.Append(" DURATION,PARTNER,SALES_DES,ZPOSITION,ZPOSITION_TXT,ORDER_TYPE,TMN_ID,TMN_DES,ISVALIDVISIT from("); dataSql.Append(" select distinct t.VARYF_DES,t.SALES_ORG_DES,t.SALES_OFFICE_DES,t.SALES_GROUP_DES,act.ORDER_ID,"); dataSql.Append(" CONVERT(varchar(100),CONVERT(datetime,(left(concat(act.DATE_FROM,act.TIME_FROM),8) + ' ' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),9,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),11,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),13,2))),21) as DATETIME_FROM,"); dataSql.Append(" CONVERT(varchar(100),CONVERT(datetime,(left(concat(act.DATE_TO,act.TIME_TO),8) + ' ' +"); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),9,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),11,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),13,2))),21) as DATETIME_TO,"); dataSql.Append(" datediff(Minute,CONVERT(datetime,(left(concat(act.DATE_FROM,act.TIME_FROM),8) + ' ' +"); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),9,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),11,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),13,2))),"); dataSql.Append(" CONVERT(datetime,(left(concat(act.DATE_TO,act.TIME_TO),8) + ' ' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),9,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),11,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),13,2)))) AS DURATION,"); dataSql.Append(" p.PARTNER,CONCAT(p.MC_NAME1,p.MC_NAME2) SALES_DES,T.ZPOSITION, t.ZPOSITION_TXT, "); dataSql.Append(" case when act.PROCESS_TYPE ='ZB01' then '业务员拜访' when act.PROCESS_TYPE ='ZB03' then '督导拜访' end ORDER_TYPE,"); dataSql.Append(" t.TMN_ID,t.TMN_DES, case when o1.TMN_GUID is null then '无效拜访' else '有效拜访' end as ISVALIDVISIT,"); dataSql.Append(" row_number() over(partition by t.VARYF_DES,t.SALES_ORG_DES,t.SALES_OFFICE_DES,t.SALES_GROUP_DES,p.PARTNER,t.TMN_ID"); dataSql.Append(" order by t.VARYF_DES,t.SALES_ORG_DES,t.SALES_OFFICE_DES,t.SALES_GROUP_DES,p.PARTNER,t.TMN_ID,"); dataSql.Append(" CONVERT(varchar(100),CONVERT(datetime,(left(concat(act.DATE_FROM,act.TIME_FROM),8) + ' ' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),9,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),11,2) + ':' + "); dataSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),13,2))),21) desc) as rownumber"); dataSql.Append(" from BO_DW_AttActivityMd act left join BO_EDB_but000_person p ");//2018-03-06修改表BO_EDB_but000为BO_EDB_but000_person dataSql.Append(" on act.VISITOR_GUID = p.PARTNER_GUID AND (p.bu_group = 'Z001' OR p.bu_group = 'Z005' ) ");//BO_EDB_but000_person表无client字段，去掉 AND act.CLIENT = p.CLIENT //dataSql.Append(" left join BO_DW_AttTmnMdV1 t ON t.TMN_GUID = act.TMN_GUID AND act.CLIENT = '800' "); dataSql.Append(" left join BO_DW_AttTmnMdV1 t ON t.TMN_GUID = act.TMN_GUID "); dataSql.Append(" left join BO_DW_AttOrderV1 o1 on o1.ORDER_ID = act.ORDER_ID"); conditionSql.Append(" WHERE 1=1 "); conditionSql.Append(" AND ( "); if (!string.IsNullOrEmpty(queryModel.DATE_FROM)) { sqlParams.Add(new SqlParameter("@Date_From", queryModel.DATE_FROM)); conditionSql.Append(" ( act.DATE_FROM >= @Date_From "); } if (!string.IsNullOrEmpty(queryModel.DATE_TO)) { sqlParams.Add(new SqlParameter("@Date_To", queryModel.DATE_TO)); conditionSql.Append(" AND act.DATE_TO <= @Date_To )"); } if (!string.IsNullOrEmpty(queryModel.DATE_FROM)) { conditionSql.Append(" OR ( act.DATE_FROM >= @Date_From "); } if (!string.IsNullOrEmpty(queryModel.DATE_TO)) { conditionSql.Append(" AND act.DATE_FROM <= @Date_To AND act.DATE_TO >= @DATE_TO) "); } conditionSql.Append(" ) "); //conditionSql.Append(" AND t.CLIENT='800' "); if (!string.IsNullOrEmpty(queryModel.DATE_TO)) { conditionSql.Append(" AND t.CRDAT <= @Date_To "); } conditionSql.Append(" AND ( t.XDELE != 'X' "); if (!string.IsNullOrEmpty(queryModel.DATE_FROM)) { conditionSql.Append(" or ( t.XDELE = 'X' AND t.CHDAT >= @Date_From ) "); } conditionSql.Append(" ) "); if (!string.IsNullOrEmpty(queryModel.VARYF_DES))//区域公司 { conditionSql.Append(" AND t.VARYF_DES = @VARYF_DES"); sqlParams.Add(new SqlParameter("@VARYF_DES", queryModel.VARYF_DES)); } if (!string.IsNullOrEmpty(queryModel.SALES_ORG_DES))//营销中心 { conditionSql.Append(" AND t.SALES_ORG_DES = @SALES_ORG_DES"); sqlParams.Add(new SqlParameter("@SALES_ORG_DES", queryModel.SALES_ORG_DES)); } if (!string.IsNullOrEmpty(queryModel.SALES_OFFICE_DES))//大区 { conditionSql.Append(" AND t.SALES_OFFICE_DES = @SALES_OFFICE_DES "); sqlParams.Add(new SqlParameter("@SALES_OFFICE_DES", queryModel.SALES_OFFICE_DES)); } if (!string.IsNullOrEmpty(queryModel.SALES_GROUP_DES))//销售部/办事处 { conditionSql.Append(" AND t.SALES_GROUP_DES = @SALES_GROUP_DES "); sqlParams.Add(new SqlParameter("@SALES_GROUP_DES", queryModel.SALES_GROUP_DES)); } if (!string.IsNullOrEmpty(queryModel.SALESMAN)) { sqlParams.Add(new SqlParameter("@SALESMAN", queryModel.SALESMAN)); conditionSql.Append(" and t.SALESMAN = @SALESMAN "); } dataSql.Append(conditionSql); dataSql.Append(" ) T "); dataSql.Append(" where T.rownumber='1' "); dataSql.Append(" order by TMN_ID "); } //dataSql.Append(" offset " + queryModel.offset + " rows fetch next " + queryModel.limit + " rows only "); #endregion #region 统计终端拜访查询-明细记录总数 cntSql.Append("select count(1) as cnt "); cntSql.Append(" from ( "); if (queryModel.IsLastVist == 0)//终端拜访明细查询 { cntSql.Append("select distinct t.VARYF_DES,t.SALES_ORG_DES,t.SALES_OFFICE_DES,t.SALES_GROUP_DES,act.ORDER_ID,"); cntSql.Append(" CONVERT(varchar(100),CONVERT(datetime,(left(concat(act.DATE_FROM,act.TIME_FROM),8) + ' ' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),9,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),11,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),13,2))),21) as DATETIME_FROM,"); cntSql.Append(" CONVERT(varchar(100),CONVERT(datetime,(left(concat(act.DATE_TO,act.TIME_TO),8) + ' ' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),9,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),11,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),13,2))),21) as DATETIME_TO,"); cntSql.Append(" datediff(Minute,CONVERT(datetime,(left(concat(act.DATE_FROM,act.TIME_FROM),8) + ' ' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),9,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),11,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),13,2))),"); cntSql.Append(" CONVERT(datetime,(left(concat(act.DATE_TO,act.TIME_TO),8) + ' ' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),9,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),11,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),13,2)))) AS DURATION,"); cntSql.Append(" p.PARTNER,CONCAT(p.MC_NAME1,p.MC_NAME2) SALES_DES,T.ZPOSITION, t.ZPOSITION_TXT, "); cntSql.Append(" case when act.PROCESS_TYPE ='ZB01' then '业务员拜访' when act.PROCESS_TYPE ='ZB03' then '督导拜访' end ORDER_TYPE,"); cntSql.Append(" t.TMN_ID,t.TMN_DES, case when o1.TMN_GUID is null then '无效拜访' else '有效拜访' end as ISVALIDVISIT"); cntSql.Append(" from BO_DW_AttActivityMd act "); cntSql.Append(" left join BO_EDB_but000_person p ");//2018-03-06修改表BO_EDB_but000为BO_EDB_but000_person cntSql.Append(" on act.VISITOR_GUID = p.PARTNER_GUID ");//BO_EDB_but000_person表无client字段，去掉 AND act.CLIENT = p.CLIENT cntSql.Append(" AND (p.bu_group = 'Z001' OR p.bu_group = 'Z005' ) "); cntSql.Append(" left join BO_DW_AttTmnMdV1 t "); //cntSql.Append(" ON t.TMN_GUID = act.TMN_GUID AND act.CLIENT = '800' "); cntSql.Append(" ON t.TMN_GUID = act.TMN_GUID "); cntSql.Append(" left join BO_DW_AttOrderV1 o1 on o1.ORDER_ID = act.ORDER_ID "); cntSql.Append(conditionSql); } else if (queryModel.IsLastVist == 1)//最近一次拜访查询 { cntSql.Append("select VARYF_DES,SALES_ORG_DES,SALES_OFFICE_DES,SALES_GROUP_DES,ORDER_ID,DATETIME_FROM,DATETIME_TO, "); cntSql.Append(" DURATION,PARTNER,SALES_DES,ZPOSITION,ZPOSITION_TXT,ORDER_TYPE,TMN_ID,TMN_DES,ISVALIDVISIT from("); cntSql.Append(" select distinct t.VARYF_DES,t.SALES_ORG_DES,t.SALES_OFFICE_DES,t.SALES_GROUP_DES,act.ORDER_ID,"); cntSql.Append(" CONVERT(varchar(100),CONVERT(datetime,(left(concat(act.DATE_FROM,act.TIME_FROM),8) + ' ' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),9,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),11,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),13,2))),21) as DATETIME_FROM,"); cntSql.Append(" CONVERT(varchar(100),CONVERT(datetime,(left(concat(act.DATE_TO,act.TIME_TO),8) + ' ' +"); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),9,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),11,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),13,2))),21) as DATETIME_TO,"); cntSql.Append(" datediff(Minute,CONVERT(datetime,(left(concat(act.DATE_FROM,act.TIME_FROM),8) + ' ' +"); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),9,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),11,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),13,2))),"); cntSql.Append(" CONVERT(datetime,(left(concat(act.DATE_TO,act.TIME_TO),8) + ' ' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),9,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),11,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_TO,act.TIME_TO),13,2)))) AS DURATION,"); cntSql.Append(" p.PARTNER,CONCAT(p.MC_NAME1,p.MC_NAME2) SALES_DES,T.ZPOSITION, t.ZPOSITION_TXT, "); cntSql.Append(" case when act.PROCESS_TYPE ='ZB01' then '业务员拜访' when act.PROCESS_TYPE ='ZB03' then '督导拜访' end ORDER_TYPE,"); cntSql.Append(" t.TMN_ID,t.TMN_DES, case when o1.TMN_GUID is null then '无效拜访' else '有效拜访' end as ISVALIDVISIT,"); cntSql.Append(" row_number() over(partition by t.VARYF_DES,t.SALES_ORG_DES,t.SALES_OFFICE_DES,t.SALES_GROUP_DES,p.PARTNER,t.TMN_ID"); cntSql.Append(" order by t.VARYF_DES,t.SALES_ORG_DES,t.SALES_OFFICE_DES,t.SALES_GROUP_DES,p.PARTNER,t.TMN_ID,"); cntSql.Append(" CONVERT(varchar(100),CONVERT(datetime,(left(concat(act.DATE_FROM,act.TIME_FROM),8) + ' ' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),9,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),11,2) + ':' + "); cntSql.Append(" SUBSTRING(concat(act.DATE_FROM,act.TIME_FROM),13,2))),21) desc) as rownumber"); cntSql.Append(" from BO_DW_AttActivityMd act left join BO_EDB_but000_person p ");//2018-03-06将BO_EDB_but000表改为BO_EDB_but000_person cntSql.Append(" on act.VISITOR_GUID = p.PARTNER_GUID AND (p.bu_group = 'Z001' OR p.bu_group = 'Z005' ) ");//BO_EDB_but000_person表无client字段，去掉 AND act.CLIENT = p.CLIENT cntSql.Append(" left join BO_DW_AttTmnMdV1 t ON t.TMN_GUID = act.TMN_GUID "); cntSql.Append(" left join BO_DW_AttOrderV1 o1 on o1.ORDER_ID = act.ORDER_ID"); cntSql.Append(conditionSql); cntSql.Append(" ) T "); cntSql.Append(" where T.rownumber='1' "); } cntSql.Append(" ) TT"); #endregion try { using (var context = new Entities()) { context.Database.CommandTimeout = 1800; //方法一 //var cntObj = context.Database.SqlQuery (cntSql.ToString(), sqlParams.Select(x => ((ICloneable)x).Clone()).ToArray()).FirstOrDefault(); //cnt = cntObj.cnt; //pageList = context.Database.SqlQuery (dataSql.ToString(), sqlParams.Select(x => ((ICloneable)x).Clone()).ToArray()).ToList(); //方法二（去掉记录总数查询，可以节省一半查询时间，特别是翻页时更明显） pageAll.Clear(); pageAll = context.Database.SqlQuery (dataSql.ToString(), sqlParams.Select(x => ((ICloneable)x).Clone()).ToArray()).ToList(); cnt = pageAll.Count(); pageList = pageAll.Skip(queryModel.offset).Take(queryModel.limit).ToList(); } } catch (Exception e) { Console.Write(e.Data); } return new TableModel (pageList, cnt); } #endregion 此致，敬礼。(^_−)☆
转载地址：http://kvhbb.baihongyu.com/