添加子信息mybatis查询语句

来源：互联网发布：个性淘宝店铺女装店名编辑：程序博客网时间：2024/05/21 12:50

查询部门下的人的名字，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

0 0