select zhspid,spgsh,hshj into gsjj from 

(select b.*,a.rq,a.djbh,a.hshj,row_number() over (PARTITION BY a.spid order by a.rq desc,a.djbh desc ) row 

from openrowset(‘SQLOLEDB‘, ‘60.220.254.22,2433‘; ‘SA‘; ‘zdyy0811.‘,ksoa.dbo.jh_rkmx) a,spkfkzh b

where a.djbh like ‘jha%‘ and a.spid=b.spid and b.spgsh=‘长治‘ and a.hshj>0) c

where row=1

insert into gsjj

select zhspid,spgsh,hshj from 

(select b.*,a.rq,a.djbh,a.hshj,row_number() over (PARTITION BY a.spid order by a.rq desc,a.djbh desc ) row 

from openrowset(‘SQLOLEDB‘,‘192.168.0.100‘; ‘SA‘; ‘‘,st_ccerp.dbo.ywmxk) a,spkfkzh b

where a.djbh like ‘jha%‘ and a.spid=b.spid and b.spgsh=‘大同‘ and a.hshj>0) c

where row=1

insert into gsjj

select zhspid,spgsh,hshj from 

(select b.*,a.rq,a.djbh,a.hshj,row_number() over (PARTITION BY a.spid order by a.rq desc,a.djbh desc ) row 

from openrowset(‘SQLOLEDB‘,‘192.168.10.222‘; ‘SA‘; ‘zdyy‘,ksoa.dbo.jh_rkmx) a,spkfkzh b

where a.djbh like ‘jha%‘ and a.spid=b.spid and b.spgsh=‘运城‘ and a.hshj>0) c

where row=1

insert into gsjj

select zhspid,spgsh,hshj from 

(select b.*,a.rq,a.djbh,a.hshj,row_number() over (PARTITION BY a.spid order by a.rq desc,a.djbh desc ) row 

from openrowset(‘SQLOLEDB‘,‘192.168.16.237‘; ‘SA‘; ‘zdyysm,.‘,ymksoa.dbo.jh_rkmx) a,spkfkzh b,openrowset(‘SQLOLEDB‘,‘192.168.16.237‘; ‘SA‘; ‘zdyysm,.‘,ymksoa.dbo.jh_rkhz) d

where a.djbh like ‘jha%‘ and a.spid=b.spid and b.spgsh=‘医贸‘ and a.hshj>0 and a.djbh=d.djbh and d.dwbh not in 

(select dwbh from openrowset(‘SQLOLEDB‘, ‘192.168.16.237‘; ‘SA‘; ‘zdyysm,.‘,ymksoa.dbo.mchk) where dwmch like ‘山西振东医药物流有限公司‘) 

) c

where row=1

select zhspid,spgsh,hshj from 

(select b.*,a.rq,a.djbh,a.hshj,row_number() over (PARTITION BY a.spid order by a.rq desc,a.djbh desc ) row 

from openrowset(‘SQLOLEDB‘,‘192.168.16.237‘; ‘SA‘; ‘zdyysm,.‘,ymksoa.dbo.jh_rkmx) a,spkfkzh b

where a.djbh like ‘jha%‘ and a.spid=b.spid and b.spgsh=‘医贸‘ and a.hshj>0 ) c

where row=1

select b.zhspid,b.spgsh,Convert(decimal(10,2),sum(a.hsje)/sum(a.shl)) hshj 

into gssj from openrowset(‘SQLOLEDB‘, ‘60.220.254.22,2433‘; ‘SA‘; ‘zdyy0811.‘,ksoa.dbo.pf_ckmx) a,spkfkzh b,

(select spid,rq,row_number() over (PARTITION BY spid order by rq desc,djbh desc ) row 

from openrowset(‘SQLOLEDB‘, ‘60.220.254.22,2433‘; ‘SA‘; ‘zdyy0811.‘,ksoa.dbo.pf_ckmx) ) c

where a.djbh like ‘xsa%‘ and a.spid=b.spid and b.spgsh=‘长治‘ and a.hshj>0 and a.spid=c.spid

