查出所有员工所在的分公司sql语句。

来源：互联网发布：微信小程序上传数据编辑：程序博客网时间：2024/05/16 17:55

select nvl((nvl((select b.name from depart_name_view b where dorder>1 and instr((select a.plist from oa_depart a where   a.id = t.fid),b.id||',') <> 0),(select b.name from depart_name_view b where dorder=1 and instr((select a.plist from oa_depart a where   a.id = t.fid), ','||b.id||',') <> 0))),(select b.name from depart_name_view b where dorder=1 and b.id=t.fid)) as fgs_name,
       t.id,
       t.fid,
       t.username,
       t.password,
       t.partlist,
       t.realname,
       t.sex,
       t.birth,
       t.idcard,
       t.native,
       t.nation,
       t.culture,
       t.polity,
       t.address,
       t.zip,
       t.homephone,
       t.telphone,
       t.mobile,
       t.email,
       t.depart,
       t.duty,
       t.employtype,
       t.postdate,
       t.leftdate,
       t.memo,
       t.fig,
       t.bossgh,
       t.salemember,
       t.disabled,
       t.updateuser,
       t.updatetime,
       t.createuser,
       t.createtime
from oa_employees t;

同事写的：

create or replace view depart_employees_view as
select --nvl((select b.name from depart_name_view b where dorder>1 and instr((select a.plist from oa_depart a where   a.id = t.fid), ','||b.id||',') <> 0),'广西北海公司') as fgs_name,
       nvl((select b.name from depart_name_view b where dorder>1 and instr(','||(select a.plist from oa_depart a where   a.id = t.fid), ','||b.id||',') <> 0),nvl((select c.name from depart_name_view c where c.id=(select (case when g.pid>0 then g.pid else g.id end) from oa_depart g where g.id = t.fid)),'广西北海公司')) as fgs_name,
       t.id,
       t.fid,
       t.username,
       t.password,
       t.partlist,
       t.realname,
       t.sex,
       t.birth,
       t.idcard,
       t.native,
       t.nation,
       t.culture,
       t.polity,
       t.address,
       t.zip,
       t.homephone,
       t.telphone,
       t.mobile,
       t.email,
       t.depart,
       t.duty,
       t.employtype,
       t.postdate,
       t.leftdate,
       t.memo,
       t.fig,
       t.bossgh,
       t.salemember,
       t.disabled,
       t.updateuser,
       t.updatetime,
       t.createuser,
       t.createtime
from oa_employees t;