能解决问题的常用SQL汇总

栏目：eas cloud知识作者：金蝶来源：金蝶云社区发布：2024-09-16浏览：1

能解决问题的常用SQL汇总

### 组织缺失客商查询、备份和删除 ``` --组织缺失供应商查询 select S.FID 供应商ID,S.FNUMBER 供应商编码,S.FNAME_L2 供应商名称,U.FNUMBER 管理单元编码,U.FNAME_L2 管理单元名称 from T_BD_SUPPLIER S left outer join T_ORG_BASEUNIT U on S.FCONTROLUNITID = U.FID where S.FCONTROLUNITID = S.FADMINCUID and U.FID is null --供应商备份 select * into kxbbak1229SUPPLIER from T_BD_SUPPLIER where fid in ( select S.FID from T_BD_SUPPLIER S left outer join T_ORG_BASEUNIT U on S.FCONTROLUNITID = U.FID where S.FCONTROLUNITID = S.FADMINCUID and U.FID is null ) --组织缺失供应商删除 delete from T_BD_SUPPLIER where fid in ( select S.FID from T_BD_SUPPLIER S left outer join T_ORG_BASEUNIT U on S.FCONTROLUNITID = U.FID where S.FCONTROLUNITID = S.FADMINCUID and U.FID is null ) --组织缺失客户查询 select C.FID 客户ID,C.FNUMBER 客户编码,C.FNAME_L2 客户名称,U.FNUMBER 管理单元编码,U.FNAME_L2 管理单元名称 from T_BD_CUSTOMER C left outer join T_ORG_BASEUNIT U on C.FCONTROLUNITID = U.FID where C.FCONTROLUNITID = C.FADMINCUID and U.FID is null --客户备份 select * into kxbbak1229CUSTOMER from T_BD_CUSTOMER where fid in ( select C.FID from T_BD_CUSTOMER C left outer join T_ORG_BASEUNIT U on C.FCONTROLUNITID = U.FID where C.FCONTROLUNITID = C.FADMINCUID and U.FID is null ) --组织缺失客户删除 delete from T_BD_CUSTOMER where fid in ( select C.FID from T_BD_CUSTOMER C left outer join T_ORG_BASEUNIT U on C.FCONTROLUNITID = U.FID where C.FCONTROLUNITID = C.FADMINCUID and U.FID is null ) ----- select * from t_bd_supplier where fcontrolunitid not in (select fid from t_org_baseunit) or FAdminCUID not in (select fid from t_org_baseunit) ``` ##### 修复物料基本计量单位引起的问 ``` --先执行 select * into bak190815_multimeasureunit from t_bd_multimeasureunit;备份 DECLARE MATERIALID VARCHAR2(100); newbaseunit VARCHAR2(100); oldbaseunit VARCHAR2(100); isexists INT; seqok INT; newseq INT; BEGIN ----首先修复多计量单位里面的脏数据（存在两个基本计量单位的数据) FOR currow IN (SELECT fmaterialid,MIN(fid) fid FROM t_bd_multimeasureunit WHERE fisbasicunit = 1 GROUP BY fmaterialid HAVING COUNT(1) > 1) LOOP SELECT COUNT(1) INTO isexists FROM t_bd_multimeasureunit WHERE fisbasicunit = 1 AND fmaterialid = currow.fmaterialid AND fmeasureunitid = (SELECT fbaseunit FROM t_bd_material WHERE fid = currow.fmaterialid); UPDATE t_bd_multimeasureunit SET fisbasicunit = 0 WHERE fmaterialid = currow.fmaterialid; IF isexists > 0 THEN UPDATE t_bd_multimeasureunit SET fisbasicunit = 1 WHERE fmaterialid = currow.fmaterialid AND fmeasureunitid = (SELECT fbaseunit FROM t_bd_material WHERE fid = currow.fmaterialid); ELSE UPDATE t_bd_multimeasureunit SET fisbasicunit = 1 WHERE fid = currow.fid; END IF; END LOOP; ------------------更新计量单位值---------------- FOR currow IN (SELECT MATERIAL.* FROM T_BD_MATERIAL MATERIAL LEFT JOIN T_BD_MULTIMEASUREUNIT UNIT ON MATERIAL.FID = UNIT.FMATERIALID WHERE FISBASICUNIT = 1 AND MATERIAL.fbaseunit <> UNIT.FMEASUREUNITID) LOOP MATERIALID := currow.FID; --得到物料表上的baseunitid用作需要更新的baseunit newbaseunit := currow.fbaseunit; -----判断新的基本计量单位是否存在于多计量单位里,如果不存在,则根据多计量单位修改物料的基本计量单位,如果存在,则变更 SELECT COUNT(1) INTO isexists FROM t_bd_multimeasureunit WHERE fmaterialid = materialid AND fmeasureunitid = newbaseunit; IF isexists > 0 THEN --将原来多计量单位里面的基本计量单位设置为非基本的 UPDATE t_bd_multimeasureunit SET fisbasicunit = 0 WHERE fmaterialid = materialid AND FISBASICUNIT = 1; ---设置新的基本计量单位 UPDATE t_bd_multimeasureunit SET fisbasicunit = 1 WHERE fmaterialid = materialid AND fmeasureunitid = newbaseunit; ELSE UPDATE t_bd_material SET fbaseunit = (SELECT fmeasureunitid FROM t_bd_multimeasureunit WHERE fmaterialid = materialid AND fisbasicunit = 1) WHERE fid= materialid; END IF; END LOOP; -----更新基本单位的序列号 FOR currow IN (SELECT fmaterialid FROM t_bd_multimeasureunit WHERE FISBASICUNIT = 1 AND fseq <> 0 GROUP BY fmaterialid HAVING COUNT(1) > 0) LOOP materialid := currow.fmaterialid; SELECT fseq INTO newseq FROM t_bd_multimeasureunit WHERE fmaterialid = materialid AND fisbasicunit = 1; ---更新原来seq为0的记录为新的序列号 UPDATE t_bd_multimeasureunit SET fseq = newseq WHERE fmaterialid = materialid AND fseq = 0; ----更新基本计量单位的序列号为0 UPDATE t_bd_multimeasureunit SET fseq =0 WHERE fmaterialid = materialid AND fisbasicunit = 1; END LOOP; END; ``` ##### 内部客商校验类，不需要此校验可以去除（某些客户会因为多了这个校验出错） ``` --添加内部客商校验类 if not exists(select * from t_sys_checkers where fchecker = 'com.kingdee.eas.basedata.master.cssp.CSSPCompanyOrgBeforeDealService' and finterface = 'com.kingdee.eas.basedata.org.IOtherDealOrgChange') insert into t_sys_checkers(FACTION, FPROPERTY, FBOSTYPE, FOBJECTNAME, FINTERFACE, FCHECKER) values (0, N'*', N'CCE7AED4', 'FullOrgUnit', N'com.kingdee.eas.basedata.org.IOtherDealOrgChange', N'com.kingdee.eas.basedata.master.cssp.CSSPCompanyOrgBeforeDealService'); --删除内部客商校验类脚本 delete from t_sys_checkers where fchecker = 'com.kingdee.eas.basedata.master.cssp.CSSPCompanyOrgBeforeDealService' and finterface = 'com.kingdee.eas.basedata.org.IOtherDealOrgChange' 去掉此校验，如果内部客商引用了某组织，仍然可以取消勾选组织实体，且可以取消掉财务组织属性或者利润中心属性。如果后续需要加上此校验，只要执行脚本加上校验类信息就可以了。校验功能回迁后，打标准补丁的话，是会重新启用该校验的。 ``` ##### 更新客户银行信息 ``` -- 备份T_BD_CUSTOMERCOMPANYBANK表 select * into T_BD_CUSTOMERCOMPANYBANK_bak from T_BD_CUSTOMERCOMPANYBANK; -- 更新银行信息,替换下面各个中文字段【保证每个财务资料下面都是只有一个银行信息才能使用这个更新脚本】 update T_BD_CUSTOMERCOMPANYBANK set FBANK = N'开户银行',FBANKACCOUNT = N'银行账号',FBANKADDRESS = N'银行地址',FACCOUNTNAME= N'账户名称',FCURRENCY= N'币别',FOPENAREA=N'开户地址' WHERE FID in (select ccb.FID from T_BD_CUSTOMERCOMPANYBANK ccb,T_BD_CUSTOMERCOMPANYINFO cci,T_BD_CUSTOMER c where cci.FID = ccb.FCUSTOMERCOMPANYINFOID and cci.FCUSTOMERID = c.FID and c.FNUMBER = '客户编码'); update T_BD_SupplierCOMPANYBANK set FBANK = N'开户银行',FBANKACCOUNT = N'银行账号',FBANKADDRESS = N'银行地址',FACCOUNTNAME= N'账户名称',FCURRENCY= N'币别',FOPENAREA=N'开户地址' WHERE FID in (select ccb.FID from T_BD_SupplierCOMPANYBANK ccb,T_BD_SupplierCOMPANYINFO cci,T_BD_Supplier c where cci.FID = ccb.FSupplierCOMPANYINFOID and cci.FSupplierID = c.FID and c.FNUMBER = 'g编码'); ``` 09:10 ##### 修复物料多计量单位多行被标识为基本单位 ``` 修复物料多计量单位多行被标识为基本单位 --临时表 : -- qabvrfnkrtsrawkk --当前步骤：确定存在问题的物料范围 (1/3) --脚本： select m.fid into qabvrfnkrtsrawkk from T_BD_MultiMeasureUnit mu inner join T_BD_Material m on m.fid =mu.FMaterialID where mu.FIsBasicUnit=1 group by m.Fid having count(1) >1 --当前步骤：清空问题物料多计量单位的基本单位标识 (2/3) --脚本： select * into T_BD_MultiMeasureUnit_bak from T_BD_MultiMeasureUnit; update T_BD_MultiMeasureUnit set fisbasicunit = 0 where fmaterialid in (select fid from qabvrfnkrtsrawkk); --当前步骤：为问题物料重新标识多计量单位中的基本单位 (3/3) --脚本： update T_BD_MultiMeasureUnit set fisbasicunit =1 where fid in (select u.fid from T_BD_MultiMeasureUnit u inner join T_BD_Material m on m.fid =u.FMaterialID and m.FBaseUnit=u.FMeasureUnitID inner join qabvrfnkrtsrawkk t on t.fid =m.fid ) --临时表 qabvrfnkrtsrawkk 删除 drop table qabvrfnkrtsrawkk ``` ##### 版本升级后造成的数据问题 FInternalCustomerType ``` 版本升级后造成的数据问题。造成了FInternalCustomerType字段为空的情况，需要手动给此字段赋值。该字段代表的是内部供应商类型，一般是0=公司 -- 客户 -- 当内部类型是空且利润中心为空时，设置默认值内部类型为0=公司 update T_BD_CUSTOMER set FINTERNALCUSTOMERTYPE = 0 where FINTERNALCUSTOMERTYPE is null and FINTERNALPROFITCENTER is NULL; -- 当内部类型是空且利润中心不为空时，设置内部类型为1=利润中心 update T_BD_CUSTOMER set FINTERNALCUSTOMERTYPE = 1 where FINTERNALCUSTOMERTYPE is null and FINTERNALPROFITCENTER is not NULL; --供应商 -- 当内部类型是空且利润中心为空时，设置默认值内部类型为0=公司 update T_BD_SUPPLIER set FinternalSupplierType = 0 where FinternalSupplierType is null and FINTERNALPROFITCENTER is NULL; -- 当内部类型是空且利润中心不为空时，设置内部类型为1=利润中心 update T_BD_SUPPLIER set FinternalSupplierType = 1 where FinternalSupplierType is null and FINTERNALPROFITCENTER is not NULL; ``` ##### 物料基本资料已经删除，删除物料利润中心资料脏数据 ``` --查询 select * from T_BD_MATERIALPROFITCENTERINFO where FPROFITCENTERID not in (select fid from t_org_profitCenter); select * from T_BD_MATERIALPROFITCENTERINFO where fmaterialid not in (select fid from t_bd_material); --备份 select * into bak0415_maPR_org from T_BD_MATERIALPROFITCENTERINFO where FPROFITCENTERID not in (select fid from t_org_profitCenter); select * into bak0415_maPR_ma from T_BD_MATERIALPROFITCENTERINFO where fmaterialid not in (select fid from t_bd_material); --删除 delete from T_BD_MATERIALPROFITCENTERINFO where fid in (select fid from bak0415_maPR_org); delete from T_BD_MATERIALPROFITCENTERINFO where fid in (select * into bak0415_maPR_ma); ``` ##### 未知用处查询不存在分配表中，存在财务资料的信息 ``` select distinct ma.fid 物料id,ma.fnumber 物料编码,ma.fname_l2 物料名称,org.fname_l2 物料财务资料组织,ma.FCONTROLUNITID 物料所属组织 from T_BD_material ma left join T_BD_materialCOMPANYINFO maCom on maCom.fmaterialid =ma.fid left join t_org_company org on org.fid=maCom.fcompanyid where maCom.Fcompanyid in (select fid from t_org_ctrlunit where fid not LIKE '000%') and maCom.Fcompanyid not in (select FASSIGNCUID from T_BD_DataBaseDAssign asdb where asdb.FBOSOBJECTTYPE ='4409E7F0' and asdb.FDATABASEDID =ma.fid ) and maCom.FCONTROLUNITID not LIKE '000%' --如果按组织查询加上下面条件 and org.fnumber='要查询的组织的编码' ``` ##### 查询、删除创建组织已被删除的外部客户 ``` --临时表 : -- temporgbak --创建临时表：包含不存在的组织id select fcontrolunitid into temporgbak from t_bd_customer where FISINTERNALCOMPANY ='0' and fcontrolunitid not in (select fid from t_org_baseunit); --查询创建组织已被删除的外部客户 select * from t_bd_customer where FISINTERNALCOMPANY ='0' and fcontrolunitid not in (select fid from temporgbak); --备份 select * into tempCustomer_cu from t_bd_customer where FISINTERNALCOMPANY ='0' and fcontrolunitid not in (select fid from temporgbak); select * into tempcustomer_fi from t_bd_customercompanyinfo where fcustomerid in (select fid from tempCustomer_cu); select * into tempcustomer_sale from T_BD_CustomerSaleInfo where fcustomerid in (select fid from tempCustomer_cu); --删除 delete from t_bd_customer where fid in (select fid from tempCustomer_cu); delete from t_bd_customercompanyinfo where fcustomerid in (select fid from tempCustomer_cu); delete from T_BD_CustomerCompanyBank where FCustomerCompanyInfoID in (select fid from tempcustomer_fi); delete from T_BD_CustomerSaleInfo where fcustomerid in (select fid from tempCustomer_cu); delete from T_BD_CustomerLinkMan where FCustomerSaleID in (select fid from tempcustomer_sale ); delete from T_BD_CustomerDlvAddress where FCustomerSaleID in (select fid from tempcustomer_sale ); ``` ##### --查询分配管理单元已被删除的外部客户 ``` --备份表已被删除的组织 temporgbak 需要自行收集被删除的组织 select bcus.* from t_bd_customer bcus join T_BD_DataBaseDAssign dbass on dbass.FDATABASEDID =bcus.fid where bcus.FISINTERNALCOMPANY ='0' and dbass.FASSIGNCUID not in (select fid from temporgbak) --备份 select bcus.* into tempcustomer_ass from t_bd_customer bcus join T_BD_DataBaseDAssign dbass on dbass.FDATABASEDID =bcus.fid where bcus.FISINTERNALCOMPANY ='0' and dbass.FASSIGNCUID not in (select fid from temporgbak) select * into tempcustomer_assfi from t_bd_customercompanyinfo where fcustomerid in (select fid from tempcustomer_ass); select * into tempcustomer_asssale from T_BD_CustomerSaleInfo where fcustomerid in (select fid from tempcustomer_ass); --删除 delete from t_bd_customer where fid in (select fid from tempcustomer_ass); delete from t_bd_customercompanyinfo where fcustomerid in (select fid from tempcustomer_ass); delete from T_BD_CustomerCompanyBank where FCustomerCompanyInfoID in (select fid from tempcustomer_assfi); delete from T_BD_CustomerSaleInfo where fcustomerid in (select fid from tempcustomer_ass); delete from T_BD_CustomerLinkMan where FCustomerSaleID in (select fid from tempcustomer_asssale ); delete from T_BD_CustomerDlvAddress where FCustomerSaleID in (select fid from tempcustomer_asssale ); ``` ##### --查询客户分配到管理单元记录 ``` select cus.fnumber 客户编码,cus.fname_l2 客户名称,cu.fnumber 分配到的管理单元,cu.fname_l2 分配到的管理单元 from t_bd_customer cus join T_BD_DataBaseDAssign dbass on dbass.FDATABASEDID=cus.fid join t_org_ctrlunit cu on cu.fid=dbass.FASSIGNCUID where dbass.FBOSOBJECTTYPE='BF0C040E' --查询分配到某个管理单元的所有客户，可以加上下面条件 and cu.fnumber='管理单元编码' --查询具体某个客户分配到管理单元，可以加上下面条件 and cus.fnumber='客户编码' ``` ##### --查询客户财务资料存在（&分配）哪些组织 ``` select cus.fnumber 客户编码,cus.fname_l2 客户名称,cufi.fnumber 分配到的组织编码,cufi.fname_l2 分配到的组织名称 from t_bd_customer cus join t_bd_customercompanyinfo cusfi on cusfi.FCUSTOMERID =cus.fid join t_org_company cufi on cufi.fid=cusfi.FCOMORGID where --如果只查询分配的加上下面条件 cus.FCONTROLUNITID <>cusfi.FCOMORGID --如果是查询分配到某个组织的加上下面条件 and cufi.fnumber='要查询的组织的编码' --如果是查询具体的某个客户分配到哪些组织，加下面条件 and cus.fnumber='要查询的客户的编码' ``` ### 供应商同步下级银行信息 ``` --供应商银行信息默认插入字段 insert into t_bd_suppliercompanybank(FID, FBANK, FBANKACCOUNT, FBANKADDRESS, FSUPPLIERCOMPANYINFOID, FSEQ, FBANKENGNAME, FBENEFICIARY, FBENEFICIARYENGNAME, FBENEFICIARYBANKCOUNTRYID, FBENEFICIARYBANKCOUNTRYISO, FBENEFICIARYBANKCODE, FBENEFICIARYADDR, FBENEFICIARYBANKSWIFT, FSETTLEMENTPARAM, FAGENCYBANKACCOUNT, FAGENCYBANKNAME, FAGENCYBANKCOUNTRYID, FAGENCYBANKCOUNTRYISO, FAGENCYBANKSWIFT, FAGENCYBANKADDR, FBANKID, FISDEFAULT, CFBANKCODE) values (N'p4AAAACAyMDmLAS9', N'中国人民银行滦州市支行', N'111', N'111', N'p4AAAABkDUvybA7H', 1, null, null, null, null, null, N'001124313009', null, null, null, null, null, null, null, null, null, N'p4AAAABoDlNlt4B1', 1, null); --根据编码查出供应商当前组织下财务资料的银行信息 SELECT bank.* FROM t_bd_suppliercompanybank bank left join t_bd_suppliercompanyinfo com on bank.FSUPPLIERCOMPANYINFOID = com.FID left join t_bd_supplier supp on com.FSUPPLIERID = supp.FID left join t_org_baseUnit org on com.FCOMORGID = org.FID where supp.fnumber = '供应商编码' and org.fnumber = '需要同步的上级组织编码' --删除当前供应商下财务资料下的所有银行信息（除需要同步的上级银行信息） DELETE FROM t_bd_suppliercompanybank WHERE fid in ( SELECT bank.fid FROM t_bd_suppliercompanybank bank left join t_bd_suppliercompanyinfo com on bank.FSUPPLIERCOMPANYINFOID = com.FID left join t_bd_supplier supp on com.FSUPPLIERID = supp.FID where supp.fnumber = '供应商编码' and bank.fid not in ( SELECT bank.fid FROM t_bd_suppliercompanybank bank left join t_bd_suppliercompanyinfo com on bank.FSUPPLIERCOMPANYINFOID = com.FID left join t_bd_supplier supp on com.FSUPPLIERID = supp.FID left join t_org_baseUnit org on com.FCOMORGID = org.FID where supp.fnumber = '供应商编码' and org.fnumber = '需要同步的上级组织编码')) --同步上级供应商财务资料银行信息到所有存在财务资料的下级组织 insert into t_bd_suppliercompanybank(FID, FBANK, FBANKACCOUNT, FBANKADDRESS, FSUPPLIERCOMPANYINFOID, FSEQ, FBANKENGNAME, FBENEFICIARY, FBENEFICIARYENGNAME, FBENEFICIARYBANKCOUNTRYID, FBENEFICIARYBANKCOUNTRYISO, FBENEFICIARYBANKCODE, FBENEFICIARYADDR, FBENEFICIARYBANKSWIFT, FSETTLEMENTPARAM, FAGENCYBANKACCOUNT, FAGENCYBANKNAME, FAGENCYBANKCOUNTRYID, FAGENCYBANKCOUNTRYISO, FAGENCYBANKSWIFT, FAGENCYBANKADDR, FBANKID, FISDEFAULT, CFBANKCODE) select newbosid('E62C04BD') FID, FBANK, FBANKACCOUNT, FBANKADDRESS, comSupp.Fcomid FSUPPLIERCOMPANYINFOID, FSEQ, FBANKENGNAME, FBENEFICIARY, FBENEFICIARYENGNAME, FBENEFICIARYBANKCOUNTRYID, FBENEFICIARYBANKCOUNTRYISO, FBENEFICIARYBANKCODE, FBENEFICIARYADDR, FBENEFICIARYBANKSWIFT, FSETTLEMENTPARAM, FAGENCYBANKACCOUNT, FAGENCYBANKNAME, FAGENCYBANKCOUNTRYID, FAGENCYBANKCOUNTRYISO, FAGENCYBANKSWIFT, FAGENCYBANKADDR, FBANKID, FISDEFAULT, CFBANKCODE from ( SELECT bank.* FROM t_bd_suppliercompanybank bank left join t_bd_suppliercompanyinfo com on bank.FSUPPLIERCOMPANYINFOID = com.FID left join t_bd_supplier supp on com.FSUPPLIERID = supp.FID left join t_org_baseUnit org on com.FCOMORGID = org.FID where supp.fnumber = '供应商编码' and org.fnumber = '需要同步的上级组织编码') bankSupp left join (select com.fid Fcomid from t_bd_suppliercompanyinfo com left join t_bd_supplier supp on com.FSUPPLIERID = supp.FID where supp.fnumber = '供应商编码') comSupp on bankSupp.fSupplierCompanyInfoId != comSupp.Fcomid ``` #### 根据bebank更新银行信息，批量跟新 ```sql --当收款人名称信息为空时，更新收款人名称 update T_BD_SUPPLIERCOMPANYBANK set ( FBENEFICIARY ) = (SELECT supp.FNAME_l2 FROM T_BD_SUPPLIERCOMPANYBANK bank left join T_BD_SUPPLIERCOMPANYINFO company on bank.FSUPPLIERCOMPANYINFOID = company.FID left join t_bd_supplier supp on company.FSUPPLIERID = supp.FID left join T_BE_BEBANK bebank on bank.FBANK = bebank.FNAME_l2 where bank.FBANK is not null and bank.FBENEFICIARY is null ) --当收款行号信息为空时，更新收款行号 update T_BD_SUPPLIERCOMPANYBANK set ( FBENEFICIARYBANKCODE ) = (SELECT bebank.fnumber FROM T_BD_SUPPLIERCOMPANYBANK bank left join T_BD_SUPPLIERCOMPANYINFO company on bank.FSUPPLIERCOMPANYINFOID = company.FID left join t_bd_supplier supp on company.FSUPPLIERID = supp.FID left join T_BE_BEBANK bebank on bank.FBANK = bebank.FNAME_l2 where bank.FBANK is not null and bank.FBENEFICIARYBANKCODE is null ) ```

敲肥羊