and c.row=1 and a.rq>=convert(varchar(10),dateadd(month,-1,c.rq),120) and a.rq<=c.rq

group by b.zhspid,b.spgsh

insert into gssj

select b.zhspid,b.spgsh,Convert(decimal(10,2),sum(a.hsje)/sum(a.shl)) hshj 

from openrowset(‘SQLOLEDB‘,‘192.168.10.222‘; ‘SA‘; ‘zdyy‘,ksoa.dbo.pf_ckmx) a,spkfkzh b,

(select spid,rq,row_number() over (PARTITION BY spid order by rq desc,djbh desc ) row 

from openrowset(‘SQLOLEDB‘,‘192.168.10.222‘; ‘SA‘; ‘zdyy‘,ksoa.dbo.pf_ckmx) ) c

where a.djbh like ‘xsa%‘ and a.spid=b.spid and b.spgsh=‘运城‘ and a.hshj>0 and a.spid=c.spid

and c.row=1 and a.rq>=convert(varchar(10),dateadd(month,-1,c.rq),120) and a.rq<=c.rq

group by b.zhspid,b.spgsh

select spid,rq into #2 from 

(select spid,rq,row_number() over (PARTITION BY spid order by rq desc,djbh desc ) row 

from openrowset(‘SQLOLEDB‘, ‘192.168.0.100‘; ‘SA‘; ‘‘,st_ccerp.dbo.ywmxk)

)a

 where row=1

insert into gssj

select b.zhspid,b.spgsh,Convert(decimal(10,2),sum(a.hsje)/sum(a.shl)) hshj 

from openrowset(‘SQLOLEDB‘, ‘192.168.0.100‘; ‘SA‘; ‘‘,st_ccerp.dbo.ywmxk) a,spkfkzh b,#2 c

where a.djbh like ‘xsa%‘ and a.spid=b.spid and b.spgsh=‘大同‘ and a.hshj>0 and a.spid=c.spid

and a.rq>=convert(varchar(10),dateadd(month,-1,c.rq),120) and a.rq<=c.rq

group by b.zhspid,b.spgsh

select spid,rq into #1 from 

(select a.spid,a.rq,row_number() over (PARTITION BY a.spid order by a.rq desc,a.djbh desc ) row 

from openrowset(‘SQLOLEDB‘, ‘192.168.16.237‘; ‘SA‘; ‘zdyysm,.‘,ymksoa.dbo.pf_ckmx) a,openrowset(‘SQLOLEDB‘, ‘192.168.16.237‘; ‘SA‘; ‘zdyysm,.‘,ymksoa.dbo.pf_ckhz) b

where a.djbh=b.djbh and b.dwbh not in

(select dwbh from openrowset(‘SQLOLEDB‘, ‘192.168.16.237‘; ‘SA‘; ‘zdyysm,.‘,ymksoa.dbo.mchk) where dwmch like ‘山西振东医药物流有限公司‘) 

)c

 where row=1

insert into gssj

select b.zhspid,b.spgsh,Convert(decimal(10,2),sum(a.hsje)/sum(a.shl)) hshj 

from openrowset(‘SQLOLEDB‘,‘192.168.16.237‘; ‘SA‘; ‘zdyysm,.‘,ymksoa.dbo.pf_ckmx) a,spkfkzh b,#1 c,

openrowset(‘SQLOLEDB‘, ‘192.168.16.237‘; ‘SA‘; ‘zdyysm,.‘,ymksoa.dbo.pf_ckhz) d

where a.djbh like ‘xsa%‘ and a.spid=b.spid and b.spgsh=‘医贸‘ and a.hshj>0 and a.spid=c.spid

and a.rq>=convert(varchar(10),dateadd(month,-1,c.rq),120) and a.rq<=c.rq and a.djbh=d.djbh and d.dwbh not in

(select dwbh from openrowset(‘SQLOLEDB‘, ‘192.168.16.237‘; ‘SA‘; ‘zdyysm,.‘,ymksoa.dbo.mchk) where dwmch like ‘山西振东医药物流有限公司‘) 

group by b.zhspid,b.spgsh

各公司进价销价