金蝶云星空常用sql语句
1、常用单据对应的成本更新情况表
SP_PickMtrl为对应的表标识，T_HS_OUTINSTOCKSEQ_H 为历史表
SELECT * FROM T_BAS_ATTACHMENT
SELECT* FROM T_BAS_FILESERVERDIRINFO
SELECT* FROM T_BAS_FILESERVERFILEINFO
SELECT* FROM T_BAS_FILESERVERINFO
3、科目余额表
--账簿
select * from T_BD_ACCOUNTBOOK where fnumber ='4105' --FACCOUNTORGID:105904
select * from T_BD_ACCOUNTBOOK_l
--科目表
select * from T_BD_ACCOUNT where fnumber like '1621%' and fuseorgid=105904
--科目核算维度组分录
select * from T_BD_ACCOUNTFLEXENTRY
--基础资料表
select fname,a.* from T_BD_FLEXITEMPROPERTY a join T_BD_FLEXITEMPROPERTY_l b on a.fid=b.fid --FFLEXNUMBER:FFLEX4
--客户表
select fnumber,* from t_BD_Customer
select * from t_BD_Customer_l
--员工表
select * from t_hr_Empinfo
--去BOS 搜索,FVALUESSOURCE ***
--辅助资料
select * from T_BAS_ASSISTANTDATA
select b.FDATAVALUE,FNUMBER,a.* from T_BAS_ASSISTANTDATAENTRY a join T_BAS_ASSISTANTDATAENTRY_l b on a.FENTRYID=b.FENTRYID
--核算维度弹性域表(核算维度类型，数量）
select * from T_BD_FLEXITEMGROUP
select * from T_BD_FLEXITEMGRPENTRY where fid=15 --FINPUTTYPE :1 可选 2：必录
--核算维度内码表(凭证，科目余额表【FDETAILDID】)
select * from T_BD_FLEXITEMDETAILV --FID
--基础资料表（对照）
select * from T_BD_FLEXITEMPROPERTY
--凭证(fdetailid 核算维度内码)--T_GL_VOUCHERENTRY 明细表
select b.* from t_gl_voucher a join T_GL_VOUCHERENTRY b on a.fvoucherid=b.fvoucherid
where fyear=2024 and faccountid=158510
--科目余额表(fdetailid 核算维度内码)
select * from t_gl_balance
where fyear=2023
--凭证过账写到科目余额表
--不包含未过账凭证，直接取科目余额表数据，否则取凭证表的数据
select b.FNUMBER 科目编码,c.FNAME 科目名称,
concat(replace(E.FNUMBER,E.FNUMBER,E.FNUMBER+'/'),replace(F.FNUMBER,F.FNUMBER,F.FNUMBER+'/'),replace(G.FNUMBER,G.FNUMBER,G.FNUMBER+'/'),replace(H.FNUMBER,H.FNUMBER,H.FNUMBER))AS '核算维度编码',
concat(replace(E.FNAME,E.FNAME,E.FNAME+'/'),replace(F.FNAME,F.FNAME,F.FNAME+'/'),replace(G.FNAME,G.FNAME,G.FNAME+'/'),replace(H.FNAME,H.FNAME,H.FNAME) )AS '核算维度名称',
case when a.FBEGINBALANCE>0 then a.FBEGINBALANCE else '0' end as '期初余额借方',case when a.FBEGINBALANCE<0 then -a.FBEGINBALANCE else '0' end as '期初余额贷方',
FDEBITFOR as 本期发生额借方,FCREDITFOR as 本期发生额贷方,
FYTDDEBITFOR as '本年累计借方',
FYTDCREDITFOR as '本年累计贷方',
case when a.FENDBALANCEFOR>0 then a.FENDBALANCEFOR else '0' end as '期末余额借方',
case when a.FENDBALANCEFOR<0 then -a.FENDBALANCEFOR else '0' end as '期末余额贷方'
from T_GL_BALANCE a --科目余额表 --T_GL_VOUCHERENTRY
join T_BD_ACCOUNT b on a.FACCOUNTID=b.FACCTID and a.FCURRENCYID=0--科目表
join T_BD_ACCOUNT_l c on b.FACCTID=c.FACCTID and FLOCALEID=2052 --科目多语言表
join T_BD_FLEXITEMDETAILV d on a.FDETAILID=d.fid --核算维度内码表
join T_BD_ACCOUNTBOOK acb on a.FACCOUNTBOOKID=acb.fbookid --账簿
LEFT join (SELECT E1.FSUPPLIERID,FNUMBER,FNAME FROM T_BD_SUPPLIER E1 JOIN T_BD_SUPPLIER_L E2 ON E1.FSUPPLIERID=E2.FSUPPLIERID)E ON FSUPPLIERID=D.FFLEX4 --供应商
LEFT join (SELECT E1.FDEPTID,FNUMBER,FNAME FROM T_BD_Department E1 JOIN T_BD_Department_L E2 ON E1.FDEPTID=E2.FDEPTID)F ON F.FDEPTID=D.FFLEX5 --部门
LEFT join (SELECT E1.FCUSTID,FNUMBER,FNAME FROM T_BD_Customer E1 JOIN T_BD_Customer_L E2 ON E1.FCUSTID=E2.FCUSTID)G ON G.FCUSTID=D.FFLEX6--客户
LEFT join (SELECT E1.FID,FNUMBER,FNAME FROM T_HR_Empinfo E1 JOIN T_HR_Empinfo_L E2 ON E1.FID=E2.FID)H ON H.FID=D.FFLEX7 --员工
LEFT join (SELECT E1.FMATERIALID,FNUMBER,FNAME FROM T_BD_MATERIAL E1 JOIN T_BD_MATERIAL_L E2 ON E1.FMATERIALID=E2.FMATERIALID)I ON I.FMATERIALID=D.FFLEX8
LEFT join (SELECT E1.FEXPID,FNUMBER,FNAME FROM T_BD_Expense E1 JOIN T_BD_Expense_L E2 ON E1.FEXPID=E2.FEXPID)J ON J.FEXPID=D.FFLEX9
LEFT join (SELECT E1.FID,FNUMBER,FNAME FROM T_FA_ASSETTYPE E1 JOIN T_FA_ASSETTYPE_L E2 ON E1.FID=E2.FID)K ON K.FID=D.FFLEX10
--LEFT join (SELECT E1.FCUSTID,FNUMBER,FNAME FROM T_BD_Customer E1 JOIN T_BD_Customer_L E2 ON E1.FCUSTID=E2.FCUSTID)L ON L.FCUSTID=D.FFLEX11
--LEFT join (SELECT E1.FCUSTID,FNUMBER,FNAME FROM T_BD_Customer E1 JOIN T_BD_Customer_L E2 ON E1.FCUSTID=E2.FCUSTID)M ON M.FCUSTID=D.FFLEX12
--LEFT join (SELECT E1.FCUSTID,FNUMBER,FNAME FROM T_BD_Customer E1 JOIN T_BD_Customer_L E2 ON E1.FCUSTID=E2.FCUSTID)N ON N.FCUSTID=D.FFLEX13
where FYEAR=2024 and FPERIOD=5 and acb.fnumber='4105' and b.fnumber like '1621%' and b.fuseorgid=105904
4、根据数据库表名查询对应的单据名称
select FID
,(select FNAME from T_META_OBJECTTYPE_L where FID = T_META_OBJECTTYPE_Temp.FID and FLOCALEID = 2052) FNAME
,FKERNELXML ,Item
from
(select FKERNELXML.query('//TableName') 'Item', * from T_META_OBJECTTYPE) T_META_OBJECTTYPE_Temp
where convert(varchar(max),Item) like '%t_ER_ExpenseReimb%' --数据库表名(依情况替换此处't_ER_ExpenseReimb')
5、执行计划定时清空临时表语句
SELECT TOP 200 T.NAME AS FTABLENAME FROM sys.objects T with(nolock) WHERE T.TYPE='U' AND
EXISTS(SELECT 1 FROM T_BAS_TEMPORARYTABLENAME with(nolock)
WHERE FTABLENAME=T.NAME AND
(FCREATEDATE <= getdate()-1 OR (FPROCESSTYPE=1 and FCREATEDATE <= dateadd(hour,-8, getdate()))))
6、反写规则
select FSourceFormId,FTargetFormId,a.FID,FName from T_BF_WRITEBACKRULE a
inner join t_BF_WriteBackRuleCust b on a.fid=b.FID and FForbidStatus='A' and FSysStatus=0 left join T_BF_WRITEBACKRULE_L c on c.fid=a.fid and c.FLOCALEID=2052
7、根据字段名等查询对应的表
--只知道字段名，查它属于在那些表中出现
select 'select a.* from' + ' ' + a.table_name + ' ' + 'a;'
from information_schema.columns a
where a.column_name like '%F_ORA_BASE%'
order by 1;
-- 查询所有数据库名
select * from master..sysdatabases;
-- 查询所有表名
select name as '表名' from sysobjects
--where xtype='U';
-- 查询表结构、表字段
select * from information_schema.columns where table_name = 'T_META_FORMENUM';
--用户
SELECT * FROM T_SEC_USER
--查询当前数据库中的锁
SELECT
A.OWNER, --OBJECT所属用户
A.OBJECT_NAME, --OBJECT名称（表名）
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID, --锁表用户的session
B.ORACLE_USERNAME, --锁表用户的Oracle用户名
B.OS_USER_NAME, --锁表用户的操作系统登陆用户名
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE, --锁表用户的计算机名称（例如：WORKGROUP\UserName）
C.STATUS, --锁表状态
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM --锁表用户所用的数据库管理工具（例如：ob9.exe）
FROM
ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE
A.OBJECT_ID = B.OBJECT_ID
AND B.PROCESS = C.PROCESS
--如下命令来kill掉当前锁表的项
alter system kill session 'sid, serial#'
8、查询单据转换规则信息
select tobjSource.FNAME as [源单] , tobjTarget.FNAME as [目标单] , tul.FNAME as [规则名称] , tu.FID -- 单据转换规则ID , tu.FSOURCEFORMID -- 源单业务对象内码 , tu.FTARGETFORMID -- 目标单业务对象内码 , tu.FSTATUS -- 规则状态|0，停用|1，启用 , tu.FISDEFAULT -- 是否默认规则|1 是|0 否 --, tu.FKERNELXML -- 通用版本XML from T_META_CONVERTRULE tu -- 单据转换规则 left join T_META_CONVERTRULE_l tul -- 单据转换规则 多语言 on tul.FID = tu.FID and tul.FLOCALEID = '2052' left join T_META_OBJECTTYPE_L tobjSource -- 业务对象多语言 on tobjSource.FLOCALEID = '2052' and tobjSource.FID = tu.FSOURCEFORMID left join T_META_OBJECTTYPE_L tobjTarget -- 业务对象多语言 on tobjTarget.FLOCALEID = '2052' and tobjTarget.FID = tu.FTARGETFORMID where tobjTarget.FNAME like '应付单%' -- 目标单为 -- OR tobjSource.FNAME like '发货%' -- 源单为 order by tobjTarget.FNAME
9、枚举值相关表结构
select * from T_META_FORMENUM;--枚举主表
select * from T_META_FORMENUM_L a where a.FNAME like '是否紧急%';--枚举多语言表
select * from T_META_FORMENUMITEM;--枚举项主表
select * from T_META_FORMENUMITEM_L;--枚举项多语言表
select * from T_PLM_CFG_FIELD;
--联合查询枚举数据
select al.fname'枚举类型名称',
b.FSEQ'序号',
b.FVALUE'枚举值',
bl.FCAPTION'枚举项名称',
b.FINVALID as '是否禁用'
from T_META_FORMENUM as a
inner join T_META_FORMENUM_L as al on al.FID = a.FID and al.FLOCALEID = '2052'
inner join T_META_FORMENUMITEM as b on b.fid = a.FID
inner join T_META_FORMENUMITEM_L as bl on bl.FENUMID = b.FENUMID and bl.FLOCALEID = '2052'
where 1=1
--and al.FNAME like '%调拨%'
and al.fname like '是否紧%'
order by a.FID,b.FSEQ
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？