sqlserver 2005 树形机关查询

select * from? DepartmentMaster

?

有如下列：FID, FNAME, FCODE, FHIGHERDEPARTMENT, FCHARGER, FSTANDARD, FCREATETIME, FUPDATTIME, FCREATER, FSTATUS

?

将部门以树形的菜单显示的SQL语句为：

?

WITH Example_Table AS
(??? --取根节点放入临时表
select *,rn=cast(fid as varchar(max)) from DepartmentMaster where FID in (SELECT DepartmentMaster.FID FROM DepartmentMaster WHERE FHIGHERDEPARTMENT =0)
UNION ALL
SELECT A.*,rn=rn+cast(A.FID as varchar(max)) FROM DepartmentMaster A INNER JOIN
Example_Table B ON A.FHIGHERDEPARTMENT = B.FID
)
SELECT * FROM Example_Table order by rn;

这样就可以了。。
?
sqlserver2005，之前没有用过，不太习惯。

下班时间到了。

?