添加子信息mybatis查询语句

最新推荐文章于 2024-08-26 11:31:11 发布

原创最新推荐文章于 2024-08-26 11:31:11 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#spring #struts #mybatis #java ee

本文展示了如何使用SQL语句查询指定部门下所有员工的名字，并通过连接子查询来获取部门层级和类型信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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