数据集set nocount onEXEC 营运调度任务用时统计 '${开始1}','${结束1}','${开始2}','${结束2}','${ccb}'存储过程重点部分DECLARE @FEILEI1 VARCHAR(20) , @FEILEI2 VARCHAR(20) , @FEILEI3 VARCHAR(20) , @FEILEI4 VARCHAR(20), @STR1 VARCHAR(1024), @STR2 VARCHAR(1024), @DSTR VARCHAR(1024), @JOIN_STR VARCHAR(1024)SET @FEILEI1 = dbo.Func_字符分割_索引(@FENLEI,',',1)SET @FEILEI2 = dbo.Func_字符分割_索引(@FENLEI,',',2)SET @FEILEI3 = dbo.Func_字符分割_索引(@FENLEI,',',3)SET @FEILEI4 = dbo.Func_字符分割_索引(@FENLEI,',',4)--时段1分类,时段2分类,join 字符拼接SET @STR1 = ''SET @STR2 = ''SET @DSTR = ''SET @JOIN_STR = ''IF ISNULL(@FEILEI1,'') <> '' BEGIN SET @STR1 = @STR1 + @FEILEI1 + '1 ,' SET @STR2 = @STR2 + @FEILEI1 + '2 ,' SET @DSTR = @DSTR + @FEILEI1 + '1 ' + @FEILEI1 + ',' SET @JOIN_STR = @JOIN_STR + 'D1.' + @FEILEI1 + '1 = D2.' + @FEILEI1 + '2' END IF ISNULL(@FEILEI2,'') <> '' BEGIN SET @STR1 = @STR1 + @FEILEI2 + '1 ,' SET @STR2 = @STR2 + @FEILEI2 + '2 ,' SET @DSTR = @DSTR + @FEILEI2 + '1 ' + @FEILEI2 + ',' SET @JOIN_STR = @JOIN_STR + ' AND D1.' + @FEILEI2 + '1 = D2.' + @FEILEI2 + '2' END IF ISNULL(@FEILEI3,'') <> '' BEGIN SET @STR1 = @STR1 + @FEILEI3 + '1 ,' SET @STR2 = @STR2 + @FEILEI3 + '2 ,' SET @DSTR = @DSTR + @FEILEI3 + '1 ' + @FEILEI1 + ',' SET @JOIN_STR = @JOIN_STR + ' AND D1.' + @FEILEI3 + '1 = D2.' + @FEILEI3 + '2' ENDIF ISNULL(@FEILEI4,'') <> '' BEGIN SET @STR1 = @STR1 + @FEILEI4 + '1 ,' SET @STR2 = @STR2 + @FEILEI4 + '2 ,' SET @DSTR = @DSTR + @FEILEI4 + '1 ' + @FEILEI1 + ',' SET @JOIN_STR = @JOIN_STR + ' AND D1.' + @FEILEI4 + '1 = D2.' + @FEILEI4 + '2' ENDIF ISNULL(@JOIN_STR,'') = '' BEGIN SET @JOIN_STR = 'D2.司机资格证号2 = D1.司机资格证号1' ENDDECLARE @SQLSTR VARCHAR(1024) SET @SQLSTR = ' SELECT ' + @DSTR + ' D1.任务1 , D1.有单天数1,D1.运行时间1,D2.有单天数2,D2.运行时间2 FROM (SELECT ' + @STR1 +'任务1,count(DISTINCT 日期1) 有单天数1 ,sum(运行时间1) 运行时间1 FROM #TEMP_DETAIL1 GROUP BY '+ @STR1 +'任务1) D1 LEFT JOIN (SELECT ' + @STR2 +'任务2,count(DISTINCT 日期2) 有单天数2 ,sum(运行时间2) 运行时间2 FROM #TEMP_DETAIL2 GROUP BY '+ @STR2 +'任务2) D2 ON ' + @JOIN_STR + ' AND D1.任务1 = D2.任务2 'EXEC (@SQLSTR)数据库是SQL SERVER 在SQL SERVER 中运行是正常的