常用SQL.

-----------用户-人员-员工任岗关系

SELECT  a.FUSERID AS 用户内码 ,

        a.FNAME 用户名 ,

        b.FPERSONID AS 人员内码 ,

        bl.FNAME AS 人员姓名 ,

        dl.FNAME AS 任岗部门名称 ,

        el.FNAME AS 任岗岗位名称

FROM    T_SEC_USER a -- 用户表

INNER JOIN T_BD_PERSON b ON a.FLINKOBJECT = b.FPERSONID -- 人员表

LEFT JOIN T_BD_PERSON_L bl ON b.FPERSONID=bl.FPERSONID AND bl.FLOCALEID=2052

INNER JOIN T_BD_STAFF c ON b.FPERSONID=c.FPERSONID -- 员工任岗表

LEFT JOIN T_BD_DEPARTMENT d ON c.FDEPTID=d.FDEPTID -- 部门表

LEFT JOIN T_BD_DEPARTMENT_L dl ON d.FDEPTID=dl.FDEPTID AND dl.FLOCALEID=2052

LEFT JOIN T_ORG_POST e ON c.FPOSTID=e.FPOSTID -- 岗位表

LEFT JOIN T_ORG_POST_L el ON e.FPOSTID=el.FPOSTID AND el.FLOCALEID=2052 WHERE a.FNAME='demo'

-----------------------------------------------------------------------------

--物料名称
select m.fmaterialid,m.fmasterid, fname,m.fnumber from t_bd_material m join t_bd_material_l l on
l.fmaterialid=m.fmaterialid
where 1=1
--and m.fmaterialid=147002
and m.fnumber='XACPWM0067'
--and l.fname like '%扯面%'
order by m.fmaterialid desc

--组织
select V_SCM_OWNERORG_L.FNAME,* from V_SCM_OWNERORG left join V_SCM_OWNERORG_L on

V_SCM_OWNERORG.FORGID=V_SCM_OWNERORG_L.FORGID

select l.FNAME,* from T_ORG_ORGANIZATIONS o
join T_ORG_ORGANIZATIONS_L l
on l.FORGID=o.FORGID

--元数据
select * from t_meta_objecttype_l ml,t_meta_objecttype m where fname like '%工序汇报%' and ml.fid=m.fid

--部门
select b.FNAME,* from T_BD_DEPARTMENT a join T_BD_DEPARTMENT_L b on a.FDEPTID=b.FDEPTID

--单据转换
select * from T_META_CONVERTRULE where fid='PlanOrder_PPBom'

--通过元数据唯一id查询菜单
select * from T_META_CONSOLEDETAIL where FOBJECTID like '%stk_%'

--反写规则
select rl.FNAME, * from T_BF_WRITEBACKRULE r
left join T_BF_WRITEBACKRULE_L rl on rl.FID=r.FID and rl.FLOCALEID=2052
left join T_BF_WRITEBACKRULECUST rc on r.FID=rc.FID
where (rl.FNAME like '%生产线生产%' or rl.FID like '%sfc_%') and rc.FFORBIDSTATUS='A'

--转换规则
select * from T_META_CONVERTRULE where FID like '%rem_%'

select * from T_META_CONVERTRULE_L

--反写规则
select * from T_BF_WRITEBACKRULE where fsourceformid like '%SFC_%' and ftargetformid like '%SFC_%'

--参数
select * from t_bas_sysparameter

--枚举值
select * from T_META_FORMENUM e
join T_META_FORMENUM_L el on el.FID=e.FID
where FNAME like '%生产线领料单%'

--单据类型
select * from T_BAS_BILLTYPE where FBILLFORMID like '%REM_INSTOCK%'

--菜单操作对应类名
select * from T_MDL_FORMOPERATIONTYPE

--操作里面的服务
select * from T_MDL_FORMBUSINESS_L where FDESC like '%更新即时库存%'
select * from T_MDL_FORMBUSINESS where FACTIONID=45

--保存界面布局
select * from T_BAS_FormParameter
where FPARAOBJID like '%SFC_OperationPlanning%'

--用户
select * from T_SEC_USER

--用户参数
select * FROM T_BAS_UserParameter where FPARAMETEROBJID like '%rem_%'

--IDE函数
select fl.FNAME,f.FAPPEARANCECLASS,f.FELEMENTCLASS, * from T_MDL_ELEMENTTYPE f join T_MDL_ELEMENTTYPE_L fl on f.FID=fl.FID
where FELEMENTCLASS like '%OPERATIONSTATUS%' or FAPPEARANCECLASS like '%OPERATIONSTATUS%'

--枚举
select * from T_META_FORMENUM_L where fname ='REM_业务类型'
select * from t_Meta_Formenumitem where fid='c4a9508f-7af8-4edb-bb2f-fc21c866ee41'

--权限对象，权限项
select * from T_SEC_PERMISSIONOBJECT
select * from T_SEC_PERMISSIONOBJECTENTRY

--通过菜单查找元数据对象，查找表名（在xml中搜t_）
select * from T_META_OBJECTTYPE h join T_META_OBJECTTYPE_L l on l.FID=h.FID where FNAME like '%运算日志%'

--单位
select l.FNAME, * from T_BD_UNIT h join T_BD_UNIT_L l on h.FUNITID=l.FUNITID where l.FNAME like '%千克%'

6