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>

 

结果：