查询部门下的人的名字,SSI下,其SQL语句为:
SELECT DISTINCT TD.*, wm_concat (TE.EMPLOYEE_NAME) AS DEP_EMPLOYEE
FROM ( SELECT TD.ID,
TD.PARENT_ID,
TD.DEPARTMENT_NAME,
TD.DEPARTMENT_ORDER || '级部门' AS DEPARTMENT_ORDER_NAME,
TD.DEPARTMENT_ORDER,
TD.CREATED_BY,
TD.DEPARTMENT_NUM,
TD.DEPARTMENT_LEVEL,
LPAD (' ', LEVEL * 4, ' ') || TD.DEPARTMENT_NAME
AS DEPARTMENT_NAME_LEVEL,
TD.DEPARTMENT_TYPE,
TD.REMARK,
NVL (
( SELECT COUNT (TE.DEPARTMENT_ID)
FROM TBL_EMPLOYEE TE,
STBL_PRI_USER_ROLE SPUR,
STBL_PRI_ROLE SPR
WHERE TE.IS_DELETED = 'N'
AND SPUR.IS_DELETED = 'N'
AND SPR.IS_DELETED = 'N'
AND TE.MGR_ID = SPUR.MGR_ID
AND SPUR.ROLE_ID = SPR.ROLE_ID
AND TD.ID = TE.DEPARTMENT_ID
GROUP BY TD.DEPARTMENT_NAME),
'0'
)
AS COUNT,
(SELECT NAME
FROM SYS_DATA
WHERE TYPE_CODE = 'DEPARTMENT_TYPE'
AND CODE = TD.DEPARTMENT_TYPE)
AS DEPARTMENT_TYPE_NAME,
TO_CHAR (TD.CREATED_DT, 'YYYY-MM-DD HH:MM') AS CREATED_DT
FROM TBL_DEPARTMENT TD
WHERE TD.IS_DELETED = 'N'
CONNECT BY TD.PARENT_ID = PRIOR TD.ID
START WITH TD.PARENT_ID = '0'
ORDER SIBLINGS BY DEPARTMENT_LEVEL) TD,
TBL_EMPLOYEE TE
WHERE TE.IS_DELETED = 'N' AND TD.ID = TE.DEPARTMENT_ID
GROUP BY TD.ID,
TD.PARENT_ID,
TD.DEPARTMENT_NAME,
TD.DEPARTMENT_ORDER_NAME,
TD.DEPARTMENT_ORDER,
TD.CREATED_BY,
TD.DEPARTMENT_NUM,
TD.DEPARTMENT_LEVEL,
DEPARTMENT_NAME_LEVEL,
TD.DEPARTMENT_TYPE,
TD.REMARK,
TD.COUNT,
TD.DEPARTMENT_TYPE_NAME,
TD.CREATED_DT