Kingdee-SQL语句汇总

Matsuoka SQL

一、文档：

管理中心：

http://localhost:8000/cmc.aspx
默认仅允许在管理中心服务器本机进行访问使用

二、SQL语句

-- 查询业务关联数据表

SELECT a.FID,b.FNAME,a.Item
FROM (SELECT FKERNELXML.query('//TableName')'Item', * FROM T_META_OBJECTTYPE) a
JOIN T_META_OBJECTTYPE_L b ON a.FID = b.FID AND b.FLOCALEID = 2052
WHERE b.FNAME LIKE '%销售订单%'

SELECT FID,
(SELECT FNAME FROM T_META_OBJECTTYPE_L WHERE FID = a.FID AND FLOCALEID = 2052) FNAME,
Item
FROM (SELECT FKERNELXML.query('//TableName')'Item', * FROM T_META_OBJECTTYPE) a
WHERE FID = 'SAL_SaleOrder'

-- 表单查询

SELECT b.FNAME,a.* 
FROM T_META_OBJECTTYPE a
LEFT JOIN T_META_OBJECTTYPE_L b ON a.FID=b.FID 
AND b.FLOCALEID=2052
WHERE a.FMODELTYPEID=100 
AND a.FDEVTYPE IN (0,1) 
AND b.FNAME ='采购订单'

-- 获取销售出库单的单据视图内码

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','6bd6de25-2d2f-4c9e-aa95-166db3e9f198')

-- 功能查询

SELECT L.FNAME,H.FNAME,B.FNAME,E.FNAME 
FROM T_META_CONSOLESUBFUNC A
JOIN T_META_CONSOLESUBFUNC_l B ON A.FSUBFUNCID = B.FSUBFUNCID 
AND B.FLOCALEID = 2052
JOIN(SELECT C.FSUBFUNCID, C.FOBJECTID, D.FNAME 
FROM T_META_CONSOLEDETAIL C 
JOIN T_META_CONSOLEDETAIL_l D ON C.FDETAILFUNCID = D.FDETAILFUNCID 
AND D.FLOCALEID = 2052)E ON A.FSUBFUNCID = E.FSUBFUNCID
JOIN(SELECT F.FID, G.FNAME, F.FTOPCLASSID 
FROM T_META_SUBSYSTEM F 
JOIN T_META_SUBSYSTEM_l G ON F.FID = G.FID 
AND G.FLOCALEID = 2052) H ON A.FSUBSYSTEMID = H.FID
JOIN(SELECT J.FTOPCLASSID, K.FNAME 
FROM T_META_TOPCLASS J
JOIN T_META_TOPCLASS_L K ON J.FTOPCLASSID = K.FTOPCLASSID 
AND K.FLOCALEID = 2052) L ON H.FTOPCLASSID = L.FTOPCLASSID
ORDER BY L.FNAME,H.FNAME,B.FNAME,E.FNAME

-- 查询部门信息

T_BD_DEPARTMENT 部门表
T_BD_DEPARTMENT_L 部门多语言表
SELECT a.FDEPTID,b.FNAME,b.FFULLNAME
FROM T_BD_DEPARTMENT a
JOIN T_BD_DEPARTMENT_L b ON a.FDEPTID = b.FDEPTID
WHERE b.FNAME = '信息科'

-- 按部门查询人员信息SQL

SELECT a.FSTAFFID,a.FMASTERID,a.FNUMBER,a.FPERSONID,a.FPOSTID,a.FDEPTID, b.FNAME,c.FNAME
FROM T_BD_STAFF a
JOIN T_BD_STAFF_L b ON a.FSTAFFID = b.FSTAFFID
JOIN T_BD_DEPARTMENT_L c ON a.FDEPTID = c.FDEPTID
WHERE a.FDEPTID = '180641'

-- 销售订单

SELECT a.FID, b.FCREATEDATE 日期,b.FBILLNO 单据编号,c.FNAME 物料名称,c.FSPECIFICATION 规格型号,d.FNAME 单位,
a.FQTY 数量,e.FNAME 销售部门,g.FNAME 销售员,h.FNAME 客户,i.FNAME 单据类型,i.FLOCALEID
FROM T_SAL_ORDERENTRY a
LEFT JOIN T_SAL_ORDER b ON a.FID = b.FID
LEFT JOIN T_BD_MATERIAL_L c ON a.FMATERIALID = c.FMATERIALID
LEFT JOIN T_BD_UNIT_L d ON a.FUNITID = d.FUNITID
LEFT JOIN T_BD_DEPARTMENT_L e ON b.FSALEDEPTID = e.FDEPTID
LEFT JOIN T_BD_OPERATORENTRY f ON b.FSALERID = f.FENTRYID
LEFT JOIN T_BD_STAFF_L g ON f.FSTAFFID = g.FSTAFFID
LEFT JOIN T_BD_CUSTOMER_L h ON b.FCUSTID = h.FCUSTID
LEFT JOIN T_BAS_BILLTYPE_L i ON b.FBILLTYPEID = i.FBILLTYPEID
WHERE b.FBILLNO = 'XSDD000152'
AND i.FLOCALEID = '2052'

-- 销售报价单

SELECT a.FID, b.FBILLNO 单据编号,c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号,
CONVERT(DECIMAL(18,0),a.FQTY) 数量,e.FNAME 单位,CONVERT(DECIMAL(18,2),f.FPRICE) 单价,
CONVERT(DECIMAL(18,2),f.FTAXNETPRICE) 含税单价, CONVERT(DECIMAL(18,0),f.FTAXRATE) "税率%", 
CONVERT(DECIMAL(18,2),f.FAMOUNT) 金额,CONVERT(DECIMAL(18,2),f.FALLAMOUNT) 含税金额,
h.FNAME 客户,g.FNAME 单据类型
FROM T_SAL_QUOTATIONENTRY a
LEFT JOIN T_SAL_QUOTATION b ON a.FID = b.FID
LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID
LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID
LEFT JOIN T_SAL_QUOTATIONENTRY_F f ON a.FID = f.FID
LEFT JOIN T_BAS_BILLTYPE_L g ON b.FBILLTYPEID = g.FBILLTYPEID
LEFT JOIN T_BD_CUSTOMER_L h ON b.FCUSTID = h.FCUSTID
WHERE b.FBILLNO = 'XSBJD0003'

