--查询保健对象前10名药品 
select * from (
select  e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00 from yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2'
 group by e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00 order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'
 


--查询某个药品的前十位使用医生
 select YSXM00 医生姓名,YPBM00 药品编码,ypmc00 药品名称,ypgg00 规格,kcdw00 库存单位,ypsl00 数量,sccjzw 生产厂家 from (
select a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00,
(select sccjzw from bm_ypsccj h where h.sccjbh=e.sccjbh) sccjzw from 
yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2' 
 and e.ypnm00+0='44859'
 group by a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,e.sccjbh order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'

union all 

 select YSXM00 医生姓名,YPBM00 药品编码,ypmc00 药品名称,ypgg00 规格,kcdw00 库存单位,ypsl00 数量,sccjzw 生产厂家 from (
select a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00,
(select sccjzw from bm_ypsccj h where h.sccjbh=e.sccjbh) sccjzw from 
yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2' 
 and e.ypnm00+0='44099'
 group by a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,e.sccjbh order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'

union all 

 select YSXM00 医生姓名,YPBM00 药品编码,ypmc00 药品名称,ypgg00 规格,kcdw00 库存单位,ypsl00 数量,sccjzw 生产厂家 from (
select a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00,
(select sccjzw from bm_ypsccj h where h.sccjbh=e.sccjbh) sccjzw from 
yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2' 
 and e.ypnm00+0='45422'
 group by a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,e.sccjbh order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'

union all 

 select YSXM00 医生姓名,YPBM00 药品编码,ypmc00 药品名称,ypgg00 规格,kcdw00 库存单位,ypsl00 数量,sccjzw 生产厂家 from (
select a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00,
(select sccjzw from bm_ypsccj h where h.sccjbh=e.sccjbh) sccjzw from 
yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2' 
 and e.ypnm00+0='37885'
 group by a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,e.sccjbh order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'

union all 

 select YSXM00 医生姓名,YPBM00 药品编码,ypmc00 药品名称,ypgg00 规格,kcdw00 库存单位,ypsl00 数量,sccjzw 生产厂家 from (
select a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00,
(select sccjzw from bm_ypsccj h where h.sccjbh=e.sccjbh) sccjzw from 
yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2' 
 and e.ypnm00+0='2885'
 group by a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,e.sccjbh order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'

union all 

 select YSXM00 医生姓名,YPBM00 药品编码,ypmc00 药品名称,ypgg00 规格,kcdw00 库存单位,ypsl00 数量,sccjzw 生产厂家 from (
select a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00,
(select sccjzw from bm_ypsccj h where h.sccjbh=e.sccjbh) sccjzw from 
yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2' 
 and e.ypnm00+0='43883'
 group by a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,e.sccjbh order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'

union all 
 select YSXM00 医生姓名,YPBM00 药品编码,ypmc00 药品名称,ypgg00 规格,kcdw00 库存单位,ypsl00 数量,sccjzw 生产厂家 from (
select a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00,
(select sccjzw from bm_ypsccj h where h.sccjbh=e.sccjbh) sccjzw from 
yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2' 
 and e.ypnm00+0='553'
 group by a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,e.sccjbh order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'

union all 
 select YSXM00 医生姓名,YPBM00 药品编码,ypmc00 药品名称,ypgg00 规格,kcdw00 库存单位,ypsl00 数量,sccjzw 生产厂家 from (
select a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00,
(select sccjzw from bm_ypsccj h where h.sccjbh=e.sccjbh) sccjzw from 
yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2' 
 and e.ypnm00+0='49453'
 group by a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,e.sccjbh order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'

union all 
 select YSXM00 医生姓名,YPBM00 药品编码,ypmc00 药品名称,ypgg00 规格,kcdw00 库存单位,ypsl00 数量,sccjzw 生产厂家 from (
select a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00,
(select sccjzw from bm_ypsccj h where h.sccjbh=e.sccjbh) sccjzw from 
yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2' 
 and e.ypnm00+0='41679'
 group by a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,e.sccjbh order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'

union all 
 select YSXM00 医生姓名,YPBM00 药品编码,ypmc00 药品名称,ypgg00 规格,kcdw00 库存单位,ypsl00 数量,sccjzw 生产厂家 from (
select a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) ypsl00,
(select sccjzw from bm_ypsccj h where h.sccjbh=e.sccjbh) sccjzw from 
yf_mzcf00 a,yf_mzcfmx b,sf_brfy00 c,SF_JZB000 d,bm_yd0000 e where a.cflsh0=b.cflsh0 and a.ghid00=c.mzid00 
and a.djh000=c.djh000 and c.jzdh00=d.jzdh00 and
 b.ypnm00=e.ypnm00 and b.sfzby0='N' AND d.JZRQ00 BETWEEN '20210101' AND '20210831' and d.yblb00 in ('3','4') and a.ypdlbh<>'2' 
 and e.ypnm00+0='41880'
 group by a.ysgzh0,a.ysxm00,e.ypnm00,e.ypbm00,e.ypmc00,e.ypgg00,e.kcdw00,e.sccjbh order by round(sum(b.ypzsl0/e.k2j000*b.zhl000),2) desc ) where rownum<='10'