自动生成已有部门的编号-RowNumber+Partition

背景：

  原本数据库中已经存在 Unit （部门表），仅有一些常用字段（部门ID、部门名称、父部门ID、部门级别之类的）。现在需要两个新的字段，存储部门的编号，如父部门：001，子部门：001001，子子部门：001001001 …

问题分析：

（1）、部门编号以三个字符（001）为基础，迭代增加。需要已知部门序号基础上，自动凑齐三个字符，不足补0。（参照zhou同学）

-- New Function: PadLeft-- Purpose: if the length of original-string is less than the specified length, fill the specified character in the left.if Object_ID('FN_PadLeft', 'FN') is not nullDrop Function FN_PadLeftgoCreate Function FN_PadLeft(@original_str nvarchar(500), @total_len int, @char_tofilled nvarchar(10))Returns nvarchar(500)asBeginif @char_tofilled is null or Len(@char_tofilled) = 0Set @char_tofilled = '0'Declare @target_str  nvarchar(500)if Len(@original_str) >= @total_lenSet @target_str = @original_strelseSet @target_str =  Replace(Str(@original_str, @total_len), SPACE(1), @char_tofilled)return @target_strEndgo

（2）、如何取得部门序号？部门ID是标识列，不可取，自然想到要用Row_Number。二级部门还好说，关键就是三级部门。每个二级部门下的三级部门需要重置从1开始。

差点就要游标来处理了，幸好仔细看了下说明文档：[使用 PARTITIONBY 参数按指定列 对结果集进行分区]。肯定是以二级部门（ParentUTID）作为分区的标准，问题解决！

但是还需要注意的是，部门创建顺序可能不是完全依照二级->三级来创建的，所以需要在每一级部门编号更新之后，再来更新下一级部门的编号。子部门更新的方法相似，直接用循环操作即可：

-- set value for root unitupdate Unit set UTNO = '001', ParentUTNO = '0' where UTLevel = 1go-- set value for sub unitDeclare @maxLevel tinyint, @levelIndex tinyintSet @levelIndex = 2select @maxLevel = Max(UTLevel) from Unitwhile @levelIndex <= @maxLevelBeginupdate UTset UT.ParentUTNO = UTA.UTNO, UT.UTNO = UTA.UTNO + dbo.FN_PadLeft(UTB.RowNO,3, '0')from Unit as UTInner Join (select UTID, UTNO from Unit where UTLevel = @levelIndex - 1) as UTA-- number of parent uniton UT.ParentUTID = UTA.UTID  Inner Join (select UTID, ROW_NUMBER() over(Partition by ParentUTID  order by UTID) as RowNO from Unit where UTLevel = @levelIndex) as UTB-- sort the number of each sub unit  on UT.UTID = UTB.UTID where UT.UTLevel = @levelIndexSet @levelIndex = @levelIndex + 1Endgo

效果显示：