数据集 set nocount on EXEC 营运调度任务用时统计 '${开始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' END IF 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' END IF ISNULL(@JOIN_STR,'') = '' BEGIN SET @JOIN_STR = 'D2.司机资格证号2 = D1.司机资格证号1' END DECLARE @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 中运行是正常的 |