技术答疑.常用SQL

持续更新......

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

【登录用户】

SELECT FUSERID AS 用户内码,FNAME AS 用户名称,FUSERACCOUNT AS 用户账号,* 

FROM T_SEC_USER

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

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

【数据中心】

--注意：查询数据中心，必须连接管理中心数据库查询

SELECT b.FNAME AS [数据中心(账套名称)],a.FDATACENTERID AS [数据中心内码(DBID)],a.FVISION AS 产品版本号,a.* 

FROM T_BAS_DATACENTER a 

LEFT JOIN T_BAS_DATACENTER_L b ON a.FDATACENTERID=b.FDATACENTERID AND b.FLOCALEID=2052

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

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

【上机操作日志】

SELECT b.FNAME AS 用户名,c.FNAME AS 子系统,d.FNAME AS 业务对象名称,a.FOPERATENAME  AS 操作名称,a.FDESCRIPTION AS 操作描述,a.* 

FROM T_BAS_OPERATELOG a 

LEFT JOIN T_SEC_USER b ON a.FUSERID=b.FUSERID 

LEFT JOIN T_META_SUBSYSTEM_L c ON a.FSUBSYSTEMID=c.FID AND c.FLOCALEID=2052 

LEFT JOIN T_META_OBJECTTYPE_L d ON a.FOBJECTTYPEID=d.FID AND d.FLOCALEID=2052 

WHERE a.FUSERID=100008

【上机操作日志归档表】

SELECT b.FNAME AS 用户名,c.FNAME AS 子系统,d.FNAME AS 业务对象名称,a.FOPERATENAME AS 操作名称,a.FDESCRIPTION AS 操作描述,a.* 

FROM T_BAS_OPERATELOGBK a 

LEFT JOIN T_SEC_USER b ON a.FUSERID=b.FUSERID 

LEFT JOIN T_META_SUBSYSTEM_L c ON a.FSUBSYSTEMID=c.FID AND c.FLOCALEID=2052 

LEFT JOIN T_META_OBJECTTYPE_L d ON a.FOBJECTTYPEID=d.FID AND d.FLOCALEID=2052 

WHERE a.FUSERID=100008

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

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

【元数据修改日志】

SELECT TOP 10 c.FNAME AS 业务对象名称,d.FNAME AS 修改人,a.*,b.FKERNELXMLLANG 

FROM T_BAS_METADATALOG a 

LEFT JOIN T_BAS_METADATALOG_L b ON a.FID=b.FID AND b.FLOCALEID=2052 

LEFT JOIN T_META_OBJECTTYPE_L c ON a.FOBJECTID=c.FID AND c.FLOCALEID=2052 

LEFT JOIN T_SEC_USER d ON a.FUSERID=d.FUSERID 

ORDER BY a.FCREATETIME DESC

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

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

【子系统】

SELECT b.FNAME AS 子系统名称,a.FID AS 子系统内码,a.* 

FROM T_META_SUBSYSTEM a 

LEFT JOIN T_META_SUBSYSTEM_L b ON a.FID=b.FID AND b.FLOCALEID=2052

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

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

【领域模型】

SELECT b.FNAME AS 领域模型名称,a.FID AS 领域模型内码,a.* 

FROM T_MDL_DOMAINMODETYPE a 

LEFT JOIN T_MDL_DOMAINMODETYPE_L b ON a.FID=b.FID AND b.FLOCALEID=2052

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

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

【业务对象】

SELECT b.FNAME AS 业务对象名称,a.FID AS [业务对象标识(FormId)],* 

FROM T_META_OBJECTTYPE a 

LEFT JOIN T_META_OBJECTTYPE_L b ON a.FID=b.FID AND b.FLOCALEID=2052 

WHERE a.FID='SAL_OUTSTOCK'

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

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

【单据视图】

SELECT c.FNAME AS 业务对象名称,a.FDEPENDENCYOBJECTID AS [业务对象内码 (FormId)],b.FNAME AS 视图名称,a.FID AS [视图内码(layoutid)],* 

FROM T_META_OBJECTTYPEVIEW a 

LEFT JOIN T_META_OBJECTTYPEVIEW_L b ON a.FID=b.FID AND b.FLOCALEID=2052 

LEFT JOIN T_META_OBJECTTYPE_L c ON a.FDEPENDENCYOBJECTID=c.FID AND  c.FLOCALEID=2052 

WHERE a.FDEPENDENCYOBJECTID IN ('SAL_OUTSTOCK','DE_CD_OutStock','00f0614c-d928-46b6-8e79-d1ac94ae867a')

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

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

【表单操作】

SELECT b.FNAME AS 操作名称,a.FID AS 操作内码,a.* 

FROM T_MDL_FORMOPERATIONTYPE a 

LEFT JOIN T_MDL_FORMOPERATIONTYPE_L b ON a.FID=b.FID AND b.FLOCALEID=2052 

ORDER BY a.FID

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

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

【表单服务】

SELECT b.FDESC AS 服务名称,a.FACTIONID AS 服务内码,a.* 

FROM T_MDL_FORMBUSINESS a 

LEFT JOIN T_MDL_FORMBUSINESS_L b ON a.FACTIONID=b.FACTIONID AND b.FLOCALEID=2052

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

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

【操作内置校验器】

SELECT b.FNAME AS 校验器名称,a.* FROM T_MDL_FORMVALIDATIONTYPE a 

LEFT JOIN T_MDL_FORMVALIDATIONTYPE_L b ON a.FTYPEID=b.FTYPEID AND b.FLOCALEID=2052

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

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

【过滤方案】

SELECT b.FDESCRIPTION AS 过滤方案名称,a.FSCHEMEID AS 过滤方案内码,c.FNAME AS 业务对象名称,d.FNAME AS 用户名,* 

FROM T_BAS_FILTERSCHEME a 

LEFT JOIN T_BAS_FILTERSCHEME_L b ON a.FSCHEMEID=b.FSCHEMEID AND b.FLOCALEID=2052 

LEFT JOIN T_META_OBJECTTYPE_L c ON a.FFORMID=c.FID AND c.FLOCALEID=2052 

LEFT JOIN T_SEC_USER d ON a.FUSERID=d.FUSERID 

WHERE a.FFORMID='PUR_PurchaseOrder'

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

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

【过滤符】

SELECT b.FNAME AS 过滤符名称,a.FCONDITIONTYPEID AS 符号集编码,a.* FROM T_MDL_FILTERCONDITION a 

LEFT JOIN T_MDL_FILTERCONDITION_L b ON a.FID=b.FID AND b.FLOCALEID=2052 

ORDER BY a.FCONDITIONTYPEID

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

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

【语言】

SELECT a.FMSNUMBER AS [语言ID(LCID)],b.FNAME AS 语言名称,a.* 

FROM T_BD_LANGUAGE a 

LEFT JOIN T_BD_LANGUAGE_L b ON a.FID=b.FID AND b.FLOCALEID=2052 

ORDER BY a.FMSNUMBER

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

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