sql server 遍历表成一棵树结构

一棵树的层次结构都在一张表内，当有这样的需要的时候。。

可以这样玩：

<!-- DepartmentDTO 对象对应 department表_查询sql -->

 <sql id="department_select_sql">

  with ldepartment as (

    select

     dept_id,

     parent_id,

     0 as dept_level,

     row_number()over(order by getdate()) as orderid

    from

     department

    where

     (

      parent_id is null

      or parent_id = ''

     )

    union all

     select

      a.dept_id,

      a.parent_id,

      b.dept_level + 1 as dept_level,

      b.orderid*100+row_number()over(order by getdate()) as orderid

     from

      department a,

      ldepartment b

     where

      a.parent_id = b.dept_id

   ) select

   t1.dept_level,

   t1.orderid,

   t2.paic_unique_deptid,

   t2.deptid_descr,

   t2.parent_id,

   (select aa.deptid_descr + '('+aa.dept_id+')' from department aa where aa.dept_id=t2.parent_id ) as parent_id_desc,

   t2.dept_id,

   t2.ou_type,

   t2.date_created,

   t2.created_by,

   t2.date_updated,

   t2.updated_by,

   t2.row_id

  from ldepartment t1, department t2

  where t1.dept_id = t2.dept_id

  <isNotEmpty prepend="and" property="deptid_descr">

   t2.deptid_descr like '%+#deptid_descr#+%'

  </isNotEmpty>

  <isNotEmpty prepend="and" property="parent_id">

   t2.parent_id = #parent_id#

  </isNotEmpty>

order by ltrim(t1.orderid) // 关键点（字符串排序）

 </sql>

调用：

<select id="department_find" parameterClass="java.util.Map"

        resultClass="com.pasc.supms.parameter.dto.DepartmentDTO">

        <include refid="department_select_sql" />

    </select>

java对象：

public class DepartmentDTO extends SupmsBaseDTO {

    private String paic_unique_deptid; // 部门唯一编号

    private String deptid_descr; // 部门名称

    private String parent_id; // 上级部门编号

    private String parent_id_desc; // 上级部门名称编号

    private String dept_id; // 部门属主编号

    private String ou_type; // 部门类型

    private String dept_level; // 部门层级

jsp页面：

<table cellpadding="0" cellspacing="0" class="table_list_2" >

            <thead align="center">

                <tr>

                    <th >部门编号</th>

                    <th >部门名称</th>

                    <th >上级部门编号</th>

                    <th >部门类型</th>

                </tr>

            </thead>

            <tbody align="center">

                <c:choose>

                    <c:when test="${not empty  pageBean.resultList}">

                            <c:forEach var="doc" items="${pageBean.resultList}" varStatus="i">

                                <tr>

                                    <td><!-- <a href="#" onclick="detailDo('${doc.row_id}');" title="点击查看详情" class="blue"></a> -->

                                    <c:forEach begin="1" end="${doc.dept_level }">--&nbsp;</c:forEach>

                                    ${doc.dept_id }</td>

                                    <td>${doc.deptid_descr }</td>

                                    <td>${doc.parent_id_desc }</td>

                                    <td>${doc.ou_type }</td>

                                </tr>

                                </c:forEach>

                        </c:when>

                        <c:otherwise>

                            <tr id="noList">

                                <td colspan="11" align="center">对不起，暂时还没有记录！</td>

                            </tr>

                        </c:otherwise>

                </c:choose>

            </tbody>

        </table>

结果：