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