@[TOC](由于二开常有写SQL找表需求，特地分享整理了相关表,有需要的CTRL+F搜索查看即可，欢迎大家补充纠正)
# 云星空开发必备：打造金蝶云星空最全数据库表备注信息，会按模块、按板块持续更新中...
[篇一：云星空开发必备：打造金蝶云星空最全数据库表，会按模块按板块持续更新](https://wenku.my7c.com/article/560479083298040320?productLineId=1&lang=zh-CN)
# 常用查询SQL
## 查询辅助资料数据
```
SELECT a.FID AS 操作内码,b.FNAME AS 操作名称,a.FOPERATION AS 操作编码
,a.FSERVICECLASS AS 运行时类全名,a.FDESIGNERCLASS AS 设计时类全名
,a.* FROM T_MDL_FORMOPERATIONTYPE a
JOIN T_MDL_FORMOPERATIONTYPE_L b ON a.FID=b.FID AND b.FLOCALEID=2052
```
## 查询即时库存
```
SELECT
TI.FSTOCKORGID,
OL.FNAME fstockorgname,
M.FNUMBER '物料编码', --物料编码
ML.FNAME '物料名称',--物料名称
TSL.FNAME '仓库',--仓库
TI.FSTOCKLOCID '仓位ID',--仓位ID
TUL0.FNAME '库存主单位',--库存主单位
TI.FQTY '主单位库存量',
TUL1.FNAME '基本单位', --基本单位
TI.FBASEQTY '基本单位库存量',
TUL2.FNAME '库存辅单位',--库存辅单位
TI.FSECQTY '库存辅单位库存量',
TL.FNUMBER flotnumber,
TI.FAUXPROPID,
CASE
WHEN TMS.FISEXPPARTOFLOT = '1' THEN
CONVERT (
CHAR (10),
TL.FPRODUCEDATE,
20
)
ELSE
CONVERT (
CHAR (10),
TI.FPRODUCEDATE,
20
)
END fproducedate,
CASE
WHEN TMS.FISEXPPARTOFLOT = '1' THEN
CONVERT (CHAR(10), TL.FEXPIRYDATE, 20)
ELSE
CONVERT (CHAR(10), TI.FEXPIRYDATE, 20)
END fexpirydate,
TB.FNUMBER fbomnumber,--BOM编号
TSSL.FNAME fstockstatus,
TI.FOWNERTYPEID,
VO_L.FNAME fownername,
TI.FKEEPERTYPEID,--保管者类型
VK_L.FNAME fkeepername,
TMS.FSTOREURNUM,
TMS.FSTOREURNOM,
TMS.FISSNMANAGE,
TSK.FALLOWMINUSQTY,
TUS.FPRECISION fstkprecision,
TUS.FROUNDTYPE froundtype,
TUE.FPRECISION fsecprecision,
TI.FMTONO,
TI.FPROJECTNO,
TSUB.FBASELOCKQTY fbaselockqty,
TSUB.FSECLOCKQTY fseclockqty,
'' fstocklocname
FROM
AIS20231009213721.dbo.T_STK_INVENTORY TI
LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L OL ON (
TI.FSTOCKORGID = OL.FORGID
AND OL.FLOCALEID = 2052
) --机构名称
INNER JOIN T_BD_MATERIAL M ON (
M.FMASTERID = TI.FMATERIALID
AND (
M.FUSEORGID = TI.FSTOCKORGID
OR EXISTS (
SELECT
1
FROM
T_META_BASEDATATYPE BT
WHERE
(
BT.FBASEDATATYPEID = 'BD_MATERIAL'
AND BT.FSTRATEGYTYPE = 1
)
)
)
)--物料
LEFT OUTER JOIN T_BD_MATERIAL_L ML ON (
M.FMATERIALID = ML.FMATERIALID
AND ML.FLOCALEID = 2052
)--物料名称、规格型号
INNER JOIN T_BD_MATERIALSTOCK TMS ON M.FMATERIALID = TMS.FMATERIALID
LEFT OUTER JOIN T_ENG_BOM TB ON (
TB.FMASTERID = TI.FBOMID
AND (
TB.FUSEORGID = TI.FSTOCKORGID
OR EXISTS (
SELECT
1
FROM
T_META_BASEDATATYPE BT
WHERE
(
BT.FBASEDATATYPEID = 'ENG_BOM'
AND BT.FSTRATEGYTYPE = 1
)
)
)
)
LEFT OUTER JOIN T_BD_STOCKSTATUS_L TSSL ON (
TI.FSTOCKSTATUSID = TSSL.FSTOCKSTATUSID
AND TSSL.FLOCALEID = 2052
)
LEFT OUTER JOIN T_BD_LOTMASTER TL ON (
(
TI.FLOT = TL.FMASTERID
AND TI.FSTOCKORGID = TL.FUSEORGID
)
AND TL.FBIZTYPE = '1'
)
LEFT OUTER JOIN T_BD_UNIT TUS ON TMS.FSTOREUNITID = TUS.FUNITID
LEFT OUTER JOIN T_BD_UNIT_L TUL0 ON (
TMS.FSTOREUNITID = TUL0.FUNITID
AND TUL0.FLOCALEID = 2052
)
INNER JOIN T_BD_STOCK TSK ON (
TSK.FMASTERID = TI.FSTOCKID
AND (
TSK.FUSEORGID = TI.FSTOCKORGID
OR EXISTS (
SELECT
1
FROM
T_META_BASEDATATYPE BT
WHERE
(
BT.FBASEDATATYPEID = 'BD_STOCK'
AND BT.FSTRATEGYTYPE = 1
)
)
)
)
LEFT OUTER JOIN T_BD_STOCK_L TSL ON (
TSL.FSTOCKID = TI.FSTOCKID
AND TSL.FLOCALEID = 2052
)
LEFT OUTER JOIN T_BD_UNIT_L TUL1 ON (
TI.FBASEUNITID = TUL1.FUNITID
AND TUL1.FLOCALEID = 2052
)
LEFT OUTER JOIN T_BD_UNIT TUE ON TMS.FAUXUNITID = TUE.FUNITID
LEFT OUTER JOIN T_BD_UNIT_L TUL2 ON (
TI.FSECUNITID = TUL2.FUNITID
AND TUL2.FLOCALEID = 2052
)
LEFT OUTER JOIN V_ITEMCLASS_OWNER VO ON (
(
VO.FMASTERID = TI.FOWNERID
AND VO.FFORMID = TI.FOWNERTYPEID
)
AND (
VO.FUSEORGID = TI.FSTOCKORGID
OR VO.FUSEORGID = 0
OR EXISTS (
SELECT
1
FROM
T_META_BASEDATATYPE BT
WHERE
(
BT.FBASEDATATYPEID = VO.FFORMID
AND BT.FSTRATEGYTYPE = 1
)
)
)
)
LEFT OUTER JOIN V_ITEMCLASS_OWNER_L VO_L ON (
VO.FITEMID = VO_L.FITEMID
AND VO_L.FLOCALEID = 2052
)
LEFT OUTER JOIN V_ITEMCLASS_KEEPER VK ON (
(
VK.FMASTERID = TI.FKEEPERID
AND VK.FFORMID = TI.FKEEPERTYPEID
)
AND (
VK.FUSEORGID = TI.FSTOCKORGID
OR VK.FUSEORGID = 0
OR EXISTS (
SELECT
1
FROM
T_META_BASEDATATYPE BT
WHERE
(
BT.FBASEDATATYPEID = VK.FFORMID
AND BT.FSTRATEGYTYPE = 1
)
)
)
)
LEFT OUTER JOIN V_ITEMCLASS_KEEPER_L VK_L ON (
VK.FITEMID = VK_L.FITEMID
AND VK_L.FLOCALEID = 2052
)
--以下仓位
LEFT OUTER JOIN T_BAS_FLEXVALUESDETAIL FVD ON TI.FSTOCKLOCID = FVD.FID
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L VFF100001 ON (
FVD.FOPCODE = VFF100001.FENTRYID
AND VFF100001.FLOCALEID = 2052
)
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L VFF100002 ON (
FVD.FOPCODE = VFF100002.FENTRYID
AND VFF100002.FLOCALEID = 2052
)
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L VFF100003 ON (
FVD.FOPCODE = VFF100003.FENTRYID
AND VFF100003.FLOCALEID = 2052
)
--以下锁库和预留
LEFT OUTER JOIN (
SELECT
TLKE.FSUPPLYINTERID finventryid,
SUM (TLKE.FBASEQTY) fbaselockqty,
SUM (TLKE.FSECQTY) fseclockqty
FROM
T_PLN_RESERVELINKENTRY TLKE
INNER JOIN T_PLN_RESERVELINK TLKH ON TLKE.FID = TLKH.FID
WHERE
(
TLKE.FSUPPLYFORMID = 'STK_Inventory'
AND TLKE.FLINKTYPE = '4'
)
GROUP BY
TLKE.FSUPPLYINTERID
) tsub ON TI.FID = TSUB.FINVENTRYID
WHERE TI.FBASEQTY > 0
```
## 通过物料名称查询物料
```
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 '%wafer%'
order by m.fmaterialid desc
```
## 用户权限、单据类型相关
```
---单据类型
select * from T_BAS_BILLTYPE a inner join T_BAS_BILLTYPE_l b on a.FBILLTYPEID=b.FBILLTYPEID
and b.FLOCALEID=2052
where a.FBILLTYPEID like '%eacb50844fc84a10b03d7b841f3a6278%'
--组织
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_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_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 '%千克%'
```
## 查询枚举值
```
--枚举值
select * from T_META_FORMENUM e
join T_META_FORMENUM_L el on el.FID=e.FID
where FNAME like '%生产线领料单%'
--枚举
select * from T_META_FORMENUM_L where fname ='REM_业务类型'
select * from t_Meta_Formenumitem where fid='c4a9508f-7af8-4edb-bb2f-fc21c866ee41'
```
## 根据仓位id查询仓位编码名称
```
--根据仓位id查询仓位编码名称
SELECT T.*,TL.FNAME
FROM T_BAS_FLEXVALUESDETAIL detail --值列表
inner join T_BAS_FLEXVALUESENTRY T on detail.FF100018=T.FENTRYID
INNER JOIN T_BAS_FLEXVALUESENTRY_L TL ON T.FENTRYID=TL.FENTRYID
WHERE detail.FF100018 = 100504 --T.FNUMBER='CJ006' or TL.FNAME='板磨-A6/PS3-5四辊平板磨砂机(A6)';
【仓位相关表说明】
---仓位值集列表
select * from T_BAS_FLEXVALUES
select * from T_BAS_FLEXVALUES_L
select * FROM T_BAS_FLEXVALUESENTRY
select * from T_BAS_FLEXVALUESENTRY_L
--仓库 (启用仓位后)
select * from t_BD_STOCK
select * from T_BD_STOCKFLEXDETAIL
select * from T_BD_STOCKFLEXITEM
--值集合组合
select * from T_BD_FLEXVALUESCOM
--值使用
select * from T_BAS_FLEXVALUESDETAIL
--值集列表
SELECT T1.FNUMBER AS 仓位值集编码, T2.FNAME AS 仓位值集名称, T1.FFLEXNUMBER AS 维度Key FROM T_BAS_FLEXVALUES T1
JOIN T_BAS_FLEXVALUES_L T2 ON T1.FID = T2.FID ORDER BY T1.FNUMBER
其中FFLEXNUMBER：维度Key 对应为BOS中的"值集资料维度数据"，FormId: BD_FLEXVALUESDETAIL_EXTEND, 查找，
```
## 生产订单下查下推单据
```
SELECT
t0.FID,
t0.FBILLNO 单据编号 ,
t0.FFORMID 交易类型码,
t0.F_HXZB_YDDJLX 源单单据类型ID,
t1.FID,
t0.FBILLTYPE 单据类型ID,
t1.FENTRYID 入库单关联FID,
t1.FMOID 生产订单内码 ,
t1.FSRCBILLNO 生产订单源单编号,
t1.FMOBILLNO 生产订单编号 ,
t1.FSRCBILLTYPE 源单单据类型
FROM
T_PRD_INSTOCK t0
LEFT OUTER JOIN T_PRD_INSTOCKENTRY t1 ON t0.FID = t1.FID
WHERE
(
-- 指定生产入库单单号
t1.FENTRYID = 100407
AND (
t0.FSTOCKORGID IN (1, 100206, 170187, 0)
AND t0.FFORMID = 'PRD_INSTOCK'
)
)
```
## 生产入库单列表
```
SELECT
*
FROM
(
SELECT
t0.FID fid,
t0.FBILLNO fbillno,
t0.F_HXZB_YDDJLX f_hxzb_yddjlx_id,
t0.FDOCUMENTSTATUS fdocumentstatus,
t0.FSTOCKORGID fstockorgid_id,
t0.FPRDORGID fprdorgid_id,
t1.FENTRYID 行内码ID,
t1.FSRCINTERID 源单内码 ,
t1.FSRCBILLNO 源单编号 ,
t1.FSRCENTRYID FSRCENTRYID_FENTRYID,
t1.FMATERIALID fmaterialid_id,
-- t1.FPRODUCTTYPE fproducttype,
-- t1.FUNITID funitid_id,
-- t1.FMUSTQTY fmustqty,
-- t1.FREALQTY frealqty,
-- t1.FSTOCKID fstockid_id,
-- t1.FSECUNITID fsecunitid_id,
-- t1.FSECREALQTY fsecrealqty,
t0.FCANCELSTATUS fcancelstatus,
t0.FBILLTYPE fbilltype,
t0.FSTOCKORGID fstockorgid,
t0.FFORMID fformid,
t1.FID fid2,
t1.FENTRYID t1_fentryid,
t1.FSeq t1_fseq,
ROW_NUMBER() OVER(
ORDER BY
t0.FCREATEDATE DESC,
t0.FBILLNO DESC,
t1.FSeq ASC
) fidentityid
FROM
T_PRD_INSTOCK t0
LEFT OUTER JOIN T_PRD_INSTOCKENTRY t1 ON t0.FID = t1.FID
WHERE
(
(
-- t0.FBILLNO = 'SCRK00001874' and
t0.FENTRUSTINSTOCKID = 0
AND t0.FSTOCKORGID = 100206
)
AND (
t0.FSTOCKORGID IN (100206, 0)
AND t0.FFORMID = 'PRD_INSTOCK'
)
)
) tlist
WHERE
(
(fidentityid >= 1)
AND (fidentityid <= 2000)
)
//非已审核单据
SELECT
*
FROM
(
SELECT
t0.FID fid,
t0.FDATE 单据日期,
t0.FBILLNO fbillno,
t0.FDOCUMENTSTATUS 单据状态,
t0.FSTOCKORGID fstockorgid_id,
t0.FPRDORGID fprdorgid_id,
t1.FMATERIALID fmaterialid_id,
t1.FPRODUCTTYPE fproducttype,
t1.FUNITID funitid_id,
t1.FMUSTQTY fmustqty,
t1.FREALQTY frealqty,
t1.FSTOCKID fstockid_id,
t1.FSECUNITID fsecunitid_id,
t1.FSECREALQTY fsecrealqty,
t0.F_HXZB_YDDJLX f_hxzb_yddjlx_id,
t0.FCANCELSTATUS fcancelstatus,
t0.FBILLTYPE fbilltype,
t0.FSTOCKORGID fstockorgid,
t0.FFORMID fformid,
t1.FID fid2,
t1.FENTRYID t1_fentryid,
t1.FSeq t1_fseq,
ROW_NUMBER() OVER(
ORDER BY
t0.FCREATEDATE DESC,
t0.FBILLNO DESC,
t1.FSeq ASC
) fidentityid
FROM
T_PRD_INSTOCK t0
LEFT OUTER JOIN T_PRD_INSTOCKENTRY t1 ON t0.FID = t1.FID
WHERE
(
(
t0.FBILLNO = 'SCRK00000322' and
t0.FENTRUSTINSTOCKID = 0
AND t0.FSTOCKORGID = 100206
)
AND (
t0.FSTOCKORGID IN (100206, 0)
AND t0.FFORMID = 'PRD_INSTOCK'
)
)
) tlist
WHERE
(
(fidentityid >= 1)
AND (fidentityid <= 200)
)
-- 枚举值：'C' = 已审核；'A' = 创建；'B' = 审核中;'D' = 重新审核
--查询生产入库单有多少物料未审核
select FDATE,FDOCUMENTSTATUS
from T_PRD_INSTOCK
where FBILLNO = 'SCRK00001854'
select count(1)
from T_PRD_INSTOCK
where FDOCUMENTSTATUS ='D'
--修改日期
update T_PRD_INSTOCK set FDATE = dateadd (month,1,FDATE) where FBILLNO = 'SCRK00001852'
update T_PRD_INSTOCK set FDATE = dateadd (month,1,FDATE) where FDOCUMENTSTATUS ='D'
```
## 生产入库单上查
```
SELECT
t1_INSTOCK.FBILLNO 入库单单据编号 ,
t0.FBILLNO 单据编号 ,
t0.FBILLTYPE 单据类型ID,
t1_INSTOCKENTRY.FSRCENTRYID 入库单源单内码ID,
t1.FENTRYID 生产订单内码ID,
t0.FID, t0.FFORMID 交易类型码
FROM
T_PRD_MO t0
LEFT OUTER JOIN T_PRD_MOENTRY t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_PRD_MOENTRY_A t1_A ON t1.FENTRYID = t1_A.FENTRYID
LEFT OUTER JOIN T_PRD_MOENTRY_Q t1_Q ON t1.FENTRYID = t1_Q.FENTRYID
LEFT OUTER JOIN T_PRD_INSTOCKENTRY t1_INSTOCKENTRY ON t1.FENTRYID = t1_INSTOCKENTRY.FSRCENTRYID
LEFT OUTER JOIN T_PRD_INSTOCK t1_INSTOCK ON t1_INSTOCKENTRY.FID = t1_INSTOCK.FID
WHERE
(
-- t0.FBILLNO = 'MO3041303'
-- t0.FBILLNO = 'MO3092203'
t1.FENTRYID in (
SELECT
tlist.FSRCENTRYID_FENTRYID
FROM
(
SELECT
t0.FID fid,
t0.FBILLNO fbillno,
t0.FDOCUMENTSTATUS fdocumentstatus,
t0.FSTOCKORGID fstockorgid_id,
t0.FPRDORGID fprdorgid_id,
t1.FENTRYID 行内码ID,
t1.FSRCINTERID 源单内码 ,
t1.FSRCBILLNO 源单编号 ,
t1.FSRCENTRYID FSRCENTRYID_FENTRYID,
t1.FMATERIALID fmaterialid_id,
t1.FPRODUCTTYPE fproducttype,
t1.FUNITID funitid_id,
t1.FMUSTQTY fmustqty,
t1.FREALQTY frealqty,
t1.FSTOCKID fstockid_id,
t1.FSECUNITID fsecunitid_id,
t1.FSECREALQTY fsecrealqty,
t0.F_HXZB_YDDJLX f_hxzb_yddjlx_id,
t0.FCANCELSTATUS fcancelstatus,
t0.FBILLTYPE fbilltype,
t0.FSTOCKORGID fstockorgid,
t0.FFORMID fformid,
t1.FID fid2,
t1.FENTRYID t1_fentryid,
t1.FSeq t1_fseq,
ROW_NUMBER() OVER(
ORDER BY
t0.FCREATEDATE DESC,
t0.FBILLNO DESC,
t1.FSeq ASC
) fidentityid
FROM
T_PRD_INSTOCK t0
LEFT OUTER JOIN T_PRD_INSTOCKENTRY t1 ON t0.FID = t1.FID
WHERE
(
(
t0.FDATE >= '2023-11-01 00:00:00' and t0.FDATE <= '2023-11-28 00:00:00' and
t0.FENTRUSTINSTOCKID = 0
AND t0.FSTOCKORGID = 100206
)
AND (
t0.FSTOCKORGID IN (100206, 0)
AND t0.FFORMID = 'PRD_INSTOCK'
)
)
) tlist
WHERE
(
(fidentityid >= 1)
AND (fidentityid <= 2000)
)
)
AND (
t0.FPRDORGID IN (1, 100206, 170187, 0)
AND t0.FFORMID = 'PRD_MO'
)
)
SELECT * FROM T_PRD_MO t0 LEFT OUTER JOIN T_PRD_MOENTRY t1 ON t0.FID = t1.FID LEFT OUTER JOIN T_PRD_MOENTRY_A t1_A ON t1.FENTRYID = t1_A.FENTRYID LEFT OUTER JOIN T_PRD_MOENTRY_Q t1_Q ON t1.FENTRYID = t1_Q.FENTRYID WHERE (t1.FENTRYID = 102858 AND (t0.FPRDORGID IN (1, 100206, 170187, 0) AND t0.FFORMID = 'PRD_MO'))
```
## 物料列表查询
```
SELECT *
FROM
(SELECT t0.FUSEORGID fuseorgid_id,
t0.FNUMBER fnumber,
t0.FCNUMBER fcnumber,
t0_L.FNAME fname,
t0_L.FSPECIFICATION fspecification,
t0.FDOCUMENTSTATUS fdocumentstatus,
t0.FFORBIDSTATUS fforbidstatus,
t4.FERPCLSID ferpclsid,
t4.FBASEUNITID fbaseunitid_id,
t0.FREFSTATUS frefstatus,
t0.FUSEORGID fuseorgid,
t0.FMATERIALID fmaterialid,
t0.FMASTERID fmasterid,
ROW_NUMBER() OVER(
ORDER BY t0.FNUMBER ASC) fidentityid
FROM T_BD_MATERIAL t0 LEFT OUTER
JOIN t_BD_MaterialBase t4
ON t0.FMATERIALID = t4.FMATERIALID LEFT OUTER
JOIN T_BD_MATERIAL_L t0_L
ON (t0.FMATERIALID = t0_L.FMATERIALID
AND t0_L.FLocaleId = 2052)
WHERE ((t0.FFORBIDSTATUS = 'A'
AND t0.FUSEORGID = 100206)
AND t0.FUSEORGID IN (100206, 0))) tlist
WHERE ((fidentityid >= 1)
AND (fidentityid <= 200))
```
## 用户员工关系
```
SELECT T10.fname 组织名称,U.FUSERID AS 用户内码, U.FNAME 用户名,T1.FPERSONID AS 人员内码,T2.FNAME AS 人员姓名,
T8.fid 员工内码,T9.FNUMBER 员工工号编码,T8.fname 员工名称,T3.FSTAFFNUMBER 员工任岗编码,T4.FNUMBER 任岗部门编码, T4.FDEPTID,
T5.FNAME AS 任岗部门名称,T6.FNUMBER 任岗岗位编码,T7.FNAME AS 任岗岗位名称 FROM T_SEC_USER U --用户表
INNER JOIN T_BD_PERSON T1 ON U.FLINKOBJECT = T1.FPERSONID --人员信息表
LEFT JOIN T_BD_PERSON_L T2 ON T1.FPERSONID=T2.FPERSONID AND T2.FLOCALEID=2052
INNER JOIN T_BD_STAFF T3 ON T1.FPERSONID=T3.FPERSONID --员工表
LEFT JOIN T_BD_DEPARTMENT T4 ON T3.FDEPTID=T4.FDEPTID --部门表
LEFT JOIN T_BD_DEPARTMENT_L T5 ON T4.FDEPTID=T5.FDEPTID AND T5.FLOCALEID=2052 --部门实体信息表
LEFT JOIN T_ORG_POST T6 ON T3.FPOSTID=T6.FPOSTID --岗位表
LEFT JOIN T_ORG_POST_L T7 ON T6.FPOSTID=T7.FPOSTID AND T7.FLOCALEID=2052
left join T_HR_EMPINFO_L T8 on T8.fid=T3.FEMPINFOID and T8.FLOCALEID=2052
left join T_HR_EMPINFO T9 on T9.FID=T8.FID
left join T_ORG_ORGANIZATIONS_L T10 on T10.FORGID=T9.FUSEORGID and T10.FLOCALEID=2052
where U.fforbidstatus = 'A'
ORDER BY T5.FNAME
SELECT T10.fname 组织名称,U.FUSERID AS 用户内码, U.FNAME 用户名,T1.FPERSONID AS 人员内码,T2.FNAME AS 人员姓名,
T8.fid 员工内码,T9.FNUMBER 员工编码,T8.fname 员工名称,T3.FSTAFFNUMBER 员工任岗编码,T4.FNUMBER 任岗部门编码, T4.FDEPTID,
T5.FNAME AS 任岗部门名称,T6.FNUMBER 任岗岗位编码,T7.FNAME AS 任岗岗位名称 FROM
T_SEC_USER U --用户表
INNER JOIN T_BD_PERSON T1 ON U.FLINKOBJECT = T1.FPERSONID --人员信息表
LEFT JOIN T_BD_PERSON_L T2 ON T1.FPERSONID=T2.FPERSONID AND T2.FLOCALEID=2052
INNER JOIN T_BD_STAFF T3 ON T1.FPERSONID=T3.FPERSONID --员工表
LEFT JOIN T_BD_DEPARTMENT T4 ON T3.FDEPTID=T4.FDEPTID --部门表
LEFT JOIN T_BD_DEPARTMENT_L T5 ON T4.FDEPTID=T5.FDEPTID AND T5.FLOCALEID=2052 --部门实体信息表
LEFT JOIN T_ORG_POST T6 ON T3.FPOSTID=T6.FPOSTID --岗位表
LEFT JOIN T_ORG_POST_L T7 ON T6.FPOSTID=T7.FPOSTID AND T7.FLOCALEID=2052
left join T_HR_EMPINFO_L T8 on T8.fid=T3.FEMPINFOID and T8.FLOCALEID=2052
left join T_HR_EMPINFO T9 on T9.FID=T8.FID
left join T_ORG_ORGANIZATIONS_L T10 on T10.FORGID=T9.FUSEORGID and T10.FLOCALEID=2052
where U.fforbidstatus = 'A'
ORDER BY T5.FNAME
```
## 查看临时表数量，占用存储空间情况
```
--临时表问题判断
--另外，我们可通过执行下面3个查询数量，根据查询数量的情况，来判断临时表方面的问题：
select count(*) from T_BAS_TEMPORARYTABLENAME where FPROCESSTYPE=1 or FCREATEDATE<DATEADD(hh,-24, GETDATE())
select count(*) from T_BAS_TEMPORARYTABLENAME
select count(*) from sys.tables t where name like 'tmp%' and len(name)=30
select count(*) from sys.tables t where name like 'gl%' and len(name)=29
-- 查看系统所有临时表占用的总空间
select cast(sum(a.total_pages)*8/1024 as varchar)+' MB' total
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
join sys.tables it on p.object_id = it.object_id
where it.name like 'TMP%'
-- 查询系统中每个临时表占用的空间大小统计
select it.name, cast(sum(a.total_pages)*8 as varchar)+'KB' total
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
join sys.tables it on p.object_id = it.object_id
where it.name like 'tm%'
group by it.name
order by sum(a.total_pages)*8 desc
--如果语句1的记录很多（正常值一般1000内），那么后台删除临时表的作业肯定有一段时间没有运行；
--如果语句2的记录很多（正常值一般10000内），那么说明有临时表残留；
--如果语句3的记录很多（正常值一般10000内），并且如果语句3远大于语句2的记录数，说明很多生成的临时表没有记录到登记表中，属于异常情况，一般是二开或其他原因导致的临时表问题，如果这个记录数一直增长，那么需要手工执行删除临时表的语句。
--SQLserver手工删临时表的方法（可在业务期间运行，推荐使用该方法删除临时表）：
--第一步：删除登记表中的可删除的临时表登记记录
delete from T_BAS_TEMPORARYTABLENAME where FPROCESSTYPE=1 or FCREATEDATE<DATEADD(hh,-24, GETDATE())
--第二步：删除临时表，每次删除50个临时表释放一次资源，可在业务运行期间执行语句
if object_id('temptb','table')>0 drop table temptb;
declare @sql varchar(max)
declare @icount int
declare @I int
set @sql='drop table '
set @i=1
select name,IDENTITY(int,1,1) id into temptb from sys.tables t where name like 'tmp%' and len(name)=30 and name not like 'tmp[_]%'
and not exists(select 1 from T_BAS_TEMPORARYTABLENAME where FTABLENAME=t.name) and create_date<=DATEADD(hh,-24, GETDATE())
select @icount=@@ROWCOUNT
while @i<@icount
begin
select @sql=@sql+name+',' from temptb where id between @i and @i+49
if @@ROWCOUNT>0
set @sql=substring(@sql,1,len(@sql)-1)+';'
set @i=@i+50
exec(@sql)
set @sql='drop table '
end
if object_id('temptb','table')>0 drop table temptb;
```
单据在数据库里有对应表，但是账表在数据库里没有对应表，是一套sql处理逻辑，怎么抓取到这个sql逻辑呀
即时库存那个语句是有问题的，主单位库存不是是查出来的，是算出来的
感谢分享
不错感谢
很好
点赞