-- 销售价目表

SELECT a.FID ID, b.FNUMBER 编号,c.FNAME 名称,d.FNUMBER 物料编号,e.FNAME 物料名称,e.FSPECIFICATION 规格,f.FNAME 单位,
a.FPRICE 价格,a.FDOWNPRICE 最低限价, CONVERT(varchar(10),a.FEFFECTIVEDATE,126) 生效日期,CONVERT(varchar(10),a.FEXPRIYDATE,126) 失效日期
FROM T_SAL_PRICELISTENTRY a
LEFT JOIN T_SAL_PRICELIST b ON a.FID = b.FID
LEFT JOIN T_SAL_PRICELIST_L c ON b.FID = c.FID
LEFT JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L e ON d.FMATERIALID = e.FMATERIALID
LEFT JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID
WHERE b.FNUMBER = 'XSJMB0005'

-- 即时库存查询

SELECT a.FMATERIALID 物料内码, b.FNUMBER 物料编码, c.FNAME 物料名称, c.FSPECIFICATION 规格型号,
d.FNUMBER 仓库代码,e.FNAME 仓库名称,d.FADDRESS 仓库地址,CONVERT(DECIMAL(18,0),a.FBASEQTY) 数量,
h.FNUMBER 批号 ,g.FNAME 库存主单位, i.FNAME 库存状态,j.FNAME 库存组织
FROM T_STK_INVENTORY a
LEFT JOIN T_BD_MATERIAL b ON a.FMATERIALID = b.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L c ON b.FMATERIALID = c.FMATERIALID
LEFT JOIN T_BD_STOCK d ON a.FSTOCKID = d.FSTOCKID
LEFT JOIN T_BD_STOCK_L e ON d.FSTOCKID = e.FSTOCKID
LEFT JOIN T_BD_UNIT f ON a.FSTOCKUNITID = f.FUNITID
LEFT JOIN T_BD_UNIT_L g ON f.FUNITID = g.FUNITID
LEFT JOIN T_BD_LOTMASTER h ON b.FMASTERID = h.FMATERIALID
LEFT JOIN T_BD_STOCKSTATUS_L i ON a.FSTOCKSTATUSID = i.FSTOCKSTATUSID
LEFT JOIN T_ORG_ORGANIZATIONS_L j ON a.FSTOCKORGID = j.FORGID
WHERE b.FNUMBER IN ('2301004700','2518072700','2518066500','2518066300')
AND j.FLOCALEID = 2052

-- 采购申请单

SELECT a.FID,f.FNAME 申请人,d.FNAME 单据类型,b.FBILLNO 单据编号,b.FAPPLICATIONDATE 申请日期,e.FNUMBER 物料编码, c.FNAME 物料名称,
c.FSPECIFICATION 规格型号,a.FREQQTY 申请数量,a.FAPPROVEQTY 批准数量,a.FEVALUATEPRICE 单价,a.FTAXPRICE 含税单价,h.FNAME 单位,
a.FAMOUNT 金额,(a.FREQQTY*a.FTAXPRICE) as 含税金额 ,a.FARRIVALDATE 到货日期,g.FNAME 供应商,i.FSRCBILLNO 来源单号,i.FDEMANDBILLNO 需求单号
FROM T_PUR_REQENTRY a
LEFT JOIN T_PUR_REQUISITION b ON a.FID = b.FID
LEFT JOIN T_BD_MATERIAL_L c ON a.FMATERIALID = c.FMATERIALID
LEFT JOIN T_BAS_BILLTYPE_L d ON b.FBILLTYPEID = d.FBILLTYPEID
LEFT JOIN T_BD_MATERIAL e ON a.FMATERIALID = e.FMATERIALID
LEFT JOIN T_BD_STAFF_L f ON b.FAPPLICANTID = f.FSTAFFID
LEFT JOIN T_BD_Supplier_L g ON a.FSUGGESTSUPPLIERID = g.FSUPPLIERID
LEFT JOIN T_BD_UNIT_L h ON a.FUNITID = h.FUNITID
LEFT JOIN T_PUR_REQENTRY_R i ON a.FENTRYID = i.FENTRYID
LEFT JOIN T_BAS_BILLTYPE_L j ON d.FBILLTYPEID = i.FSRCBILLTYPEID
WHERE b.FBILLNO = 'CGSQ000062'

-- 采购订单

SELECT a.FID,c.FBILLNO 单据编号,d.FNAME 单据类型,c.FDATE 采购日期,e.FNAME 供应商,F.FNUMBER 物料编码,
g.FNAME 物料名称,g.FSPECIFICATION 规格型号,h.FNAME 单位,a.FQTY 采购数量,i.FSRCBILLNO 来源单号
FROM T_PUR_POORDERENTRY a
LEFT JOIN T_PUR_POORDERENTRY_L b ON a.FID = b.FPKID
LEFT JOIN T_PUR_POORDER c ON a.FID = c.FID
LEFT JOIN T_BAS_BILLTYPE_L d ON c.FBILLTYPEID = d.FBILLTYPEID
LEFT JOIN T_BD_Supplier_L e ON c.FSUPPLIERID = e.FSUPPLIERID
LEFT JOIN T_BD_MATERIAL f ON a.FMATERIALID = f.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L g ON a.FMATERIALID = g.FMATERIALID
LEFT JOIN T_BD_UNIT_L h ON a.FUNITID = h.FUNITID
LEFT JOIN T_PUR_POORDERENTRY_R i ON a.FENTRYID = i.FENTRYID
WHERE c.FBILLNO = 'CGDD000177'

-- 直接调拨单

