常用Sql

1、官常用脚本

2、员工和用户的关系脚本

3、业务流程相关脚本sql

二、常用脚本

1、得到领域下元素中属性默认值

比如得到移动领域下的下拉列表字段中的必录显示空白行属性的默认值

--得到移动领域FID 1400

select * from T_MDL_DOMAINMODETYPE_L where FName like '%移动业务%' and FLocaleId=2052

--得到下拉列表元素FID 9

select * from T_MDL_ELEMENTTYPE_l where Fname like '%下拉列表%' and FLocaleId=2052

--得到领域和元素的关系FEntryId 34121

select * from T_MDL_DOMAINMODELELEMENTMAP where Fid=1400 and FElementTypeId=9 

--得到必录显示空白行属性FID 1946

select * from T_MDL_ELEMENTPROPERTY_L where Fname like '%必录显示空白行%' and FLocaleId=2052

--得到必录显示空白行在移动领域下拉列表中的默认值

select * from T_MDL_DOMAINMODELELPROPMAP where FEntryId=34121 and FElementPropertyId=1946

2、查询上机操作日志

当前表：t_bas_operateLog  备份表：t_bas_operateLogBK 

select Convert(varchar(100),FDateTime,120) FDate, * from t_bas_operateLogBK where FObjectTypeId='FormId' 

and FDescription like '%编码%' and FDateTime>'2002-02-27' order by FDateTime

3、根据一个表来更新另外一个表，msSql和oracle都适应

MERGE INTO target_table

USING source_table

ON condition

WHEN MATCHED THEN 

operation

WHEN NOT MATCHED THEN 

operation;

4、查询用户组织和角色关系

select t0.FUSERID,t0.FNAME userName,t1.FORGID,t12.FNAME orgName,t2.FROLEID,t22.FNAME roleName from T_SEC_USER t0

inner join T_SEC_USERORG  t1 on t1.FUSERID = t0.FUSERID

left join T_ORG_ORGANIZATIONS_L t12 on t12.FORGID = t1.FORGID and t12.FLOCALEID=2052

inner join T_SEC_USERROLEMAP t2 on t2.FENTITYID = t1.FENTITYID

left join T_SEC_ROLE_L t22 on t22.FROLEID = t2.FROLEID and t22.FLOCALEID=2052

where t0.FNAME='Demo'

5、查询业务对象，子系统，子系统分组

SELECT t0.FID,t1.FNAME,  t3.FNAME FTOPCLASSNAME,t4.FNAME FSUBSYSTEMNAME

FROM

T_META_OBJECTTYPE t0

LEFT JOIN V_META_OBJECTTYPE_L t1 ON t0.FID= t1.FID AND t1.FLOCALEID = 2052 --业务对象

INNER JOIN T_META_SUBSYSTEM t2 ON t0.FSUBSYSID = t2.FID --子系统

LEFT JOIN T_META_SUBSYSTEM_L t3 ON t0.FSUBSYSID = t3.FID AND t3.FLOCALEID =2052

LEFT JOIN T_META_TOPCLASS_L t4 ON T2.FTOPCLASSID=  t4.FTOPCLASSID AND t4.FLOCALEID = 2052 --子系统分组

WHERE  t0.FDEVTYPE<>2

6、查询用户业务对象字段权限

SELECT t0.FUSERID,t0.FName,t1.FORGID,t2.FNAME orgName ,t3.FROLEID,t4.FNAME roleName,t5.FOBJECTTYPEID,t7.Fname objectName,

case WHEN t6.FVIEW is null then '有权'

when t6.FVIEW =1 then '无权' else '禁用'  end 'ViewPermission',

case WHEN t6.FEDIT is null then '有权'

when t6.FEDIT =1 then '无权' else '禁用'  end 'EditPermission'

,t6.FFIELDKEY

  FROM  T_SEC_USER t0

  INNER JOIN T_SEC_USERORG t1 ON  t1.FUSERID = t0.FUSERID

  left join T_ORG_ORGANIZATIONS_L t2 on t2.FORGID = t1.FORGID and t2.FLOCALEID=2052

  INNER JOIN T_SEC_USERROLEMAP t3 ON t3.FENTITYID = t1.FENTITYID

    left join T_SEC_ROLE_L t4 on t4.FROLEID = t3.FROLEID and t4.FLOCALEID=2052

  LEFT JOIN T_SEC_FIELDPERMISSION t5 ON t5.FROLEID = t3.FROLEID

  LEFT JOIN T_SEC_FIELDPERMISSIONENTRY t6 ON t6.FITEMID = t5.FITEMID

  left join V_META_OBJECTTYPE_L t7 on t7.FID = t5.FOBJECTTYPEID and t7.FLOCALEID=2052

  WHERE t0.FName = 'demo' AND t5.FOBJECTTYPEID ='BOS_BillEE'