父类组织机构中查询出所有子类下的用户:

WITH TEMPLEORZ(DeptID, ParentId,DeptName)
      AS
      (
      SELECT DeptID, ParentId,DeptName FROM dbo.tp_com_dept WHERE DeptID=3
      UNION ALL
      SELECT ORZ.DeptID,ORZ.ParentId,ORZ.DeptName
      FROM dbo.tp_com_dept AS ORZ,TEMPLEORZ AS TORZ WHERE ORZ.ParentId = TORZ.DeptID
      )
     select UserID
      ,UserName
      ,TrueName
      ,MobilePhone
      ,WorkPhone
      ,HomePhone
      ,Email
      ,QQ
      ,UserIcon, TEMPLEORZ.DeptName from tp_com_user
      join TEMPLEORZ on  tp_com_user.DeptID=TEMPLEORZ.DeptID
        where tp_com_user. DeptID in  (select DeptID from TEMPLEORZ)

posted on 2013-03-21 15:14  求知的我  阅读(218)  评论(0编辑  收藏  举报