[AgentFlow]SQL_組織

/*
 * 查詢多重職務
 */
SELECT a.id, a.memid, a.username, a.loginid, b.name, b.id, c.name
  FROM mem_geninf a, dep_geninf b, rol_geninf c
 WHERE a.mainroleid = c.rolid
   AND c.depid = b.depid
   AND a.memid in
       (select memid from rol_mem group by memid having count(*) > 1)
   AND a.resign <> 'true' --指定非離職人員
   AND b.id like '08%' --指定公司組織 
 order by username

/*
 * 部門以下所有部門
 */
SELECT *
  FROM dep_Geninf
 start with depid = 'DEP61131148392187557'
connect by prior depid = parentid;

/*
 * 部門以上所有部門
 */
SELECT *
  FROM dep_Geninf
 start with depid = 'DEP61131148392187557'
connect by prior parentid = depid;

/*
 * 查詢目前MEMID專案職務
 */
SELECT a.rolid,
       a.memid,
       b.parentid,
       b.prjid,
       b.name,
       b.synopsis,
       c.authorid,
       c.name prj_name
  FROM prjrol_mem a, prjrol_geninf b, project c
 WHERE a.memid = 'MEM47111273625147618'
   AND a.rolid = b.rolid
   AND b.prjid = c.prjid;

/*
 * 更新所有專案職務給代理人
 */   
update prjrol_geninf
   set memid = '代理人memid'
 where memid = 'MEM47471276599403061';


/*
 * 關閉代理人資料
 */
update deputy_geninf set state = 'false' WHERE memid ='MEM47111273625147618'

SELECT a.*,rowid FROM deputyrule a
WHERE memid = 'MEM41471180408852437'

/*
 * 查詢人員清單(員工編號、員工姓名、部門代號、部門名稱、職稱) 01 = chen ,02 = chensource ,08 = 勤昆 ,09 = 東莞
 */
SELECT c.id          emp_id,
       c.username,
       d.id          dep_id,
       d.name        dep_name,
       a.name        Title,
       a.synopsis    real_title,
       c.resign,
       c.ONBOARDDATE,
       c.email
  FROM rol_geninf a, rol_mem b, mem_geninf c, dep_geninf d
 WHERE a.rolid = b.rolid
   AND b.memid = c.memid
   AND a.depid = d.depid
   AND d.id like '01%'
   AND c.resign = 'false'
 order by c.id
 
/*
 * 查詢人員資料
 */
SELECT * FROM mem_geninf
WHERE memid='MEM48001281579688046'

/*
 * 查詢部門資料
 */
SELECT * FROM dep_geninf
WHERE depid= 'DEP05211388484796125'

/*
 * 查詢eflow帳號
 */
SELECT * FROM eflow.mem_geninf WHERE username = '陳莉'

/*
 * 更新資料庫人員
 */
UPDATE mem_geninf SET email = 'yolinche@XXXX.com' WHERE loginid = 'a0682'