数据清洗SQL，一次性的工作

 清洗决策易的客户

select * from jcyclient where clientid=0
create index jcyclient_index on jcyclient(clientname)
update jcyclient set clientname=replace(clientname,'（','(');
update jcyclient set clientname=replace(clientname,'）',')');
update jcyclient set clientid=b.id from client as b where clientname=b.name

 

UPDATE JCY_YJLX SET yjlx_xyjs=3 where yjlx_tx='OTC' and (yjlx_xyjs=0 or yjlx_xyjs is null)
and yjlx_xylabel in ('部队医院:2040','村卫生室:2087','非连锁门店:3002','非协议商业:1004','非协议商业-专销:1005','进口连锁:6030',
'进口连锁-专销:6032','连锁门店:3001','门诊部:2083','其它医院:2080','社区卫生服务中心:2086','社区医疗站:2085',
'卫生监督所:2033','卫生科室:2082','乡镇卫生院:2084','医院-专销:2012','院校医院:2070','诊所:2081','综合医院:2010');

 

 更新物料类别

update jcy_yjlx set yjlx_wllb=b.category from goods as b 
where yjlx_wlbh=b.code and (yjlx_wllb is null )
;

 

select yjlx_lxqj,yjlx_gysmc,yjlx_xybm,yjlx_tzry,yjlx_tzrymc as 新系统,yjlx_xyryxm as 决策易,yjlx_xybmmc,yjlx_xybmxmc from jcy_yjlx
where yjlx_tzrymc='李弘波' and  yjlx_xyryxm='赵伟'  
--and yjlx_xybmmc='苏州三部' ;
order by yjlx_lxqj;

update jcy_yjlx set yjlx_tzry=11216,yjlx_tzrymc='卢福全' 
where yjlx_tzrymc='杨苏华' and  yjlx_xyryxm='卢福全' 

select a.id,username,department_id ,b.name from account as a
left join department as b 
on a.department_id=b.id
where username like '赵伟%';
where b.name like '%贵阳%'；
select id,username from account where  username like '高%';

 

--通过别名库更新客户号
update jcy_yjlx set yjlx_khbh=b.标准客户编号 from c13 as b 
where yjlx_gysbh=b.供应商编号 and yjlx_khmc=b.下游客户名称;
--通过客户字典典更新
update jcy_yjlx set yjlx_khbh=b.id from client as b 
where  yjlx_khmc=b.name;
select distinct yjlx_gysbh,yjlx_gysmc,yjlx_khmc,yjlx_ppkhmc from jcy_yjlx where yjlx_khbh=0 or yjlx_khbh is null;

 

 查找人员错误的SQL

select yjlx_lxqj,yjlx_khmc,yjlx_tzrymc,yjlx_xyryid,yjlx_xyryxm,yjlx_xybmid,yjlx_xybmmc from jcy_yjlx where yjlx_tzrymc!=yjlx_xyryxm and yjlx_tzrymc is not null and yjlx_dqmc like '%阜新%' order by yjlx_lxqj;

select * from department where comments='09124cf9-6574-db14-c913-edc204b8ec2b';
select * from account where tblsystemuserid='615a7c49-5c2b-477f-9f52-3daf0bf17428';
select * from account where username='兰辉'

 

--更新纯销
update jcy_yjlx set yjlx_sfcx='1' where yjlx_xyjs=3 and yjlx_tx='OTC' ;
--分销
update jcy_yjlx set yjlx_sfFx='1' where yjlx_xyjs=2 and yjlx_JS=1 ;
--平调
update jcy_yjlx set yjlx_sfFx='2' where yjlx_xyjs=2 and yjlx_JS=2 ;
update jcy_yjlx set yjlx_sfFx='2' where yjlx_xyjs=1 and yjlx_JS=1 ;

 

--更新物料编号
update jcy_yjlx set yjlx_wlbh=b.code from goods as b
where yjlx_spbh=b.code1;
select distinct yjlx_gysmc,yjlx_wlbh,yjlx_wlmc,yjlx_ggxh,yjlx_spbh from jcy_yjlx where yjlx_wlbh is null or yjlx_wlbh='';
select distinct yjlx_wlbh,yjlx_wlmc,yjlx_spbh from jcy_yjlx where yjlx_wlbh is null or yjlx_wlbh='';

--更新上游级别
update jcy_yjlx set yjlx_js=1 where yjlx_sylabel in ('地区平台:1003','连锁直供:6010','一级商:1001');
update jcy_yjlx set yjlx_js=2 where yjlx_sylabel in ('二级商:1002','出口连锁:6031');
--更新下游级别
update jcy_yjlx set yjlx_xyjs=1 where yjlx_xylabel in ('地区平台:1003','连锁直供:6010','一级商:1001');
update jcy_yjlx set yjlx_xyjs=2 where yjlx_xylabel in ('二级商:1002','出口连锁:6031');
select distinct yjlx_sylabel,yjlx_xyjs from jcy_yjlx where yjlx_xyjs=0 or yjlx_xyjs is null;
UPDATE JCY_YJLX SET yjlx_xyjs=3 where yjlx_tx='OTC' and (yjlx_xyjs=0 or yjlx_xyjs is null);


--更新条线；
SELECT * FROM goods_categories
update jcy_yjlx set yjlx_tx='RX'     from goods_categories as b 
         where  yjlx_wllb=b.categories_id           and b.parent_id=33 ;
update jcy_yjlx set yjlx_tx='OTC'     from goods_categories as b 
         where  yjlx_wllb=b.categories_id           and b.parent_id=3 ;
