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，之前没有用过，不太习惯。

下班时间到了。