1 1.1.1.1 1.1.2 1.3 2 2.1 ...这样的数据sql排序
----其中wbscode 为1 1.1.1 1.1.2 这样的编码。
主要关注的地方为:1 分解函数 2.根据分解函数进行行转列 3 最终合并数据去重
-----分解函数 CREATE FUNCTION dbo.Split( @String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000),splitLevel int ) as begin declare @idx int declare @slice varchar(8000) declare @index int set @index=0 select @idx=1 if len(@String)<1 or @String is null return while @idx!=0 begin set @idx=charindex(@Delimiter,@String) if @idx!=0 set @slice=left(@String,@idx-1) else set @slice=@String if(len(@slice)>0) set @index=@index+1; insert into @temptable(Items,splitLevel) values(@slice,@index) set @String=right(@String,len(@String) -@idx) if len(@String) =0 break end return end go ---更新执行版本的任务序号数据 update PMM_TaskPerformance set Sequence=c.se from PMM_TaskPerformance tp inner join ( ---进行排序获取排序数据 select ROW_NUMBER() over(order by level1,level2,level3,level4,level5,level6,level7,level8,level9,level10 ) as se, * from ( -----数据进行行转列 select performancetaskid,WBSCode,sum(level1)as level1,SUM(level2) as level2,sum(level3) as level3,sum(level4) as level4 ,sum(level5) as level5,sum(level6) as level6,sum(level7) as level7,sum(level8) as level8,sum(level9) as level9,sum(level10) as level10 from ( ---执行查询与编码分解 select items 'SalesOffice',splitLevel, case splitLevel when 1 then items else 0 end as level1, case splitLevel when 2 then items else 0 end as level2, case splitLevel when 3 then items else 0 end as level3, case splitLevel when 4 then items else 0 end as level4, case splitLevel when 5 then items else 0 end as level5, case splitLevel when 6 then items else 0 end as level6, case splitLevel when 7 then items else 0 end as level7, case splitLevel when 8 then items else 0 end as level8, case splitLevel when 9 then items else 0 end as level9, case splitLevel when 10 then items else 0 end as level10, --执行版本任务id pt.id as performancetaskid ,pt.WBSCode,pt.Level from PMM_TaskPerformance pt CROSS apply dbo.split(pt.WBSCode,'.') ----以下三行为筛选条件,可以不用关注 --join PMM_WBSTask t on t.ID=pt.WBSTask ---项目主键条件 ----where t.ProjectMaster='1002209270220008' )a group by performancetaskid,WBSCode )b )c on c.performancetaskid=tp.ID ---删除临时方法 drop FUNCTION dbo.Split;