select * from 

(select '长治' as spgsh,isnull(b.rq,f.rq) rq,e.dwmch,replace(a.spmch,'*','') spmch,a.shpgg,a.shengccj,a.dw,a.jlgg,b.rkdj,f.hshj,c.shl,c.xshe,c.je,c.ml,d.kcshl,row_number() over (PARTITION BY b.spid order by b.rq desc) row

from spkfk_cz a

join splsk_cz b on b.djbh like 'jha%' and a.spid=b.spid   

left join (select spid,sum(chkshl) shl,sum(xshe) xshe,sum(xshje) je,sum(ml) ml from splsk_cz where djbh like 'xs%' and rq>='2015-01-01' and rq<='2015-12-31' group by spid) c on a.spid=c.spid 

left join spkfjc_cz d on a.spid=d.spid

left join mchk_cz e on b.dwbh=e.dwbh

left join sprkhshj f on f.spid=b.spid and f.spgsh='长治'

where a.spmch like '%头孢克肟%') a

where row=1

union all

select * from 

(select '大同' as spgsh,isnull(b.rq,f.rq) rq,e.dwmch,replace(a.spmch,'*','') spmch,a.shpgg,a.shengccj,a.dw,a.jlgg,b.rkdj,f.hshj,c.shl,c.xshe,c.je,c.ml,d.kcshl,row_number() over (PARTITION BY b.spid order by b.rq desc) row

from spkfk_dt a

join splsk_dt b on b.djbh like 'jha%' and a.spid=b.spid   

left join (select spid,sum(chkshl) shl,sum(xshe) xshe,sum(xshje) je,sum(ml) ml from splsk_dt where djbh like 'xs%' and rq>='2015-01-01' and rq<='2015-12-31' group by spid) c on a.spid=c.spid 

left join spkfjc_dt d on a.spid=d.spid

left join mchk_dt e on b.dwbh=e.dwbh

left join sprkhshj f on f.spid=b.spid and f.spgsh='大同'

where a.spmch like '%头孢克肟%') a

where row=1

union all

select * from 

(select '运城' as spgsh,isnull(b.rq,f.rq) rq,e.dwmch,replace(a.spmch,'*','') spmch,a.shpgg,a.shengccj,a.dw,a.jlgg,b.rkdj,f.hshj,c.shl,c.xshe,c.je,c.ml,d.kcshl,row_number() over (PARTITION BY b.spid order by b.rq desc) row

from spkfk_yc a

join splsk_yc b on b.djbh like 'jha%' and a.spid=b.spid   

left join (select spid,sum(chkshl) shl,sum(xshe) xshe,sum(xshje) je,sum(ml) ml from splsk_yc where djbh like 'xs%' and rq>='2015-01-01' and rq<='2015-12-31' group by spid) c on a.spid=c.spid 

left join spkfjc_yc d on a.spid=d.spid

left join mchk_yc e on b.dwbh=e.dwbh

left join sprkhshj f on f.spid=b.spid and f.spgsh='运城'

where a.spmch like '%头孢克肟%') a

where row=1

union all

select * from 

(select '医贸' as spgsh,isnull(b.rq,f.rq) rq,e.dwmch,replace(a.spmch,'*','') spmch,a.shpgg,a.shengccj,a.dw,a.jlgg,b.rkdj,f.hshj,c.shl,c.xshe,c.je,c.ml,d.kcshl,row_number() over (PARTITION BY b.spid order by b.rq desc) row

from openrowset('SQLOLEDB', '192.168.16.237'; 'SA'; 'zdyysm,.',ymksoa.dbo.spkfk) a

join openrowset('SQLOLEDB', '192.168.16.237'; 'SA'; 'zdyysm,.',ymksoa.dbo.splsk) b on b.djbh like 'jha%' and a.spid=b.spid   

left join (select spid,sum(chkshl) shl,sum(xshe) xshe,sum(xshje) je,sum(ml) ml from openrowset('SQLOLEDB', '192.168.16.237'; 'SA'; 'zdyysm,.',ymksoa.dbo.splsk) where djbh like 'xs%' and rq>='2015-01-01' and rq<='2015-12-31' group by spid) c on a.spid=c.spid 

left join openrowset('SQLOLEDB', '192.168.16.237'; 'SA'; 'zdyysm,.',ymksoa.dbo.spkfjc) d on a.spid=d.spid

left join openrowset('SQLOLEDB', '192.168.16.237'; 'SA'; 'zdyysm,.',ymksoa.dbo.mchk) e on b.dwbh=e.dwbh

left join sprkhshj f on f.spid=b.spid and f.spgsh='医贸'

where a.spmch like '%头孢克肟%') a

where row=1