SELECT GDTEL_USER.userid 用户编号,GDTEL_USER.useraccount 帐号, GDTEL_ORG.orgid 组织编号, GDTEL_ORG.ORGNAME 组织名称, GDTEL_USER.enname 英文名, GDTEL_USER.zhname 中文名, GDTEL_USER.fullname 用户全称, GDTEL_USER.posid 岗位编号, POSITION.posname 岗位名称, GDTEL_USER.email 邮箱, GDTEL_USER.ophone 办公电话, GDTEL_USER.mobile 移动电话, GDTEL_USER.phs 小灵通, GDTEL_USER.fax 传真号码, GDTEL_USER.other 其他, GDTEL_USER.showorder 显示序号,GDTEL_USER.status 状态编号,
CASE WHEN GDTEL_USER.status='0' THEN '在职' ELSE CASE WHEN GDTEL_USER.status='1' THEN '离职' ELSE '其他' END END AS 状态
FROM GDTEL_USER
Left Outer Join GDTEL_UserOrg On GDTEL_USER.userid=GDTEL_UserOrg.userid
Left Outer Join GDTEL_ORG On GDTEL_ORG.orgid=GDTEL_UserOrg.orgid
Left Outer Join POSITION On GDTEL_USER.posid=POSITION.posid
WHERE GDTEL_USER.userid IN
(SELECT GDTEL_UserOrg.userid FROM GDTEL_UserOrg WHERE GDTEL_UserOrg.orgid in ( select GDTEL_ORG.orgID from GDTEL_ORG connect by prior GDTEL_ORG.orgid=GDTEL_ORG.parentid start with GDTEL_ORG.orgid = '277' ) )
ORDER BY GDTEL_USER.showorder DESC
CASE WHEN GDTEL_USER.status='0' THEN '在职' ELSE CASE WHEN GDTEL_USER.status='1' THEN '离职' ELSE '其他' END END AS 状态
FROM GDTEL_USER
Left Outer Join GDTEL_UserOrg On GDTEL_USER.userid=GDTEL_UserOrg.userid
Left Outer Join GDTEL_ORG On GDTEL_ORG.orgid=GDTEL_UserOrg.orgid
Left Outer Join POSITION On GDTEL_USER.posid=POSITION.posid
WHERE GDTEL_USER.userid IN
(SELECT GDTEL_UserOrg.userid FROM GDTEL_UserOrg WHERE GDTEL_UserOrg.orgid in ( select GDTEL_ORG.orgID from GDTEL_ORG connect by prior GDTEL_ORG.orgid=GDTEL_ORG.parentid start with GDTEL_ORG.orgid = '277' ) )
ORDER BY GDTEL_USER.showorder DESC
其中:
1)connect by prior GDTEL_ORG.orgid = GDTEL_ORG.parentid start with GDTEL_ORG.orgid = '277'
表示取出 orgid 为 277 的所有下级子孙组织
2)CASE WHEN .... THEN .... ELSE .... END
这是条件分支语句
3)Left Outer Join .... On
这是左外连接,效率比 Inner Join 低,但可以取出左表所有字段,即使和右表记录不匹配