SELECT g.FSTOCKID,a.FID, b.FBILLNO 单据编号,b.FCREATEDATE 日期,b.FTRANSFERDIRECT 调拨方向, c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号,e.FNAME 单位, a.FQTY 调拨数量, 
a.FSRCSTOCKID 仓库ID,f.FNUMBER 仓库编码,g.FNAME 调出仓库,i.FNUMBER 调出仓位,h.FNAME 调入仓库,j.FNAME 单据类型,k.FSRCBILLNO 来源单号,k.FORDERNO 订单编号
FROM T_STK_STKTRANSFERINENTRY a
LEFT JOIN T_STK_STKTRANSFERIN b ON a.FID = b.FID
LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID
LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID
LEFT JOIN T_BD_STOCK f ON a.FSRCSTOCKID = f.FSTOCKID
LEFT JOIN T_BD_STOCK_L g ON f.FSTOCKID = g.FSTOCKID
LEFT JOIN T_BD_STOCK_L h ON a.FDESTSTOCKID = h.FSTOCKID
LEFT JOIN T_BAS_FLEXVALUESENTRY i ON a.FID = i.FID
LEFT JOIN T_BAS_BILLTYPE_L j ON b.FBILLTYPEID = j.FBILLTYPEID
LEFT JOIN T_STK_STKTRANSFERINENTRY_R k ON a.FENTRYID = k.FENTRYID
WHERE b.FBILLNO = 'ZJDB000057'

-- 调拨申请单

SELECT a.FID, b.FBILLNO 单据编号,c.FNUMBER 物料编码, d.FNAME 物料名称,d.FSPECIFICATION 规格型号,
e.FNAME 单位,CONVERT(DECIMAL(18,0),a.FQTY) 数量, f.FNAME 调出仓库,l.FNAME 调出仓位1,m.FNAME 调出仓位2, g.FNAME 调入仓库,j.FNAME 调入仓位,h.FNAME 单据类型
FROM T_STK_STKTRANSFERAPPENTRY a
LEFT JOIN T_STK_STKTRANSFERAPP b ON a.FID = b.FID
LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID
LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID
LEFT JOIN T_BD_STOCK_L f ON a.FSTOCKID = f.FSTOCKID
LEFT JOIN T_BD_STOCK_L g ON a.FSTOCKINID = g.FSTOCKID
LEFT JOIN T_BAS_BILLTYPE_L h ON b.FBILLTYPEID = h.FBILLTYPEID
LEFT JOIN T_BAS_FLEXVALUESDETAIL i ON a.FSTOCKLOCINID = i.FID
LEFT JOIN T_BAS_FLEXVALUESENTRY_L j ON i.FF100001 = j.FPKID
LEFT JOIN T_BAS_FLEXVALUESDETAIL k ON a.FSTOCKLOCID = k.FID
LEFT JOIN T_BAS_FLEXVALUESENTRY_L l ON k.FF100046 = l.FPKID
LEFT JOIN T_BAS_FLEXVALUESENTRY_L m ON k.FF100047 = m.FPKID
WHERE b.FBILLNO = 'DBSQ000018'

-- 查询角色

SELECT a.FROLEID ID, a.FNUMBER 编码,b.FNAME 名称 
FROM T_SEC_ROLE a
LEFT JOIN T_SEC_ROLE_L b ON a.FROLEID = b.FROLEID

-- 查询用户&用户组

SELECT a.FPRIMARYGROUP ID,a.FNAME 用户名,b.FNAME 用户组,a.FPHONE 手机号, a.FAPPGROUP 许可分组,c.FDESCRIPTION 描述
FROM T_SEC_USER a
JOIN T_SEC_USERGROUP_L b ON a.FPRIMARYGROUP = b.FID
JOIN T_SEC_USER_L c ON a.FUSERID = c.FUSERID
where b.FNAME LIKE '%研发%'

-- 用户同步日志

SELECT * 
FROM T_SEC_REGUSERSYNCLOG
ORDER BY FDATETIME DESC

-- 收料通知单采购收料

SELECT b.FID,b.FBILLNO 单据编号,CONVERT(varchar(10),FDATE,126)收料日期,c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号,
e.FNAME 单位,CONVERT(DECIMAL(18,0),a.FACTLANDQTY) 实到数量,CONVERT(DECIMAL(18,0),a.FACTRECEIVEQTY) 实收数量,a.FSRCBILLNO 来源单号,
F.FNAME 仓库,g.FNAME 供应商
FROM T_PUR_RECEIVEENTRY a
LEFT JOIN T_PUR_RECEIVE b ON a.FID = b.FID
LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID
LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID
LEFT JOIN T_BD_STOCK_L f ON a.FSTOCKID = f.FSTOCKID
LEFT JOIN T_BD_SUPPLIER_L g ON b.FSUPPLIERID = g.FSUPPLIERID
WHERE b.FBILLNO = '1201-2206290006'

-- 供应商查询

SELECT a.FSUPPLIERID ID,b.FNAME 供应商名称,b.FSHORTNAME 简称,c.FADDRESS 地址,d.FTaxRegisterCode 税务登记号,e.FContact 联系人,e.FTEL 电话,e.FMOBILE 手机
FROM T_BD_SUPPLIER a
LEFT JOIN T_BD_SUPPLIER_L b ON a.FSUPPLIERID = b.FSUPPLIERID
LEFT JOIN T_BD_SUPPLIERBASE c ON a.FSUPPLIERID = c.FSUPPLIERID
LEFT JOIN t_BD_SupplierFinance d ON a.FSUPPLIERID = d.FSUPPLIERID
LEFT JOIN t_BD_SupplierContact e ON a.FSUPPLIERID = e.FSUPPLIERID

-- 采购价目表

SELECT a.FID ID, b.FNUMBER 编码,c.FNAME 名称,g.FNAME 供应商,d.FNUMBER 物料编码,e.FNAME 物料名称,e.FSPECIFICATION 规格,f.FNAME 单位,
a.FPRICE 单价,a.FTAXPRICE 含税单价,CONVERT(DECIMAL(18,0),a.FTAXRATE) "税率%",
CONVERT(varchar(10),a.FEFFECTIVEDATE,126) 生效日期,CONVERT(varchar(10),a.FEXPIRYDATE,126) 失效日期
FROM T_PUR_PRICELISTENTRY a
LEFT JOIN T_PUR_PRICELIST b ON a.FID = b.FID
LEFT JOIN T_PUR_PRICELIST_L c ON b.FID = c.FID
LEFT JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L e ON a.FMATERIALID = e.FMATERIALID
LEFT JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID
LEFT JOIN T_BD_SUPPLIER_L g ON b.FSUPPLIERID = g.FSUPPLIERID
WHERE b.FNUMBER = 'CGJM000166'