update jcy_yjlx set yjlx_tx='KFY'     from goods_categories as b 
         where  yjlx_wllb=b.categories_id           and b.parent_id=9 ;
--查临床的发货

 

--更新 条线 共户产品问题；

  --OTC--> RX

    mysql = ' update ' ||filename || ' set yjlx_tx=''RX'' ';

    mysql = mysql ||' from goods_categories as b ';

        mysql = mysql ||' where 1=1 ';

        mysql = mysql || strwhere ;

        mysql = mysql || ' and yjlx_wllb=b.categories_id ';

        mysql = mysql || ' and b.parent_id=13  and yjlx_tx =''OTC'' '  ;

        

        RAISE NOTICE 'SQL语句1为： %', mysql;

      execute mysql  ;    

  --RX-->OTC 

          mysql = ' update ' ||filename || ' set yjlx_tx=''OTC'' ';

            mysql = mysql || ' from  goods_categories as b ';

          mysql = mysql || ' where  1=1 ';

            mysql = mysql || strwhere ;

          mysql = mysql || ' and     yjlx_wllb=b.categories_id ';

            mysql = mysql || ' and b.parent_id=3 and yjlx_tx =''RX''';

        

        RAISE NOTICE 'SQL语句1为： %', mysql;

      execute mysql  ;

        --OTC-->KFY 

        mysql = ' update ' ||filename || ' set yjlx_tx=''KFY'' , ';

        mysql = mysql || ' yjlx_tzry=0,yjlx_tzrymc='''',';

        mysql = mysql || ' yjlx_sybm=0,yjlx_ywy=0,yjlx_ywymc='''', ';

        mysql = mysql || ' yjlx_sffx=0,yjlx_sfcx=0,';

        mysql = mysql || ' yjlx_sfzx=0 ';

        mysql = mysql || ' where 1=1 ';

        mysql = mysql || strwhere ;

        mysql = mysql || ' and yjlx_wllb in (10,11,12) ';

        RAISE NOTICE 'SQL语句OTC-->KFY为： %', mysql;

        execute mysql  ;

 

--更新下游人员名称；
update jcy_yjlx set yjlx_tzry=b.id ,yjlx_tzrymc=b.username from account as b 
where yjlx_xyryid=b.tblsystemuserid;

--更新旧系统的物料编号
update jcy_yjlx set yjlx_spbh=b.code from productbase as b 
where yjlx_goodsid=b.productid;

 

--查找未匹配人员
select distinct yjlx_xyryid,yjlx_xyryxm from jcy_yjlx  where yjlx_tzry=0 or yjlx_tzry is null

 

--更新下游部门ID
update jcy_yjlx set yjlx_xybm=b.id from department as b
where yjlx_xybmid=b.comments

 

 

select upload_ym,billdate,fromaccountidname,toaccountidname,toenttypename,
saleto_entname,
lotno,drugidname,drugid,saleto_unit,productsize,saleto_count,saleto_total_price,
saleto_price,
saleto_business_unit_idname,saleto_business_unit_id,to_ownerid,to_owneridname,to_account_classificationcode2,from_account_classificationcode2
from drug_flowbase  where upload_ym='202203' limit 100;
select businessunitid,tblbusinessunitid,* from tblbusinessunitbase where tblbusinessunitid='0cd30992-e6b9-4991-9c16-766878b21d4c'


select * from jcy_yjlx limit 1000;
update jcy_yjlx set yjlx_spbh=b.code from productbase as b 
where yjlx_goodsid=b.productid;

select * from department;

--更新下游部门ID
update jcy_yjlx set yjlx_xybm=b.id from department as b
where yjlx_xybmid=b.comments;
select distinct yjlx_xybmid,yjlx_xybmmc from jcy_yjlx where yjlx_xybm is null 
select * from jcy_yjlx where yjlx_xybm is null;
update jcy_yjlx set yjlx_xylabel="replace"(yjlx_xylabel, '", "Value": "', ':'),
yjlx_sylabel="replace"(yjlx_sylabel, '", "Value": "', ':')


DATA zsfhm(10) TYPE c.
CLEAR :zsf,zsfhm,zcs.
SELECT  SINGLE regio,ort01 INTO (@zsfhm,@zcs) FROM kna1
  WHERE kunnr = @vbak-kunnr .
**zsf = zsfhm.
SELECT SINGLE bezei INTO @zsf FROM t005u
  WHERE land1 = 'CN'
  AND spras = '1' and  bland = @ZsfHM .

ZBP009 显示订单
ZBP018 加上运费
zbp013 负数加上符号
select 
from_businessunitid,from_businessunitidname,
from_accountidname,import_year_month,product_alias_uom,to_account_alias_name,to_accountidname,from_ownerid,from_owneridname,to_owneridname,to_ownerid
product_alias_name,product_alias_size,product_lotno,
saleto_qty,confirm_qty,ori_saleto_qty,import_qty,productcode,
to_businessunitid,to_businessunitidname

from ddm_clean_database limit 100；

select upload_ym,billdate,fromaccountidname,toaccountidname,toenttypename,
saleto_entname,
lotno,drugidname,drugid,saleto_unit,productsize,saleto_count,saleto_total_price,
saleto_price,
saleto_business_unit_idname,saleto_business_unit_id,to_ownerid,to_owneridname,to_account_classificationcode2,from_account_classificationcode2
from drug_flowbase  where upload_ym='202203' limit 100