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，之前没有用过，不太习惯。
下班时间到了。
?