-- 生产订单

SELECT a.FID,b.FBILLNO 单据编号,CONVERT(varchar(10),b.FDATE,126) 单据日期,f.FNAME 单据类型,
c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号,e.FNAME 单位,FQTY 数量,
g.FNAME 生产车间,h.FSERIALNO 序列号,a.FPLANSTARTDATE 计划开工时间,a.FPLANFINISHDATE 计划完工时间,
a.FMTONO 计划跟踪号 
FROM T_PRD_MOENTRY a
LEFT JOIN T_PRD_MO b ON a.FID = b.FID
LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID
LEFT JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID
LEFT JOIN T_BAS_BILLTYPE_L f ON b.FBILLTYPE = f.FBILLTYPEID
LEFT JOIN T_BD_DEPARTMENT_L g ON a.FWORKSHOPID = g.FDEPTID
LEFT JOIN T_PRD_MOSNDETAIL h ON a.FENTRYID = h.FENTRYID --序列号
WHERE b.FBILLNO = '3101-2206300010'
AND f.FLOCALEID = '2052'

-- 单据类型查询

SELECT a.FBILLTYPEID,a.FNUMBER 单据编码, b.FNAME 单据名称 
FROM T_BAS_BILLTYPE a
LEFT JOIN T_BAS_BILLTYPE_L b ON a.FBILLTYPEID = b.FBILLTYPEID
WHERE b.FLOCALEID = '2052'
AND b.FNAME LIKE '%配件%'

-- 用户组织角色查询

SELECT distinct a.FUSERID 用户id,a.FNAME 用户名称,d.FNUMBER 组织编码,
e.FNAME 组织名称,f.FNUMBER 角色编码,g.FNAME 角色名称
FROM T_SEC_USER a
INNER JOIN T_SEC_USERORG b ON b.FUSERID= a.FUSERID
LEFT JOIN T_SEC_USERROLEMAP c ON c.FENTITYID = b.FENTITYID
LEFT JOIN T_ORG_ORGANIZATIONS d ON d.FORGID=b.FORGID
LEFT JOIN T_ORG_ORGANIZATIONS_L e ON d.FORGID=e.FORGID AND e.FLOCALEID=2052
LEFT JOIN T_SEC_ROLE f ON f.FROLEID=c.FROLEID
LEFT JOIN T_SEC_ROLE_L g ON f.FROLEID=g.FROLEID
WHERE a.FNAME='用户名'

-- 员工角色、岗位查询

SELECT k.fname 组织名称,a.FUSERID AS 用户内码, a.FNAME 用户名,b.FPERSONID AS 人员内码,
c.FNAME AS 人员姓名,i.FID 员工内码,j.FNUMBER 员工编码,i.FNAME 员工名称,
d.FSTAFFNUMBER 员工任岗编码,e.FNUMBER 任岗部门编码,f.FNAME AS 任岗部门名称,g.FNUMBER 任岗岗位编码,h.FNAME AS 任岗岗位名称 
FROM T_SEC_USER a
INNER JOIN T_BD_PERSON b ON a.FLINKOBJECT = b.FPERSONID
LEFT JOIN T_BD_PERSON_L c ON b.FPERSONID=c.FPERSONID AND c.FLOCALEID=2052
INNER JOIN T_BD_STAFF d ON b.FPERSONID=d.FPERSONID
LEFT JOIN T_BD_DEPARTMENT e ON d.FDEPTID=e.FDEPTID
LEFT JOIN T_BD_DEPARTMENT_L f ON d.FDEPTID=f.FDEPTID AND f.FLOCALEID=2052
LEFT JOIN T_ORG_POST g ON d.FPOSTID=g.FPOSTID
LEFT JOIN T_ORG_POST_L h ON g.FPOSTID=h.FPOSTID AND h.FLOCALEID=2052
LEFT JOIN T_HR_EMPINFO_L i ON i.FID=d.FEMPINFOID AND f.FLOCALEID=2052
LEFT JOIN T_HR_EMPINFO j ON j.FID=i.FID
LEFT JOIN T_ORG_ORGANIZATIONS_L k ON k.forgid=j.FUSEORGID AND k.FLOCALEID=2052
WHERE a.FNAME='用户名'

-- 计划订单主产品

SELECT a.FID, a.FBILLNO 单据号,d.FNAME 单据类型,a.FMATERIALID 物料代码,b.FNUMBER 物料编码,c.FNAME 物料名称,c.FSPECIFICATION 规格型号,
e.FNAME 单位,g.FNAME 投放单据类型, FFIRMQTY 确认订单量,a.FCOMPUTERNO 运算编号,h.FSALEORDERNO 来源单号,h.FSALEORDERENTRYSEQ 来源单据行号
FROM T_PLN_PLANORDER a
JOIN T_BD_MATERIAL b ON a.FMATERIALID = b.FMATERIALID
JOIN T_BD_MATERIAL_L c ON b.FMATERIALID = c.FMATERIALID
JOIN T_BAS_BILLTYPE_L d ON a.FBILLTYPEID = d.FBILLTYPEID
JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID
JOIN T_PLN_PLANORDER_A f ON a.FID = f.FID
JOIN T_BAS_BILLTYPE_L g ON f.FRELEASEBILLTYPE = g.FBILLTYPEID
JOIN T_PLN_PLANORDER_B h ON a.FID = h.FID
WHERE a.FBILLNO = 'MRP00066201-2'

-- 计划订单计划BOM

