sqlserver 递归查询

 WITH  read_tree (id,departmentmark,supdepid)
          AS ( SELECT   id ,
                        departmentmark ,
                        supdepid
               FROM     hrmdepartment
               WHERE    supdepid = 10
               UNION ALL
               SELECT   p.id,
      p.departmentmark ,
                        p.supdepid
               FROM  hrmdepartment p
                        INNER JOIN read_tree A ON A.supdepid = p.id
             )
    SELECT  sr.id AS superid ,
            et.id AS id
    FROM    read_tree sr
            INNER JOIN hrmdepartment et ON sr.id = et.supdepid group by sr.id ,et.id