维保专业、组织机构、线别、用户等数据联查

最新推荐文章于 2022-03-21 17:47:16 发布

代码我只会COPY

最新推荐文章于 2022-03-21 17:47:16 发布

阅读量189

点赞数

分类专栏： Oracle 文章标签： Oracle 设备维保 SQL

本文链接：https://blog.csdn.net/qq_38839739/article/details/84957680

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

--根据用户工号或者组织机构查询 专业、线别、用户组、用户、组织机构 --新版

SELECT * FROM (SELECT
                 T_WW_USERORG.USER_CODE,
                 T_WW_USER.USER_NAME,
                 T_WW_USERORG.ORG_CODE,
                 T_WW_ORG.ORG_NAME,
                 TDMBM06.LINE_CODE,
                 TDMBM05.MAJOR_CODE,
                 TDMBM01.NODE_NAME,
                 U3.GROUP_ENAME,
                 U3.GROUP_CNAME
               FROM RPLAT60.T_WW_USERORG
                 LEFT JOIN RPLAT60.T_WW_USER ON T_WW_USERORG.USER_CODE = T_WW_USER.USER_CODE
                 LEFT JOIN RPLAT60.T_WW_ORG ON T_WW_USERORG.ORG_CODE = T_WW_ORG.ORG_CODE
                 LEFT JOIN WBPLAT.TDMBM06 ON T_WW_USERORG.ORG_CODE = TDMBM06.ORG_CODE
                 LEFT JOIN WBPLAT.TDMBM05 ON T_WW_USERORG.ORG_CODE = TDMBM05.ORG_CODE
                 LEFT JOIN WBPLAT.TDMBM01 ON TDMBM05.MAJOR_CODE = TDMBM01.NODE_CODE
                 LEFT JOIN IPLAT60.XS_USER U1 ON T_WW_USERORG.USER_CODE = U1.LOGIN_NAME
                 LEFT JOIN IPLAT60.XS_USER_GROUP_MEMBER U2 ON U1.USER_ID = U2.MEMBER_ID
                 LEFT JOIN IPLAT60.XS_USER_GROUP U3 ON U2.PARENT_ID = U3.ID
)
WHERE MAJOR_CODE = '11';



--根据用户工号或者组织机构查询  专业、线别、用户组、用户、组织机构  --废版
SELECT DISTINCT
  T1.*,
  T2.LINE_CODE,
  T3.MAJOR_CODE,
  (SELECT NODE_NAME FROM WBPLAT.TDMBM01 WHERE NODE_CODE = T3.MAJOR_CODE) AS 专业名称,
  T4.groupEname,
  T4.groupCname
FROM QUERY_ORGUSER_BYUSER T1 LEFT JOIN WBPLAT.TDMBM06 T2 ON T1.ORG_CODE = T2.ORG_CODE
  LEFT JOIN WBPLAT.TDMBM05 T3 ON T1.ORG_CODE = T3.ORG_CODE
  LEFT JOIN (SELECT
               U1.LOGIN_NAME  AS userId,
               U1.USER_NAME   AS userName,
               U3.GROUP_ENAME AS groupEname,
               U3.GROUP_CNAME AS groupCname
             FROM IPLAT60.XS_USER U1 LEFT JOIN IPLAT60.XS_USER_GROUP_MEMBER U2 ON U1.USER_ID = U2.MEMBER_ID
               LEFT JOIN IPLAT60.XS_USER_GROUP U3 ON U2.PARENT_ID = U3.ID) T4 ON T1.USER_CODE = T4.userId
WHERE T4.groupEname = 'DM_004' AND T3.MAJOR_CODE = '11';