SELECT a.FID, a.FBILLNO 单据号,b.FMATERIALID 子项物料代码,c.FNUMBER 子项物料编码,
d.FNAME 物料名称,d.FSPECIFICATION 规格型号,e.FNAME 单位,
CONVERT(DECIMAL(18,0),b.FNUMERATOR) 分子,CONVERT(DECIMAL(18,0),b.FDENOMINATOR) 分母,
b.FSTDQTY 标准用量,b.FNEEDQTY 计划投料数量,b.FMUSTQTY 应发数量
FROM T_PLN_PLANORDER a
JOIN T_PLN_PLBOMENTRY b ON a.FID = b.FID
JOIN T_BD_MATERIAL c ON b.FMATERIALID = c.FMATERIALID
JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID
JOIN T_BD_UNIT_L e ON b.FUNITID = e.FUNITID
WHERE a.FBILLNO = 'MRP00066201-2'

-- 预测单

SELECT a.FID,b.FBILLNO 单据编号,c.FNAME 单据类型,b.FDATE 日期,d.FNUMBER 物料编码,e.FNAME 物料名称,
e.FSPECIFICATION 规格型号,f.FNAME 单位,a.FBASEQTY 数量,g.FNUMBER BOM版本,
CONVERT(VARCHAR(10),a.FSTARTDATE,126) 预测开始日期,CONVERT(VARCHAR(10),a.FENDDATE,126) 预测结束日期
FROM T_PLN_FORECASTENTRY a
JOIN T_PLN_FORECAST b ON a.FID = b.FID
JOIN T_BAS_BILLTYPE_L c ON b.FBILLTYPEID = c.FBILLTYPEID
JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID
JOIN T_BD_MATERIAL_L e ON d.FMATERIALID = e.FMATERIALID
JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID
LEFT JOIN T_ENG_BOM g ON a.FBOMID = g.FID
WHERE b.FBILLNO = 'FO00000088 '

-- 生产制造物料清单

SELECT a.FID,a.FNUMBER BOM版本,a.FMATERIALID 物料内码,b.FNUMBER 父项物料编码,c.FNAME 物料名称,
c.FSPECIFICATION 规格型号,d.FNAME 单位,e.FMATERIALID 子项物料内码,f.FNUMBER 子项物料编码,
g.FNAME 子项物料名称,g.FSPECIFICATION 子项规格型号,h.FNAME 单位,e.FBASENUMERATOR 用量分子,e.FBASEDENOMINATOR 用量分母
FROM T_ENG_BOM a
JOIN T_BD_MATERIAL b ON a.FMATERIALID = b.FMATERIALID
JOIN T_BD_MATERIAL_L c ON b.FMASTERID = c.FMATERIALID
JOIN T_BD_UNIT_L d ON a.FUNITID = d.FUNITID
JOIN T_ENG_BOMCHILD e ON a.FID = e.FID
JOIN T_BD_MATERIAL f ON e.FMATERIALID = f.FMATERIALID
JOIN T_BD_MATERIAL_L g ON f.FMATERIALID = g.FMATERIALID
JOIN T_BD_UNIT_L h ON e.FUNITID = h.FUNITID
WHERE a.FNUMBER = '2104171100_V1.0'

-- 生产用料清单

SELECT a.FID,b.FBILLNO 单据编号,c.FNUMBER 子项物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格型号,
e.FNAME 单位,a.FMOBILLNO 生产订单编号,a.FBASENUMERATOR 分子,a.FBASEDENOMINATOR 分母,
a.FMUSTQTY 应发数量,f.FPICKEDQTY 已领数量
FROM T_PRD_PPBOMENTRY a
JOIN T_PRD_PPBOM b ON a.FID = b.FID
JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID
JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID
JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID
JOIN T_PRD_PPBOMENTRY_Q f ON a.FENTRYID = f.FENTRYID
--JOIN T_PRD_MOENTRY e ON a.FMOID = e.FID
--JOIN T_BD_MATERIAL f ON e.FMATERIALID = f.FMATERIALID
WHERE b.FBILLNO = 'PPBOM00001350'

-- 上机操作日志

SELECT a.FUSERID,b.FNAME 用户名,a.FDATETIME 操作时间,c.FNAME 当前组织,
d.FNAME 子系统,e.FNAME 业务对象,a.FOPERATENAME 操作名称,a.FDESCRIPTION 描述,
a.FCOMPUTERNAME 客户端,a.FCLIENTIP 客户端IP,a.FGATEWAYIP 网关
FROM T_BAS_OPERATELOG a
LEFT JOIN T_SEC_USER b ON a.FUSERID = b.FUSERID
LEFT JOIN T_ORG_ORGANIZATIONS_L c ON a.FLOGONORGID = c.FORGID
LEFT JOIN T_META_SUBSYSTEM_L d ON a.FSUBSYSTEMID = d.FID
LEFT JOIN T_META_OBJECTTYPE_L e ON a.FOBJECTTYPEID = e.FID
LEFT JOIN T_MDL_FORMOPERATIONTYPE_L f ON a.FENVIRONMENT = f.FID
WHERE c.FLOCALEID = 2052
AND b.FNAME = '用户名'
AND a.FDATETIME BETWEEN '2022-07-01' AND '2022-07-06'
ORDER BY a.FDATETIME DESC

-- 上机操作日志备份表

SELECT a.FUSERID,b.FNAME 用户名,a.FDATETIME 操作时间,c.FNAME 当前组织,
d.FNAME 子系统,e.FNAME 业务对象,a.FOPERATENAME 操作名称,a.FDESCRIPTION 描述,
a.FCOMPUTERNAME 客户端,a.FCLIENTIP 客户端IP,a.FGATEWAYIP 网关
FROM T_BAS_OPERATELOGBK a
LEFT JOIN T_SEC_USER b ON a.FUSERID = b.FUSERID
LEFT JOIN T_ORG_ORGANIZATIONS_L c ON a.FLOGONORGID = c.FORGID
LEFT JOIN T_META_SUBSYSTEM_L d ON a.FSUBSYSTEMID = d.FID
LEFT JOIN T_META_OBJECTTYPE_L e ON a.FOBJECTTYPEID = e.FID
LEFT JOIN T_MDL_FORMOPERATIONTYPE_L f ON a.FENVIRONMENT = f.FID
WHERE c.FLOCALEID = 2052
--AND b.FNAME = '用户名'
AND a.FDATETIME BETWEEN '2022-06-20' AND '2022-07-01'
ORDER BY a.FDATETIME DESC

