sqlserver实现层级树形查询

本sql已经经过试验，请放心使用（无毒）

WITH T AS  
(  
    SELECT *,CAST(dept_id AS VARBINARY(MAX)) AS px   
    FROM comm_dept AS A 
    WHERE NOT EXISTS(SELECT * FROM comm_dept WHERE dept_id=A.[parent_dept_id])  AND A.parent_dept_id=0
    UNION ALL   
    SELECT A.*,CAST(B.px+CAST(A.dept_id AS VARBINARY) AS VARBINARY(MAX))    
    FROM comm_dept AS A  
        JOIN T AS B  
           ON A.[parent_dept_id]=B.dept_id  
)  
SELECT * FROM T   
ORDER BY px  

注释：

1，comm_dept ---->数据表

2，dept_id------>表主键字段

3，parent_dept_id -------->父数据的主键字段

4，parent_dept_id=0------->新创建的数据，默认设置该字段为0