select * from
(select ‘长治‘ as spgsh,b.rq,e.dwmch,a.spmch,a.shpgg,a.shengccj,a.dw,a.jlgg,b.rkdj,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
where a.spmch like ‘%头孢克肟%‘) a
where row=1
union all
select * from
(select ‘大同‘ as spgsh,b.rq,e.dwmch,a.spmch,a.shpgg,a.shengccj,a.dw,a.jlgg,b.rkdj,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
where a.spmch like ‘%头孢克肟%‘) a
where row=1
union all
select * from
(select ‘运城‘ as spgsh,b.rq,e.dwmch,a.spmch,a.shpgg,a.shengccj,a.dw,a.jlgg,b.rkdj,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
where a.spmch like ‘%头孢克肟%‘) a
where row=1
union all
select * from
(select ‘医贸‘ as spgsh,b.rq,e.dwmch,a.spmch,a.shpgg,a.shengccj,a.dw,a.jlgg,b.rkdj,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
where a.spmch like ‘%头孢克肟%‘) a
where row=1
原文：http://bks2015.blog.51cto.com/9847303/1762654