-- 仓库信息查询

SELECT a.FSTOCKID,a.FNUMBER 仓库代码,b.FNAME 仓库名称,a.FADDRESS 地址
FROM T_BD_STOCK a
JOIN T_BD_STOCK_L b ON a.FSTOCKID = b.FSTOCKID
WHERE b.FNAME LIKE '%成品%'

-- 供应商对账单

SELECT b.FID,b.FBILLNO 单据号,CONVERT(VARCHAR(10),b.FDATE,126) 单据日期,c.FNAME 供应商,d.FNUMBER 物料编码,
e.FNAME 名称,e.FSPECIFICATION 规格,f.FNAME 采购单位,CONVERT(DECIMAL(18,2),a.FQTY) 合同数量,
CONVERT(VARCHAR(10),a.FDELIVERYDATE,126) 交货日期,a.FGIVEAWAY "是否赠品:0否 1是",g.FPRICE 单价,
g.FAMOUNT 金额,CONVERT(DECIMAL(18,0),g.FTAXRATE) "税率%",g.FTAXAMOUNT 税额,g.FTAXPRICE 含税单价,
g.FALLAMOUNT 含税金额,h.FSRCBILLNO 来源单号,h.FSRCBILLTYPEID 来源单据类型
FROM T_PUR_CONTRACTENTRY a -- 采购合同明细表
JOIN T_PUR_CONTRACT b ON a.FID = b.FID --采购合同表
JOIN T_BD_SUPPLIER_L c ON b.FSUPPLIERID = c.FSUPPLIERID --供应商
JOIN T_BD_MATERIAL d ON a.FMATERIALID = d.FMATERIALID --物料
JOIN T_BD_MATERIAL_L e ON d.FMATERIALID = e.FMATERIALID 
JOIN T_BD_UNIT_L f ON a.FUNITID = f.FUNITID --单位
JOIN T_PUR_CONTRACTENTRY_F g ON a.FENTRYID = g.FENTRYID
JOIN T_PUR_CONTRACTENTRY_R h ON a.FENTRYID = h.FENTRYID
WHERE b.FBILLNO = 'CGHT000009'

-- 获取当前数据库最大的十张表

SELECT TOP 10 NAME 表名,sum(RowCnt) 表行数,sum(ReservedKb) "表大小(Kb)",
sum(TableUsedKb) "数据大小(Kb)",sum(UsedKb-TableUsedKb) "索引大小(Kb)",sum(TMPTABLESNum) 临时表NUM
FROM(SELECT OBJECT_ID,SCHEMA_ID,
CASE WHEN NAME LIKE 'TMP%' THEN 'TMPTABLES' ELSE NAME END NAME,
CASE WHEN NAME LIKE 'TMP%' THEN 1 ELSE 0 END TMPTABLESNum,
(SELECT MAX(row_count) FROM sys.dm_db_partition_stats p WITH(nolock) WHERE p.object_id=t.object_id and p.index_id < 2) AS RowCnt,
(SELECT COUNT(1) FROM dbo.syscolumns WITH(nolock) WHERE id = t.object_id) Columns,
(SELECT COUNT(DISTINCT index_id) FROM sys.dm_db_partition_stats p WITH(nolock) WHERE p.object_id=t.object_id) AS Indexes,
(SELECT SUM(length) FROM dbo.syscolumns WITH(nolock) WHERE id = t.object_id) AS RowLength,
ISNULL((SELECT SUM(reserved_page_count) FROM sys.dm_db_partition_stats p WITH(nolock) WHERE p.object_id=t.object_id),0)*8 + 
ISNULL((SELECT SUM(reserved_page_count) FROM sys.dm_db_partition_stats p2 WITH(nolock)
INNER JOIN sys.internal_tables i WITH(nolock) ON p2.object_id = i.object_id
WHERE i.parent_id = t.object_id
AND i.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)),0)* 8 AS ReservedKb,
ISNULL((SELECT SUM(in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) from sys.dm_db_partition_stats p with(nolock) 
where p.object_id=t.object_id and p.index_id < 2),0)* 8 AS TableUsedKb,
ISNULL((SELECT SUM(used_page_count) FROM sys.dm_db_partition_stats p WITH(nolock) WHERE p.object_id=t.object_id),0)*8 + 
ISNULL((SELECT SUM(used_page_count) FROM sys.dm_db_partition_stats p2 WITH(nolock) 
INNER JOIN sys.internal_tables i WITH(nolock) on p2.object_id = i.object_id
WHERE i.parent_id = t.object_id
AND i.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)),0)* 8 AS UsedKb,create_date,modify_date
FROM sys.tables t WITH(nolock) WHERE TYPE='U') A
GROUP BY name
ORDER BY 3 DESC

-- Z表创建

【Z表简介】金蝶云星空中业务对象内码获取的方式有如下两种：
1、整数型的内码，绝大部分是通过一个包含自增列（列名统一为id）的z_开头的表来实现的，如科目多语言表t_bd_account_l对应的z表为z_bd_account_l;当然也有例外如T_BD_MATERIALGROUPLEVEL及其他分组级次表的主键是直接利用自增列来生成内码的。
2、字符型的内码，通过newid()生成唯一的guid作为内码，比如报表T_KDS_RPT或者辅助资料类别T_BAS_ASSISTANTDATA。
【Z表生成时机】
部分表创建数据中心的时候已经生成，但是，也有一部分的表要等到第一次使用的时候系统才生成，如初始核算数据明细表Z_HS_INITCALCULATEENTRY空白账套是没有这个表的
【如何生成Z表】
如果不是通过数据库直接操作，是不需要担心Z表不存在的，因为不存在时系统会自动生成。当系统中不存在Z表又需要通过数据库直接获取Z表的内码的时候，才需要自己创建。比如我们的【金蝶云星空专项服务工具】的数据结转（https://kdcsrv.ik3cloud.com/）工具，自动生成初始核算数据，如果操作的数据中心没有做过核算数据录入，那么我们就需要自己创建Z_HS_INITCALCULATEENTRY了。以下是创建Z表的脚本，只需要替换@TableName参数即可执行，并且只有在Z表不存在时创建。

