CREATEFUNCTION dbo.GetTotalSalary ( @manager_id ASchar(5) ) RETURNSintAS BEGIN RETURN( SELECT[工资]FROM[员工信息]WHERE[员工号码]= @manager_id) + CASEWHENEXISTS(SELECT*FROM[员工信息]WHERE[上级号码]= @manager_id) THEN ( SELECTSUM(dbo.GetTotalSalary([员工号码])) FROM[员工信息] WHERE[上级号码]= @manager_id ) ELSE0 END END
--员工逍遥及其下属的工资总额 SELECT dbo.GetTotalSalary('E9906') AS 工资总额 3. 某棵子树的结点信息,如:员工郑可可及其所有下属员工的信息 a.求树的深度:
CREATEFUNCTION dbo.GetUnderlyingLevel ( @manager_id ASchar(5) ) RETURNSintAS BEGIN RETURN CASE WHENEXISTS(SELECT*FROM[员工信息]WHERE[上级号码]= @manager_id) THEN1+ (SELECTMAX(dbo.GetUnderlyingLevel([员工号码])) FROM[员工信息]WHERE[上级号码]= @manager_id) ELSE1 END END
如:SELECT dbo.GetUnderlyingLevel('E9901') AS '下属级别' ---返回:4
b 求某一个节点所有的子节点的信息。 由于返回的是一个结果集,所以需要用table数据类型来存储
CREATEFUNCTION dbo.GetSubtreeInfo ( @manager_id ASchar(5) ) RETURNS @treeinfo table ([员工号码][char] (5) NOTNULL, [姓名][char] (10) NOTNULL, [年龄][int]NOTNULL, [工资][money]NOTNULL, [上级号码][char] (5) NULL, [级别][int]NOTNULL ) AS BEGIN DECLARE @level ASint SELECT @level =0 INSERTINTO @treeinfo SELECT[员工号码], [姓名], [年龄], [工资], [上级号码], @level FROM[员工信息] WHERE[员工号码]= @manager_id WHILE @@ROWCOUNT >0 BEGIN SET @level = @level +1 INSERTINTO @treeinfo SELECT E.[员工号码], E.[姓名], E.[年龄], E.[工资], E.[上级号码], @level FROM[员工信息]AS E JOIN @treeinfo AS T ON E.[上级号码]= T.[员工号码]AND T.[级别]= @level -1 END RETURN END
--如:查询郑可可及其下属的信息 SELECT * FROM dbo.GetSubtreeInfo('E9903') --结果: c .对b进行改进,将该树型结构以图形化的方式打印出来。 由于打印要进行排序,所以加了一个标记字段。
CREATEFUNCTION dbo.GetSubtreeInfo2 ( @manager_id ASchar(5) ) RETURNS @treeinfo table ([员工号码][char] (5) NOTNULL, [姓名][char] (10) NOTNULL, [年龄][int]NOTNULL, [工资][money]NOTNULL, [上级号码][char] (5) NULL, [级别][int]NOTNULL, [标记][varchar] (200) NOTNULL ) AS BEGIN DECLARE @level ASint, @path ASvarchar(200) SELECT @level =0, @path ='NULL' INSERTINTO @treeinfo SELECT[员工号码], [姓名], [年龄], [工资], [上级号码], @level, 'NULL->'+[员工号码] FROM[员工信息] WHERE[员工号码]= @manager_id WHILE @@ROWCOUNT >0 BEGIN SET @level = @level +1 INSERTINTO @treeinfo SELECT E.[员工号码], E.[姓名], E.[年龄], E.[工资], E.[上级号码], @level, T.[标记]+'->'+ E.[员工号码] FROM[员工信息]AS E JOIN @treeinfo AS T ON E.[上级号码]= T.[员工号码]AND T.[级别]= @level -1 END RETURN
END
--如: SELECT REPLICATE ('| ', [级别]) + [姓名] AS 组织结构 FROM dbo.GetSubtreeInfo2('E9901') order by [标记] --结果: