不为别的，只为炫耀-工作中写的sql

梦里梦外; 2024-02-18 20:03 26阅读 0赞

声明：本博客没有任何技术含量，只为炫耀。  
　　经过12个小时的长途跋涉，来到雪花啤酒工作，主要接的工作就是报表的制作。  
　　经过多天的了解，大概知道了这个CRM系统的基本框架。前端采用MVC，后端采用WebAPI，也算是半个前后端分离吧。  
　　因为需要经常写静态网页的报表，所以在这里的主要工作就是写sql语句。经过三天的奋斗，终于写好了第一个报表。下面来秀一秀，这可是我从原来经常操作3张以内的数据库表，瞬间剧增到6张数据库表的质变。请大家接下来慢慢膜拜吧。  
　　本sql语句是写在WebAPI中的Controller层的SQL拼接语句。因为在家写的博客，无法给大家展示界面，所以只能讲SQL语句展现给大家。

[Route("TAVDtlPageServ")]
            public TableModel<ViewBOTAVDetail> TAVDtlPageServ(TAVQueryModel queryModel)
            {
                int cnt = 0;
                var pageAll = new List<ViewBOTAVDetail>();
                var pageList = new List<ViewBOTAVDetail>();
                var sqlParams = new List<SqlParameter>();
                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<Count>(cntSql.ToString(), sqlParams.Select(x => ((ICloneable)x).Clone()).ToArray()).FirstOrDefault();
                        //cnt = cntObj.cnt;
                        //pageList = context.Database.SqlQuery<ViewBOTAVDetail>(dataSql.ToString(), sqlParams.Select(x => ((ICloneable)x).Clone()).ToArray()).ToList();
                        //方法二（去掉记录总数查询，可以节省一半查询时间，特别是翻页时更明显）
                        pageAll.Clear();
                        pageAll = context.Database.SqlQuery<ViewBOTAVDetail>(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<ViewBOTAVDetail>(pageList, cnt);
            }
            #endregion

此致，敬礼。(^\_−)☆