DECLARE @sql nvarchar(max)
DECLARE @TableName nvarchar(100)
SET @TableName='z_bd_account_L'
SET @sql='IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name='''+@TableName+''')
CREATE TABLE [dbo].['+@TableName+']
([Id] [bigint] IDENTITY(100001,1) NOT NULL,
[Column1] [int] NOT NULL,
CONSTRAINT [P'+@TableName+'] PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]'
EXEC (@sql)

-- 查看数据库服务器硬盘空间

WITH T1 AS(
SELECT DISTINCT
REPLACE(vs.volume_mount_point,':\','') AS 盘符 ,
CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS 总空间 ,
CAST(vs.available_bytes/ 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS 剩余空间
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
)
SELECT
盘符,
总空间,
总空间-剩余空间 AS 已使用空间,
剩余空间,CAST(剩余空间*100/总空间 AS NUMERIC(18,2)) AS 空闲率
FROM T1

-- 数据库优化 1

--第一步重建索引

DECLARE @sName VARCHAR( 200)
DECLARE @SqlStr VARCHAR(2000) 
DECLARE Opo_Cursor CURSOR 
FOR SELECT name FROM sysobjects WHERE xtype = 'U' AND ( name NOT LIKE 'TMP%' ) ;
OPEN Opo_Cursor 
FETCH NEXT FROM Opo_Cursor INTO @sName
WHILE @@FETCH_STATUS = 0 
BEGIN TRY 
SET @SqlStr = '';
SELECT @SqlStr =@SqlStr + 'alter index all on [' + @sName + '] rebuild;' 
EXEC ( @SqlStr );
FETCH NEXT FROM Opo_Cursor INTO @sName 
END TRY 
BEGIN CATCH 
FETCH NEXT FROM Opo_Cursor INTO @sName 
END CATCH
CLOSE Opo_Cursor
DEALLOCATE Opo_Cursor

--第二步更新统计信息

DECLARE @sName1 VARCHAR( 200)
DECLARE @SqlStr1 VARCHAR(2000) 
DECLARE Opo_Cursor1 CURSOR 
FOR SELECT name FROM sysobjects WHERE xtype = 'U' AND ( name NOT LIKE 'TMP%' ) ;
OPEN Opo_Cursor1 
FETCH NEXT FROM Opo_Cursor1 INTO @sName1
WHILE @@FETCH_STATUS = 0 
BEGIN TRY 
SET @SqlStr1 = '';
SELECT @SqlStr1 =@SqlStr1 + 'UPDATE STATISTICS ['+ @sName1+'];'
EXEC ( @SqlStr1 );
FETCH NEXT FROM Opo_Cursor1 INTO @sName1 
END TRY 
BEGIN CATCH 
FETCH NEXT FROM Opo_Cursor1 INTO @sName1 
END CATCH
CLOSE Opo_Cursor1
DEALLOCATE Opo_Cursor1

--第三步压缩索引

DECLARE @sName2 VARCHAR( 200)
DECLARE @SqlStr2 VARCHAR(2000) 
DECLARE Opo_Cursor2 CURSOR 
FOR SELECT name FROM sysobjects WHERE xtype = 'U' AND ( name NOT LIKE 'TMP%' ) ;
OPEN Opo_Cursor2 
FETCH NEXT FROM Opo_Cursor2 INTO @sName2
WHILE @@FETCH_STATUS = 0 
BEGIN TRY 
SET @SqlStr2 = '';
SELECT @SqlStr2 =@SqlStr1 + 'alter index all on ['+@sName2+ '] rebuild WITH (DATA_COMPRESSION=ROW);'
EXEC ( @SqlStr2 );
FETCH NEXT FROM Opo_Cursor2 INTO @sName2 
END TRY 
BEGIN CATCH 
FETCH NEXT FROM Opo_Cursor2 INTO @sName2 
END CATCH
CLOSE Opo_Cursor2
DEALLOCATE Opo_Cursor2

--第四步压缩数据表

DECLARE @sName3 VARCHAR( 200)
DECLARE @SqlStr3 VARCHAR(2000) 
DECLARE Opo_Cursor3 CURSOR 
FOR SELECT name FROM sysobjects WHERE xtype = 'U' AND ( name NOT LIKE 'TMP%' ) ;
OPEN Opo_Cursor3 
FETCH NEXT FROM Opo_Cursor3 INTO @sName3
WHILE @@FETCH_STATUS = 0 
BEGIN TRY 
SET @SqlStr3 = '';
SELECT @SqlStr3 =@SqlStr1 + 'alter index all on ['+@sName3+ '] rebuild WITH (DATA_COMPRESSION=ROW);'
EXEC ( @SqlStr3 );
FETCH NEXT FROM Opo_Cursor3 INTO @sName3 
END TRY 
BEGIN CATCH 
FETCH NEXT FROM Opo_Cursor3 INTO @sName3 
END CATCH
CLOSE Opo_Cursor3
DEALLOCATE Opo_Cursor3

-- 数据库优化 2

/*可一次全部执行，不必分4次。4段语句均为执行全库扫描与优化，故耗时会很久。
执行期间将会拖慢金蝶系统性能，强烈要求必须放在系统空闲时执行。
作者：深圳周少斌*/

SET NOCOUNT ON --关闭统计以提升整体执行效率
declare @StepStartTime datetime,@GlobalStartTime datetime --时长统计公共变量：步进开始时间，全局开始时间。
select @StepStartTime=getdate(),@GlobalStartTime=getdate()
declare @TableName TABLE(name nvarchar(100) NOT NULL,UNIQUE CLUSTERED(name)) --声明表变量：待优化的数据库物理表名清单。
declare @TableNameForLoop TABLE(name nvarchar(100) NOT NULL,UNIQUE CLUSTERED(name)) --声明表变量：待优化的数据库物理表名清单_用于循环。
declare @name nvarchar(100),@SqlStr nvarchar(300) --声明变量：待优化物理表名，优化语句。
Insert into @TableName SELECT name from sysobjects where xtype='U' and (name not like'TMP%') --初始化表变量：待优化的数据库物理表名清单

--第1步.重建索引，整理碎片

Insert into @TableNameForLoop SELECT * from @TableName --初始化表变量：用于循环待优化的数据库物理表名清单_用于循环
WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name)
begin 
select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行
delete from @TableNameForLoop WHERE name=@name;--删除上一行语句已取值的行。
set @SqlStr='alter index all on ['+@name+'] rebuild;' --拼接优化语句与待优化物理表名
BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句，如果有异常就直接吞掉，继续循环。
end
print '第1步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长
set @StepStartTime=getdate();

--第2步.更新统计信息，以利于SQL Server后台优化执行计划

Insert into @TableNameForLoop SELECT * from @TableName --重新初始化表变量：用于循环待优化的数据库物理表名清单_用于循环
WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name)
begin 
select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行
delete from @TableNameForLoop WHERE name=@name;--删除上一行语句已取值的行。
set @SqlStr='UPDATE STATISTICS ['+@name+'];' --拼接优化语句与待优化物理表名
BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句，如果有异常就直接吞掉，继续循环。
end
print '第2步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长
set @StepStartTime=getdate();

--第3步.压缩索引以提升I/O性能（注意，这里并不是收缩数据库，而是压缩索引）

Insert into @TableNameForLoop SELECT * from @TableName --重新初始化表变量：用于循环待优化的数据库物理表名清单_用于循环
WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name)
begin 
select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行
delete from @TableNameForLoop WHERE Name=@name;--删除上一行语句已取值的行。
set @SqlStr='alter index all on ['+@name+ '] rebuild WITH (DATA_COMPRESSION=ROW);' --拼接优化语句与待优化物理表名
BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句，如果有异常就直接吞掉，继续循环。
end
print '第3步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长
set @StepStartTime=getdate();

--第4步.压缩数据表以提升I/O性能（注意，这里并不是收缩数据库，而是压缩物理表)

Insert into @TableNameForLoop SELECT * from @TableName --重新初始化表变量：用于循环待优化的数据库物理表名清单_用于循环
WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name)
begin 
select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行
delete from @TableNameForLoop WHERE Name=@name;--删除上一行语句已取值的行。
set @SqlStr='alter table ['+@name+'] rebuild WITH (DATA_COMPRESSION=ROW);' --拼接优化语句与待优化物理表名
BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句，如果有异常就直接吞掉，继续循环。
end
print '第4步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长

--*最后，一定记录本次执行以上四步优化的全程总耗时，做为后续设置SQL Server定时作业的重要参考。
print '全程总耗时:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@GlobalStartTime,getdate())))/60000,2)))+'分钟。请记录此总耗时，做为设置定时作业的依据';
SET NOCOUNT OFF --恢复统计
/*特别说明：第1步、第2步，推荐每天、每周执行；第3步、第4步，推荐每月、每季执行，不推荐高频执行*/

-- 查询数据库版本

SELECT @@VERSION;

-- 查询BOS版本（基于管理中心库 K3DBConfiger）

SELECT a.FDATACENTERID AS 数据中心内码,b.FNAME 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 [数据中心(账套名称)],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

-- 查询BOS版本（基于业务数据库）

SELECT *
FROM T_BOS_INSTALLEDPACKAGE
WHERE FPKGTYPE='DataModel' 
AND FPKGID='K3Cloud_DC_VERSION'

此处不加PKKGID可查询其他二开补丁包的打包版本记录，此处记录的是版本号非产品版本而是构件号，需要自行匹配对应的产品版本号：
eg :平台最新的2022年6月补丁 PT-146915 [8.0.0.202206] 对应构建号：8.0.277.10

-- 查询视图依赖关系：

EXEC SP_DEPENDS 视图名称

-- 采购入库单

SELECT a.FID,a.FENTRYID,b.FBILLNO 单据号,CONVERT(VARCHAR(10),b.FDATE,126) 入库日期,f.FNAME 供应商,
        c.FNUMBER 物料编码,d.FNAME 物料名称,d.FSPECIFICATION 规格,e.FNAME 单位,a.FREALQTY 实收数量,
        g.FNAME 仓库,i.FNAME 仓位,j.FPRICE 单价,CONVERT(DECIMAL(18,2),j.FAMOUNT) 金额,
        CONVERT(DECIMAL(18,0),j.FTAXRATE) "税率%",CONVERT(DECIMAL(18,2),j.FTAXAMOUNT) 税额,
        CONVERT(DECIMAL(18,2),J.FTAXPRICE) 含税单价,CONVERT(DECIMAL(18,2),j.FALLAMOUNT) 含税金额,
        a.FSRCBILLNO 来源单号,a.FSRCBILLTYPEID 来源单据
FROM T_STK_INSTOCKENTRY a
JOIN T_STK_INSTOCK b ON a.FID = b.FID
JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID
JOIN T_BD_MATERIAL_L d ON c.FMATERIALID = d.FMATERIALID
JOIN T_BD_UNIT_L e ON a.FUNITID = e.FUNITID
JOIN T_BD_SUPPLIER_L f ON b.FSUPPLIERID = f.FSUPPLIERID
JOIN T_BD_STOCK_L g ON a.FSTOCKID = g.FSTOCKID
JOIN T_BAS_FLEXVALUESDETAIL h ON a.FSTOCKLOCID = h.FID
JOIN T_BAS_FLEXVALUESENTRY_L i ON h.FF100004 = i.FPKID
JOIN T_STK_INSTOCKENTRY_F j ON a.FENTRYID = j.FENTRYID
-- JOIN T_STK_INSTOCKSERIAL k ON a.FENTRYID = k.FENTRYID    --序列号信息
WHERE b.FBILLNO = 'CGRK00306'