sql语句

--可以入径的病人

select f.brxm,f.brxb,f.brks,f.jzhm,f.brbh, case when ryzd is not null then
(select jbmc from gy_jbbm where jbxh= ryzd )else (select jbmc from gy_jbbm where jbxh=cbzd) end zddm ,
f.zdys,f.ryrq,f.cyrq,(to_date(to_char(cyrq,'yyyy-mm-dd'),'yyyy-mm-dd') - to_date(to_char(ryrq,'yyyy-mm-dd'),'yyyy-mm-dd')),
( select sum(zjje) from v_zy_fymx where zyh= f.jzhm )
from (
select e.brxm,e.brxb,e.brks,e.jzhm,e.brbh,sum(e.ryzd) as ryzd,sum(e.cbzd) as cbzd,e.zdys,e.ryrq,e.cyrq from (
select brxm,brxb,brks,jzhm,brbh,'' ryzd,jbdm as cbzd,zdys,c.ryrq,c.cyrq
from ys_zy_jbzd a,gy_jbbm b ,zy_brry c
where a.jbdm=b.jbxh and a.jzhm=c.zyh and znxh=1 and tjbz=1 and zfbz=0 and jbzh=1 and
ryrq between to_date('2017-07-01', 'yyyy-mm-dd') and to_date('2017-07-31', 'yyyy-mm-dd')and
(zdlb='初步诊断' ) and icd9 in (select glbm from emr_bzgl )
union all
select brxm,brxb,brks,jzhm,brbh,jbdm as ryzd,'' as cbzd,zdys,c.ryrq,c.cyrq
from ys_zy_jbzd a,gy_jbbm b ,zy_brry c
where a.jbdm=b.jbxh and a.jzhm=c.zyh and znxh=1 and tjbz=1 and zfbz=0 and jbzh=1 and
ryrq between to_date('2017-07-01', 'yyyy-mm-dd') and to_date('2017-07-31', 'yyyy-mm-dd')and
(zdlb='入院诊断' ) and icd9 in (select glbm from emr_bzgl )
) e group by e.brxm,e.brxb,e.brks,e.jzhm,e.brbh,e.zdys,e.ryrq,e.cyrq ) f

--

 已入径

select ( select zyhm from zy_brry where zyh= a.jzxh) 住院号,a.brxm 姓名,（select ksmc from gy_ksdm where ksdm=a.brks ）病人科室,
(select ygxm from gy_ygdm where ygdm=a.zzys) 主治医生,jbmc 疾病名称,c.ryrq 入院日期,c.cyrq 出院日期,
(to_date(to_char(c.cyrq,'yyyy-mm-dd'),'yyyy-mm-dd') - to_date(to_char(c.ryrq,'yyyy-mm-dd'),'yyyy-mm-dd')) 住院天数,
( select sum(zjje) from v_zy_fymx where zyh= a.jzxh ) 费用
from cp_brlj a,gy_jbbm b ,zy_brry c where a.dyzd=b.icd9 and c.zyh=a.jzxh
and c.ryrq between to_date('2018-01-01', 'yyyy-mm-dd') and to_date('2018-06-30', 'yyyy-mm-dd') order by a.brks,a.jzxh,c.ryrq

---------------------------------------------------------------

统计器械科对手术室的出库单，

SELECT WL_CK01.KFXH,
WL_CK01.ZBLB,
WL_CK01.LZDH,
WL_CK01.LZFS,
WL_CK01.KSDM,
WL_CK01.CKRQ,
WL_CK01.ZDGH,
WL_CK01.SHGH,
WL_CK01.JZGH,
WL_CK01.DJBZ,
WL_CK01.DJXH,
WL_CK01.JBGH,
WL_CK01.DJJE,
WL_CK01.DJZT,
WL_CK01.THDJ,
WL_CK01.DJLX,
WL_CK01.JGID,
WL_CK01.CKFS,
WL_LZFS.FSMC,
WL_ZBLB.ZBMC
FROM WL_CK01,WL_LZFS,WL_ZBLB
WHERE ( WL_CK01.KFXH =48 ) AND ksdm=235 and to_char(ckrq,'yyyy-mm')='2018-05' and
( WL_CK01.ZBLB = WL_ZBLB.ZBLB ) AND
( WL_CK01.LZFS = WL_LZFS.FSXH ) AND
WL_CK01.DJZT = 2
ORDER BY WL_CK01.CKRQ ASC

 

 -===========================================

select (
SELECT sum(djje)
FROM WL_CK01,WL_LZFS,WL_ZBLB
WHERE ( WL_CK01.KFXH =48 ) AND ksdm=235 and to_char(ckrq,'yyyy-mm')='2018-05' and
( WL_CK01.ZBLB = WL_ZBLB.ZBLB ) AND
( WL_CK01.LZFS = WL_LZFS.FSXH ) AND
WL_CK01.DJZT = 2 and thdj = 0
) -(
SELECT sum(djje)
FROM WL_CK01,WL_LZFS,WL_ZBLB
WHERE ( WL_CK01.KFXH =48 ) AND ksdm=235 and to_char(ckrq,'yyyy-mm')='2018-05' and
( WL_CK01.ZBLB = WL_ZBLB.ZBLB ) AND
( WL_CK01.LZFS = WL_LZFS.FSXH ) AND
WL_CK01.DJZT = 2 and thdj<>0

) from dual

 上面是手术室（单机）的单机的申领明细，

------------------------------------------------------------------

注意(0 - sum(a.wzje * a.ywlb)) as lywc 这种写法，在表里面钱都是正的，但是系数是+1或者-1的差别

select b.hsks,mxxh,(0 - sum(a.wzje * a.ywlb)) as lywc,sum(wzje) , (0 - sum(a.wzje * a.ywlb)) - sum(wzje) from wl_zcmx a,gy_ksdm b
where a.ksdm=b.ksdm
and a.kfxh=48
and a.fsrq>to_date('2018-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.fsrq<to_date('2018-05-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
　 and b.hsks =38 having (0 - sum(a.wzje * a.ywlb)) - sum(wzje)<>0
group by hsks　,mxxh

 

------------------------------------------------------------------------------

 

 

 

 

 

 

 

--表3

select 开嘱日期,药品通用名称,剂型,规格,计量单位,计数单位,sum(门诊用量),sum(住院用量),sum(出院带药用量) from 
(select to_char(kzsj,'yyyy-mm') as 开嘱日期,
yzmc as 药品通用名称,
(select sxmc from YK_YPSX where YK_YPSX.ypsx = c.YPSX) as 剂型,
(select bfgg from yk_typk where yk_typk.ypxh = c.YPXH) as 规格,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计量单位,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计数单位,
0 as 门诊用量,
count(*) as 住院用量,
0 as 出院带药用量

from emr_yzb,v_amqc_kjyw c where ypcd > 0 and emr_yzb.xmid = c.YPXH 
group by to_char(kzsj,'yyyy-mm'),yzmc,YPSX,YPXH
union all
select to_char(kzsj,'yyyy-mm') as 开嘱日期,
yzmc as 药品通用名称,
(select sxmc from YK_YPSX where YK_YPSX.ypsx = c.YPSX) as 剂型,
(select bfgg from yk_typk where yk_typk.ypxh = c.YPXH) as 规格,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计量单位,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计数单位,
0 as 门诊用量,
0 as 住院用量,
count(*) as 出院带药用量

from emr_yzb,v_amqc_kjyw c where ypcd > 0 and emr_yzb.xmid = c.YPXH and emr_yzb.yysx = 4
group by to_char(kzsj,'yyyy-mm'),yzmc,YPSX,YPXH
union all

select to_char(kfrq,'yyyy-mm') as 开嘱日期,
ypmc as 药品通用名称,
(select sxmc from YK_YPSX where YK_YPSX.ypsx = c.YPSX) as 剂型,
(select bfgg from yk_typk where yk_typk.ypxh = c.YPXH) as 规格,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计量单位,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计数单位,
count(*) as 门诊用量,
0 as 住院用量,
0 as 出院带药用量

from ms_cf02,v_amqc_kjyw c,ms_cf01 where ms_cf02.ypxh = c.YPXH and ms_cf01.cfsb = ms_cf02.cfsb
group by to_char(kfrq,'yyyy-mm'),ypmc,YPSX,c.YPXH) a group by 开嘱日期,药品通用名称,剂型,规格,计量单位,计数单位
order by 开嘱日期

 

select rq as 日期,ypmc as 药品名称,sxmc as 剂型,ypgg as 药品规格,yfdw as 计量单位,yfbz as 计数单位,sum(mz) as 门诊量 ,sum(zy)as 住院量,sum(cy) as 出院带药 ,sum(mz+zy) as 总计
from (
select '' as rq,b.ypmc ,c.sxmc,b.ypgg,a.yfdw ,a.yfbz ,sum(a.ypsl) as mz,0 as zy, 0 as cy from yf_mzfymx a, yk_typk b,yk_ypsx c
where a.ypxh=b.ypxh
and b.ypsx=c.ypsx
and b.ksbz=1
and a.fyrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
and a.fyrq<to_date('2017-07-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
group by b.ypmc ,c.sxmc,b.ypgg,a.yfdw ,a.yfbz
union all
select '' as rq,b.ypmc,c.sxmc,b.ypgg,a.yfdw,a.yfbz,0 as mz,sum(a.ypsl) as zy,0 as cy from yf_zyfymx a, yk_typk b,yk_ypsx c
where a.ypxh=b.ypxh
and b.ypsx=c.ypsx
and b.ksbz=1
and jfrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
and jfrq<to_date('2017-07-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
group by b.ypmc ,c.sxmc,b.ypgg,a.yfdw ,a.yfbz
union all
select '' as rq,b.ypmc,c.sxmc,b.ypgg,a.yfdw,a.yfbz,0 as mz,0 as zy,sum(a.ypsl) as cy from yf_zyfymx a, yk_typk b,yk_ypsx c
where a.ypxh=b.ypxh
and b.ypsx=c.ypsx
and b.ksbz=1
and jfrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
and jfrq<to_date('2017-07-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
and a.fylx=3
group by b.ypmc ,c.sxmc,b.ypgg,a.yfdw ,a.yfbz) group by rq,ypmc,sxmc,ypgg,yfdw,yfbz
order by rq,ypmc,sxmc,ypgg,yfdw,yfbz

----------------------------------------------------------------------------------------------

select 药品通用名称,剂型,规格,计量单位,计数单位,sum(门诊用量),sum(住院用量),sum(出院带药用量) from 
(select 
yzmc as 药品通用名称,
(select sxmc from YK_YPSX where YK_YPSX.ypsx = c.YPSX) as 剂型,
(select bfgg from yk_typk where yk_typk.ypxh = c.YPXH) as 规格,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计量单位,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计数单位,
0 as 门诊用量,
count(*) as 住院用量,
0 as 出院带药用量

from emr_yzb,v_amqc_kjyw c where ypcd > 0 and emr_yzb.xmid = c.YPXH 
group by yzmc,YPSX,YPXH
union all
select 
yzmc as 药品通用名称,
(select sxmc from YK_YPSX where YK_YPSX.ypsx = c.YPSX) as 剂型,
(select bfgg from yk_typk where yk_typk.ypxh = c.YPXH) as 规格,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计量单位,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计数单位,
0 as 门诊用量,
0 as 住院用量,
count(*) as 出院带药用量

from emr_yzb,v_amqc_kjyw c where ypcd > 0 and emr_yzb.xmid = c.YPXH and emr_yzb.yysx = 4
group by yzmc,YPSX,YPXH
union all

select 
ypmc as 药品通用名称,
(select sxmc from YK_YPSX where YK_YPSX.ypsx = c.YPSX) as 剂型,
(select bfgg from yk_typk where yk_typk.ypxh = c.YPXH) as 规格,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计量单位,
(select jldw from yk_typk where yk_typk.ypxh = c.YPXH) as 计数单位,
count(*) as 门诊用量,
0 as 住院用量,
0 as 出院带药用量

from ms_cf02,v_amqc_kjyw c where ms_cf02.ypxh = c.YPXH 
group by ypmc,YPSX,c.YPXH) a group by 药品通用名称,剂型,规格,计量单位,计数单位

---用这个统计一类切开手术
select distinct a.brbh as 患者病历号,a.brxb as 性别,a.rynl as 年龄,a.ryrq as 入院日期,a.cyrq as 出院日期 ,
(select count(distinct emr_yzb.yfyy) from emr_yzb where emr_yzb.zyh=a.jzhm ) as 是否预防0为否大于0为是,
c.pz as 品种,
c.lc as 疗程
from ys_zy_jzjl a,sm_sssq b,v_amqc_kjyw_lc c,gy_ssdm d
where a.jzhm=b.zyh
and a.jzhm=c.zyh
and b.tjbz=1
and b.apbz=1
and b.zfbz=0
and a.cyrq>to_date('2014-05-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
and a.cyrq<to_date('2014-05-31 23:59:59','yyyy-mm-dd hh24:mi;ss')
and b.ssnm = d.ssnm and substr(ssdm,0,5) >'53.0' and substr(ssdm,0,5) <'53.1'

--------------------------------------------------------------------------------------------------------------------------------------------

 

----------------------------------------

--4.1

 

select count（distinct zyh） as b from zy_brry ---一类切口手术
where cyrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cyrq<to_date('2017-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cypb<>99
and exists（select 1 from sm_sssq where zy_brry.zyh=sm_sssq.zyh and
qkdj=1 and tjbz=1 and zfbz=0 and apbz=1
）

 ---------------------------------------------------------

---4.11一类切口手术

select count（distinct a.zyh） as b from zy_brry a,sm_ssjl b
where cyrq>to_date('2016-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cyrq<to_date('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cypb<>99
and a.zyh=b.zyh
and b.qkdj=1
and exists（select 1 from sm_sssq where a.zyh=sm_sssq.zyh and
qkdj=1 and tjbz=1 and zfbz=0 and apbz=1）
and exists (select 1 from emr_Yzb where sypc in ('sq','sq1','qpq30') and emr_Yzb.zyh=a.zyh )

-----------------------------------

-

----表5-1-1 总人数

---总手数人数
select zyhm from zy_brry where zyh in(
SELECT distinct(zyh) FROM SM_SSAP WHERE SSRQ >= TO_DAte('2016-10-01','yyyy-mm-dd') and
ssrq < to_date('2017-01-01','yyyy-mm-dd') and
ssnm in(select ssnm from gy_ssdm where ssdm like '01.24%' or ssdm like '01.39%'--and ssdm < '36.18'
)
and zfbz = 0 and wcbz = 1
group by zyh ) ;

---使用预防用药人数
select count(distinct(zyh)) from v_zy_bqyz,yk_ypml where yk_ypml.ypxh = v_zy_bqyz.YPXH and v_zy_bqyz.YPLX >0 and
zyh in(SELECT zyh FROM SM_SSAP WHERE SSRQ >= TO_DAte('2016-10-01','yyyy-mm-dd') and
ssrq < to_date('2017-10-01','yyyy-mm-dd') and wcbz = 1 and zfbz = 0 and
ssnm in(select ssnm from gy_ssdm where (ssdm like '01.24%' or ssdm like '01.39%')--and ssdm < '81.55' -- or ssdm like '53.1%'
)
group by zyh) and ( yfyy = 1 or ypyf = 36) and ypyf <> 27 and ksbz >0

 

----表5-1-2-X明细
SELECT distinct b.bahm AS 患者病历号,
CASE WHEN b.brxb = 1 THEN '男' WHEN b.brxb = 2 THEN '女' end AS 性别,
round((SYSDATE - b.csny)/365) AS 年龄,
b.ryrq AS 入院日期,
b.cyrq AS 出院日期,

CASE WHEN (SELECT bb.pzs FROM ((SELECT aaa.zyh AS zyh,SUM(aaa.yysj) AS yysj,COUNT(1) AS pzs FROM (
SELECT aa.zyh AS zyh,aa.ypxh AS ypxh,aa.ypmc,SUM(aa.yysj) AS yysj FROM
(SELECT c.zyh AS zyh,d.ypxh AS ypxh,d.ypmc AS ypmc,c.TZSj AS tzsj,c.KSSJ AS kssj,
round(c.TZSj - c.KSSJ)+1 AS yysj
FROM v_zy_bqyz c,yk_ypml d
WHERE c.ypxh = d.ypxh
AND c.YPLX > 0
AND c.zyh IN
(SELECT zyh
FROM SM_SSAP
WHERE SSRQ >= TO_DAte('2016-10-01','yyyy-mm-dd')
AND ssrq < to_date('2017-10-01','yyyy-mm-dd')
and zfbz = 0 and wcbz = 1
AND ssnm in(select ssnm from gy_ssdm WHERE ssdm LIKE '%01.24%' OR ssdm LIKE '%01.39%'))--ssdm >= '35.0' and ssdm < '35.3'))
and ( c.yfyy = 1 or c.ypyf = 36) and c.ypyf <> 27 AND d.ksbz >0
GROUP BY c.zyh,d.ypxh,d.ypmc,c.TZSj,c.KSSJ
ORDER BY c.zyh) aa
GROUP BY aa.zyh,aa.ypxh,aa.ypmc) aaa
GROUP BY aaa.zyh)) bb WHERE bb.zyh = a.zyh) > 0 THEN '是' ELSE '否' END AS 是否预防用药,

(SELECT bb.pzs FROM ((SELECT aaa.zyh AS zyh,SUM(aaa.yysj) AS yysj,COUNT(1) AS pzs FROM (
SELECT aa.zyh AS zyh,aa.ypxh AS ypxh,aa.ypmc,SUM(aa.yysj) AS yysj FROM
(SELECT c.zyh AS zyh,d.ypxh AS ypxh,d.ypmc AS ypmc,c.TZSj AS tzsj,c.KSSJ AS kssj,
round(c.TZSj - c.KSSJ)+1 AS yysj
FROM v_zy_bqyz c,yk_ypml d
WHERE c.ypxh = d.ypxh
AND c.YPLX > 0
AND c.zyh IN
(SELECT zyh
FROM SM_SSAP
WHERE SSRQ >= TO_DAte('2016-10-01','yyyy-mm-dd')
AND ssrq < to_date('2017-10-01','yyyy-mm-dd')
and zfbz = 0 and wcbz = 1
AND ssnm in(select ssnm from gy_ssdm where ssdm LIKE '%01.24%' OR ssdm LIKE '%01.39%'))--ssdm >= '81.51' and ssdm < '81.52'))
and ( c.yfyy = 1 or c.ypyf = 36) and c.ypyf <> 27 AND d.ksbz >0
GROUP BY c.zyh,d.ypxh,d.ypmc,c.TZSj,c.KSSJ
ORDER BY c.zyh) aa
GROUP BY aa.zyh,aa.ypxh,aa.ypmc) aaa
GROUP BY aaa.zyh)) bb WHERE bb.zyh = a.zyh) AS 品种数,

(SELECT bb.yysj FROM ((SELECT aaa.zyh AS zyh,SUM(aaa.yysj) AS yysj,COUNT(1) AS pzs FROM (
SELECT aa.zyh AS zyh,aa.ypxh AS ypxh,aa.ypmc,SUM(aa.yysj) AS yysj FROM
(SELECT c.zyh AS zyh,d.ypxh AS ypxh,d.ypmc AS ypmc,c.TZSj AS tzsj,c.KSSJ AS kssj,
round(c.TZSj - c.KSSJ)+1 AS yysj
FROM v_zy_bqyz c,yk_ypml d
WHERE c.ypxh = d.ypxh
AND c.YPLX > 0
AND c.zyh IN
(SELECT zyh
FROM SM_SSAP
WHERE SSRQ >= TO_DAte('2016-10-01','yyyy-mm-dd')
AND ssrq < to_date('2017-10-01','yyyy-mm-dd')
and zfbz = 0 and wcbz = 1
AND ssnm in(select ssnm from gy_ssdm where ssdm LIKE '%01.24%' OR ssdm LIKE '%01.39%'))--ssdm >= '81.51' and ssdm < '81.52'))
and ( c.yfyy = 1 or c.ypyf = 36) and c.ypyf <> 27 AND d.ksbz >0
GROUP BY c.zyh,d.ypxh,d.ypmc,c.TZSj,c.KSSJ
ORDER BY c.zyh) aa
GROUP BY aa.zyh,aa.ypxh,aa.ypmc) aaa
GROUP BY aaa.zyh)) bb WHERE bb.zyh = a.zyh) AS 用药时间 ,

'' AS 备注
FROM SM_SSAP a,zy_brry b
WHERE a.zyh = b.zyh
AND a.ssrq >=to_date('2016-10-01','yyyy-mm-dd')
AND a.ssrq <to_date('2017-10-01','yyyy-mm-dd')
and a.zfbz = 0 and a.wcbz = 1
AND a.ssnm in(select ssnm from gy_ssdm where ssdm LIKE '%01.24%' OR ssdm LIKE '%01.39%' )--ssdm >= '81.51' and ssdm < '81.52');

ORDER BY b.bahm

 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 --统计做手术、医嘱里面带“髋”和“股骨头”的耗材大于200和耗材费大于10000的

 select 

C.KSMC as 费用科室,b.brxm,b.zyhm,
SUM(ZJJE) as 总费用,
sum(case when ( a.FYDJ > 200  and a.fyxm in (48,69) )then a.zjje else 0  end )  as  大于200卫生材料  ,
sum(case when (a.fydj > 10000  and a.fyxm in (48,69) ) then a.zjje else 0  end )  as  大于1000卫生材料
from v_zy_fymx a,zy_brry b,gy_ksdm c,gy_ksdm d,gy_ksdm e
where A.ZYH=B.ZYH
AND   A.FYKS=c.KSDM
AND   A.FYBQ=d.KSDM
and   b.brks=e.ksdm
AND   B.CYRQ>TO_DATE('2017-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND   B.CYRQ<TO_DATE('2017-06-30 00:00:00','YYYY-MM-DD HH24:MI:SS')
and   b.cypb<>99
and B.ZYH IN (select ZYH  from emr_yzb where  yzmc like '%拟%髋%'  or  yzmc like '%股骨头%'  
)
GROUP BY  C.KSMC,b.brxm,b.zyhm
order by  C.KSMC,b.brxm,b.zyhm

-----------------------------------------------------------------------------------------------------------------

 

--1.5门诊患者就诊使用抗菌药物累及品种数
select sum(pz)from (
select c.ksmc,count(distinct b.ypxh) as pz  from ms_cf01 a,ms_cf02 b ,gy_ksdm c
where  a.cfsb=b.cfsb and a.fybz=1 and a.zfpb=0 and a.fphm is not null and b.ypxh in (select ypxh from yk_typk where ksbz=1 and zfpb=0) 
and  a.ksdm=c.ksdm
and fyrq>to_date('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and fyrq<to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and  a.ksdm<>109
group by c.ksmc )

--2.5急诊患者就诊使用抗菌药物累及品种数
select sum(pz)from (
select c.ksmc,count(distinct b.ypxh) as pz  from ms_cf01 a,ms_cf02 b ,gy_ksdm c
where  a.cfsb=b.cfsb and a.fybz=1 and a.zfpb=0 and a.fphm is not null and b.ypxh in (select ypxh from yk_typk where ksbz=1 and zfpb=0) 
and  a.ksdm=c.ksdm
and fyrq>to_date('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and fyrq<to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.ksdm=109
group by c.ksmc )


--3.9联合抗菌药物人数
select count(distinct EMR_YZB.zyh) as kjywrs
from EMR_YZB,yk_typk,zy_brry 
where emr_yzb.zyh = zy_brry.zyh and yk_typk.YPXH = EMR_YZB.xmid and EMR_YZB.ZFPB = 0 
and  yk_typk.KSBZ = 1 AND ZY_BRRY.CYPB < 99 
and  ZY_BRRY.CYRQ >= to_date('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and  ZY_BRRY.CYRQ <= to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and EMR_YZB.xmlb = 1 and EMR_YZB.kjywlh in(2,3)



--3.10出院患者使用抗菌药物做病原学检查
select COUNT(distinct V_AMQC_ZY_BRRY.zyh)as yxym
FROM GY_KSDM,V_AMQC_ZY_BRRY ,v_amqc_zy_bqyz where  V_AMQC_ZY_BRRY.zyh  in 
   (  select distinct a.zyh from v_zy_fymx a,zy_brry b,v_amqc_kjyw c
      where a.zyh=b.zyh
      and   b.cyrq>= to_date('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
      and   b.cyrq<= to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
      and   a.YPCD>0
      and   a.FYXH =c.ypxh 
   )
AND   ( V_AMQC_ZY_BRRY.ZYH = v_amqc_zy_bqyz.ZYH ) 
and   (V_AMQC_ZY_BRRY.BRKS = GY_KSDM.KSDM )
and   v_amqc_zy_bqyz.ypxh in (1131,5731,5727,5728) 
--- 4.3
select sum (a) from (
select count（distinct zyh） as a from zy_brry ---白内障手术
where cyrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cyrq<to_date('2017-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cypb<>99
and exists (select 1 from emr_yzb where emr_yzb.zyh=zy_brry.zyh
and （ yzmc like '%拟%白内障%'） )
union all
select count（distinct zyh） as b from zy_brry ---一类切口手术
where cyrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cyrq<to_date('2017-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cypb<>99
and exists（select 1 from sm_sssq where zy_brry.zyh=sm_sssq.zyh and qkdj=1 and tjbz=1 and zfbz=0 and apbz=1
and (nssmc like '%甲状腺%' or nssmc like '%乳腺%'or nssmc like '%腹股沟%疝%'or nssmc like '%关节镜%'or nssmc like '%颈动脉%'or nssmc like '%颅骨%肿物%')）
)
---4.4
select sum (a) from (
select count（distinct a. zyh） as a from zy_brry a,v_zy_fymx b ---白内障手术
where cyrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cyrq<to_date('2017-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.zyh=b.zyh
and exists (select 1 from yk_typk where ksbz=1 and ypxh=b.fyxh )
and cypb<>99
and exists (select 1 from emr_yzb where emr_yzb.zyh=a.zyh and （ yzmc like '%拟%白内障%'） )
union all
select count（distinct a.zyh） as b from zy_brry a,v_zy_fymx b ---一类切口手术
where cyrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cyrq<to_date('2017-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.zyh=b.zyh
and exists (select 1 from yk_typk where ksbz=1 and ypxh=b.fyxh )
and cypb<>99
and exists（select 1 from sm_sssq where a.zyh=sm_sssq.zyh and qkdj=1 and tjbz=1 and zfbz=0 and apbz=1
and (nssmc like '%甲状腺%' or nssmc like '%乳腺%'or nssmc like '%腹股沟%疝%'or nssmc like '%关节镜%'or nssmc like '%颈动脉%'or nssmc like '%颅骨%肿物%')）
)

 

--4.5不合理人数
select count(distinct a.zyh) from zy_brry a ,sm_ssjl b,v_zy_fymx c --分子
where a.zyh=b.zyh
and a.zyh=c.zyh
and a.cyrq>=to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.cyrq<=to_date('2017-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.cypb<>99
and b.qkdj=1
and exists (select 1 from yk_typk where ksbz=1 and yfyysx is null and ypxh=c.fyxh )
and exists (select 1 from emr_Yzb where yfyy=1 and emr_Yzb.zyh=a.zyh )

select count(distinct a.zyh) from zy_brry a ,sm_ssjl b,v_zy_fymx c --分母
where a.zyh=b.zyh
and a.zyh=c.zyh
and a.cyrq>=to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.cyrq<=to_date('2017-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.cypb<>99
and b.qkdj=1
and exists (select 1 from yk_typk where ksbz=1 and ypxh=c.fyxh )
and exists (select 1 from emr_Yzb where yfyy=1 and emr_Yzb.zyh=a.zyh )

 

--4.6 一类切口手术预防 使用抗菌药物小于24小时
select  count(distinct a.zyh)  from  
(select  distinct a.zyh  from v_zy_fymx a,zy_brry b,v_amqc_kjyw c
      where a.zyh=b.zyh
      and   b.cyrq>= to_date('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
      and   b.cyrq<= to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
      and   a.YPCD>0
      and   a.FYXH =c.ypxh 
      and  exists (select 1 from sm_ssjl where sm_ssjl.zyh=b.zyh  and qkdj=1)) a,v_amqc_kjyw_lc  b
      where a.zyh=b.zyh
      and   b.lc<=1
      
--4.7出院患者使用抗菌药物手术人次
select count(distinct a.zyh) from v_zy_fymx a,zy_brry b,v_amqc_kjyw c
      where a.zyh=b.zyh
      and   b.cyrq>= to_date('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
      and   b.cyrq<= to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
      and   a.YPCD>0
      and   a.FYXH =c.ypxh 
      and  exists (select 1 from sm_ssjl where sm_ssjl.zyh=b.zyh )

--4.8

select count(distinct a.zyh) from zy_brry a ,sm_ssjl b,v_zy_fymx c -----分子
where a.zyh=b.zyh
and a.zyh=c.zyh
and a.cyrq>=to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.cyrq<=to_date('2017-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.cypb<>99
and exists (select 1 from yk_typk where ksbz=1 and ypxh=c.fyxh )
and exists (select 1 from emr_Yzb where yfyy=1 and sypc in ('sq','sq1','qpq30') and emr_Yzb.zyh=a.zyh )

select count(distinct a.zyh) from zy_brry a ,sm_ssjl b,v_zy_fymx c ---分母
where a.zyh=b.zyh
and a.zyh=c.zyh
and a.cyrq>=to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.cyrq<=to_date('2017-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.cypb<>99
and exists (select 1 from yk_typk where ksbz=1 and ypxh=c.fyxh )
and exists (select 1 from emr_Yzb where yfyy=1 and emr_Yzb.zyh=a.zyh )

 

 

 

 

 

-----------------------------------------------------------------------------------------------------

----表5-1-2-X明细
SELECT distinct b.bahm AS 患者病历号,
CASE WHEN b.brxb = 1 THEN '男' WHEN b.brxb = 2 THEN '女' end AS 性别,
round((SYSDATE - b.csny)/365) AS 年龄,
b.ryrq AS 入院日期,
b.cyrq AS 出院日期,
CASE WHEN (SELECT bb.pzs FROM ((SELECT aaa.zyh AS zyh,SUM(aaa.yysj) AS yysj,COUNT(1) AS pzs FROM (
SELECT aa.zyh AS zyh,aa.ypxh AS ypxh,aa.ypmc,SUM(aa.yysj) AS yysj FROM
(SELECT c.zyh AS zyh,d.ypxh AS ypxh,d.ypmc AS ypmc,c.TZSj AS tzsj,c.KSSJ AS kssj,
round(c.TZSj - c.KSSJ)+1 AS yysj
FROM v_zy_bqyz c,yk_typk d
WHERE c.ypxh = d.ypxh
AND c.YPLX > 0
AND c.zyh IN
(SELECT zyh
FROM SM_SSAP
WHERE SSRQ >= TO_DAte('2016-10-01','yyyy-mm-dd')
AND ssrq < to_date('2017-10-01','yyyy-mm-dd')
and zfbz = 0 and wcbz = 1
AND ssnm in(select ssnm from gy_ssdm where ssdm >= '13.1' and ssdm <= '13.6'))--ssdm >= '35.0' and ssdm < '35.3'))
and ( c.yfyy = 1 or c.ypyf = 36) and c.ypyf <> 27 AND d.ksbz >0
GROUP BY c.zyh,d.ypxh,d.ypmc,c.TZSj,c.KSSJ
ORDER BY c.zyh) aa
GROUP BY aa.zyh,aa.ypxh,aa.ypmc) aaa
GROUP BY aaa.zyh)) bb WHERE bb.zyh = a.zyh) > 0 THEN '是' ELSE '否' END AS 是否预防用药,

(SELECT bb.pzs FROM ((SELECT aaa.zyh AS zyh,SUM(aaa.yysj) AS yysj,COUNT(1) AS pzs FROM (
SELECT aa.zyh AS zyh,aa.ypxh AS ypxh,aa.ypmc,SUM(aa.yysj) AS yysj FROM
(SELECT c.zyh AS zyh,d.ypxh AS ypxh,d.ypmc AS ypmc,c.TZSj AS tzsj,c.KSSJ AS kssj,
round(c.TZSj - c.KSSJ)+1 AS yysj
FROM v_zy_bqyz c,yk_typk d
WHERE c.ypxh = d.ypxh
AND c.YPLX > 0
AND c.zyh IN
(SELECT zyh
FROM SM_SSAP
WHERE SSRQ >= TO_DAte('2016-10-01','yyyy-mm-dd')
AND ssrq < to_date('2017-10-01','yyyy-mm-dd')
and zfbz = 0 and wcbz = 1
AND ssnm in(select ssnm from gy_ssdm where ssdm like '%01.24%' or ssdm like '%01.39%'))--ssdm >= '81.51' and ssdm < '81.52'))
and ( c.yfyy = 1 or c.ypyf = 36) and c.ypyf <> 27 AND d.ksbz >0
GROUP BY c.zyh,d.ypxh,d.ypmc,c.TZSj,c.KSSJ
ORDER BY c.zyh) aa
GROUP BY aa.zyh,aa.ypxh,aa.ypmc) aaa
GROUP BY aaa.zyh)) bb WHERE bb.zyh = a.zyh) AS 品种数,

(SELECT bb.yysj FROM ((SELECT aaa.zyh AS zyh,SUM(aaa.yysj) AS yysj,COUNT(1) AS pzs FROM (
SELECT aa.zyh AS zyh,aa.ypxh AS ypxh,aa.ypmc,SUM(aa.yysj) AS yysj FROM
(SELECT c.zyh AS zyh,d.ypxh AS ypxh,d.ypmc AS ypmc,c.TZSj AS tzsj,c.KSSJ AS kssj,
round(c.TZSj - c.KSSJ)+1 AS yysj
FROM v_zy_bqyz c,yk_typk d
WHERE c.ypxh = d.ypxh
AND c.YPLX > 0
AND c.zyh IN
(SELECT zyh
FROM SM_SSAP
WHERE SSRQ >= TO_DAte('2016-10-01','yyyy-mm-dd')
AND ssrq < to_date('2017-10-01','yyyy-mm-dd')
and zfbz = 0 and wcbz = 1
AND ssnm in(select ssnm from gy_ssdm where ssdm like '%01.24%' or ssdm like '%01.39%'))
and ( c.yfyy = 1 or c.ypyf = 36) and c.ypyf <> 27 AND d.ksbz >0
GROUP BY c.zyh,d.ypxh,d.ypmc,c.TZSj,c.KSSJ
ORDER BY c.zyh) aa
GROUP BY aa.zyh,aa.ypxh,aa.ypmc) aaa
GROUP BY aaa.zyh)) bb WHERE bb.zyh = a.zyh) AS 用药时间 ,

 

'' AS 备注
FROM SM_SSAP a,zy_brry b
WHERE a.zyh = b.zyh
AND a.ssrq >=to_date('2016-10-01','yyyy-mm-dd')
AND a.ssrq <to_date('2017-10-01','yyyy-mm-dd')
and a.zfbz = 0 and a.wcbz = 1
AND a.ssnm in(select ssnm from gy_ssdm where ssdm like '%01.24%' or ssdm like '%01.39%' )

ORDER BY b.bahm

 

 

----------------------------

 

---用这个统计一类切开手术

---方法1统计使用抗生素
select distinct a.brbh as 患者病历号,a.brxb as 性别,a.rynl as 年龄,a.ryrq as 入院日期,a.cyrq as 出院日期 ,
(select count(distinct emr_yzb.yfyy) from emr_yzb where emr_yzb.zyh=a.jzhm ) as 是否预防0为否大于0为是,
c.pz as 品种,
c.lc as 疗程
from ys_zy_jzjl a,sm_sssq b,v_amqc_kjyw_lc c
where a.jzhm=b.zyh
and a.jzhm=c.zyh
and b.tjbz=1
and b.apbz=1
and b.zfbz=0
and a.cyrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
and a.cyrq<to_date('2017-03-31 23:59:59','yyyy-mm-dd hh24:mi;ss')
and b.nssmc like '%髋%'
----方法2统计使用抗生素
select distinct a.brbh as 患者病历号,a.brxb as 性别,a.rynl as 年龄,a.ryrq as 入院日期,a.cyrq as 出院日期 ,
(select count(distinct emr_yzb.yfyy) from emr_yzb where emr_yzb.zyh=a.jzhm ) as 是否预防0为否大于0为是,
b.pz as 品种,
b.lc as 疗程
from ys_zy_jzjl a
where a.cyrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
and a.cyrq<to_date('2017-01-10 23:59:59','yyyy-mm-dd hh24:mi;ss')
and exists (select 1 from emr_Yzb where emr_yzb.zyh=a.jzhm and yzmc like '%拟%髋%' )




--统计5-1-2-1

select distinct a.brbh as 患者病历号,a.brxb as 性别,a.rynl as 年龄,a.ryrq as 入院日期,a.cyrq as 出院日期 ,
(select count(distinct emr_yzb.yfyy) from emr_yzb where emr_yzb.zyh=a.jzhm ) as 是否预防0为否大于0为是,
b.pz as 品种,
b.lc as 疗程
from ys_zy_jzjl a,v_amqc_kjyw_lc b
where a.jzhm=b.zyh
and a.cyrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
and a.cyrq<to_date('2017-01-31 23:59:59','yyyy-mm-dd hh24:mi;ss')
and exists (select 1 from emr_Yzb where emr_yzb.zyh=a.jzhm and yzmc like '%拟%白内障%' )
union all
select distinct a.brbh as 患者病历号,a.brxb as 性别,a.rynl as 年龄,a.ryrq as 入院日期,a.cyrq as 出院日期 ,
(select count(distinct emr_yzb.yfyy) from emr_yzb where emr_yzb.zyh=a.jzhm ) as 是否预防0为否大于0为是,
0 as 品种,
0 as 疗程
from ys_zy_jzjl a
where a.cyrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi;ss')
and a.cyrq<to_date('2017-01-31 23:59:59','yyyy-mm-dd hh24:mi;ss')
and not exists(select 1 from v_amqc_kjyw_lc where a.jzhm=v_amqc_kjyw_lc.zyh )
and exists (select 1 from emr_Yzb where emr_yzb.zyh=a.jzhm and yzmc like '%拟%白内障%' )

-------------------------------------------------------------------------------------------------------------------------

select a.*,substr(b.yzmc,19,100) from (
select
C.KSMC as 费用科室,b.brxm,b.zyhm,b.zyh,
SUM(ZJJE) as 总费用,
sum(case when ( a.FYDJ > 200 and a.fyxm in (48,69) )then a.zjje else 0 end ) as 大于200卫生材料,
sum(case when (a.fydj > 10000 and a.fyxm in (48,69) ) then a.zjje else 0 end ) as 大于10000卫生材料
from v_zy_fymx a,zy_brry b,gy_ksdm c,gy_ksdm d,gy_ksdm e
where A.ZYH=B.ZYH
AND A.FYKS=c.KSDM
AND A.FYBQ=d.KSDM
and b.brks=e.ksdm
AND B.CYRQ>TO_DATE('2017-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND B.CYRQ<TO_DATE('2017-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and a.fyks in (select ksdm from gy_ksdm where ksmc like '%伤骨%')
and (exists (select 1 from sm_sssq where sm_sssq.zyh=b.zyh and nssmc like '%髋%' and tjbz=1 and zfbz=0 and apbz=1 )or exists(select 1 from emr_yzb where emr_yzb.zyh=b.zyh and yzmc like '%拟%髋%' ) )
GROUP BY C.KSMC,b.brxm,b.zyhm,b.zyh
union all
select
C.KSMC as 费用科室,b.brxm,b.zyhm,b.zyh,
SUM(ZJJE) as 总费用,
sum(case when ( a.FYDJ > 200 and a.fyxm in (48,69) )then a.zjje else 0 end ) as 大于200卫生材料,
sum(case when (a.fydj > 10000 and a.fyxm in (48,69) ) then a.zjje else 0 end ) as 大于10000卫生材料
from v_zy_fymx a,zy_brry b,gy_ksdm c,gy_ksdm d,gy_ksdm e
where A.ZYH=B.ZYH
AND A.FYKS=c.KSDM
AND A.FYBQ=d.KSDM
and b.brks=e.ksdm
AND B.CYRQ>TO_DATE('2017-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND B.CYRQ<TO_DATE('2017-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and a.fyks in (select ksdm from gy_ksdm where ksmc like '%伤骨%')
and (exists (select 1 from sm_sssq where sm_sssq.zyh=b.zyh and nssmc like '%股骨%' and tjbz=1 and zfbz=0 and apbz=1 )or exists(select 1 from emr_yzb where emr_yzb.zyh=b.zyh and yzmc like '%拟%股骨%' ) )
GROUP BY C.KSMC,b.brxm,b.zyhm,b.zyh) a,emr_yzb b
where a.zyh=b.zyh
and b.yzmc like '%拟%股骨头%'
----
union
select a.*,substr(b.yzmc,19,100) from (
select
C.KSMC as 费用科室,b.brxm,b.zyhm,b.zyh,
SUM(ZJJE) as 总费用,
sum(case when ( a.FYDJ > 200 and a.fyxm in (48,69) )then a.zjje else 0 end ) as 大于200卫生材料,
sum(case when (a.fydj > 10000 and a.fyxm in (48,69) ) then a.zjje else 0 end ) as 大于10000卫生材料
from v_zy_fymx a,zy_brry b,gy_ksdm c,gy_ksdm d,gy_ksdm e
where A.ZYH=B.ZYH
AND A.FYKS=c.KSDM
AND A.FYBQ=d.KSDM
and b.brks=e.ksdm
AND B.CYRQ>TO_DATE('2017-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND B.CYRQ<TO_DATE('2017-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and a.fyks in (select ksdm from gy_ksdm where ksmc like '%伤骨%')
and (exists (select 1 from sm_sssq where sm_sssq.zyh=b.zyh and nssmc like '%髋%' and tjbz=1 and zfbz=0 and apbz=1 )or exists(select 1 from emr_yzb where emr_yzb.zyh=b.zyh and yzmc like '%拟%髋%' ) )
GROUP BY C.KSMC,b.brxm,b.zyhm,b.zyh
union all
select
C.KSMC as 费用科室,b.brxm,b.zyhm,b.zyh,
SUM(ZJJE) as 总费用,
sum(case when ( a.FYDJ > 200 and a.fyxm in (48,69) )then a.zjje else 0 end ) as 大于200卫生材料,
sum(case when (a.fydj > 10000 and a.fyxm in (48,69) ) then a.zjje else 0 end ) as 大于10000卫生材料
from v_zy_fymx a,zy_brry b,gy_ksdm c,gy_ksdm d,gy_ksdm e
where A.ZYH=B.ZYH
AND A.FYKS=c.KSDM
AND A.FYBQ=d.KSDM
and b.brks=e.ksdm
AND B.CYRQ>TO_DATE('2017-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND B.CYRQ<TO_DATE('2017-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and a.fyks in (select ksdm from gy_ksdm where ksmc like '%伤骨%')
and (exists (select 1 from sm_sssq where sm_sssq.zyh=b.zyh and nssmc like '%髋%' and tjbz=1 and zfbz=0 and apbz=1 )or exists(select 1 from emr_yzb where emr_yzb.zyh=b.zyh and yzmc like '%拟%髋%' ) )
GROUP BY C.KSMC,b.brxm,b.zyhm,b.zyh) a,emr_yzb b
where a.zyh=b.zyh
and b.yzmc like '%拟%髋%'

-------------------------------------------------------------------------------

 

 select ksmc,sum(lywc),sum(cl),sum(hj) from (
select ksmc,0 as lywc,sum(cl)as cl,sum(hj) as hj from (
select  ksmc,0 as cl, sum(mz + zy)  as hj from (
select c.ksmc,  sum(b.hjje) as mz, 0 as zy  from ms_yj01 a,ms_yj02 b ,gy_ksdm c
where a.yjxh=b.yjxh and a.zxks=c.ksdm
and   a.zxks IN (180,38,56,39,102,129,45,46,43,174,41,55,57,47,59,44)
and   a.fphm is not null
and   a.zxpb=1
and   a.zfpb=0
and   a.zxrq>to_date('2017-08-16 00:00:00','yyyy-mm-dd hh24:mi:ss')
and   a.zxrq<to_date('2017-08-23 23:59:59','yyyy-mm-dd hh24:mi:ss')
group by  c.ksmc
union all
select c.ksmc,   0 as mz, sum(zjje) as zy from v_zy_fymx  a ,gy_ksdm c
where a.zxks IN (180,38,56,39,102,129,45,46,43,174,41,55,57,47,59,44)
and   a.zxks=c.ksdm
and  a.JFRQ>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and  a.jfrq<to_date('2017-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
group by c.ksmc) group by ksmc
union all
select  ksmc,sum(mz + zy)  as cl , 0as hj from (
select c.ksmc,  sum(b.hjje) as mz, 0 as zy  from ms_yj01 a,ms_yj02 b ,gy_ksdm c,gy_ylsf d
where a.yjxh=b.yjxh and a.zxks=c.ksdm
and   a.zxks IN (180,38,56,39,102,129,45,46,43,174,41,55,57,47,59,44)
and   a.fphm is not null
and   a.zxpb=1
and   a.zfpb=0
and   b.ylxh =d.fyxh
and   d.fygb=48
and   a.zxrq>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and   a.zxrq<to_date('2017-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
group by  c.ksmc
union all
select c.ksmc,   0 as mz, sum(zjje) as zy from v_zy_fymx  a ,gy_ksdm c
where a.zxks IN (180,38,56,39,102,129,45,46,43,174,41,55,57,47,59,44)
and   a.zxks=c.ksdm
and   a.fyxm=48
and  a.JFRQ>to_date('2017-01-16 00:00:00','yyyy-mm-dd hh24:mi:ss')
and  a.jfrq<to_date('2017-08-23 23:59:59','yyyy-mm-dd hh24:mi:ss')
group by c.ksmc) group by ksmc) group by ksmc
union all
select b.ksmc,(0 - sum(a.wzje * a.ywlb))  as lywc, 0 as cl, 0 as hj  from wl_zcmx a,gy_ksdm b
where a.ksdm IN (180,38,56,39,102,129,45,46,43,174,41,55,57,47,59,44)
and   a.ksdm=b.ksdm
and   a.kfxh=48
and   a.fsrq>to_date('2017-08-16 00:00:00','yyyy-mm-dd hh24:mi:ss')
and   a.fsrq<to_date('2017-08-23 23:59:59','yyyy-mm-dd hh24:mi:ss')
group by b.ksmc ) group by ksmc

 

 ---------------------------------------------------------------------------------------------------

--颗粒的定义：草药中的剂型为颗粒的药（可以切换到药库的角色，然后再切换到草药房，看剂型为颗粒的药，用拼音码调取的时候前面一般会加上z）

 --住院颗粒
select 　sum(zjje)  from v_zy_fymx  where to_char(jfrq,'yyyy')='2017' and    fyxh 
in(select ypxh　from yk_typk where ypsx in (select  ypsx from  yk_ypsx where sxmc like '%颗粒%' ) and zfpb=0  and xtsb=91
) 
-----------------------------------------
--门诊颗粒
select  sum(ypsl*ypdj) from    ms_cf01 a,     ms_cf02 b   where ypxh in (select ypxh　from yk_typk where ypsx in
 (select  ypsx from  yk_ypsx where sxmc like '%颗粒%' ) and zfpb=0  and xtsb=91 ) 
and  a.cfsb=b.cfsb and   to_char(fyrq,'yyyy')='2017'   ;


--住院常用
select 　sum(zjje)  from v_zy_fymx  where to_char(jfrq,'yyyy')='2017' and    fyxh 
in(select ypxh  from yk_typk  where    cyypbz =1)  ; 
--住院基本
select 　sum(zjje)  from v_zy_fymx  where to_char(jfrq,'yyyy')='2017' and    fyxh 
in(select ypxh  from yk_typk  where   jbywbz =1 )   ;

--门诊基本

select  sum(ypsl*ypdj) from    ms_cf01 a,     ms_cf02 b   where ypxh in (select ypxh  from yk_typk  where   jbywbz =1 )
and  a.cfsb=b.cfsb and   to_char(fyrq,'yyyy')='2017' and a.zfpb=0   and a.fybz=1 ;
  


--门诊常用

select  sum(ypsl*ypdj) from    ms_cf01 a,     ms_cf02 b   where ypxh in (select ypxh  from yk_typk  where    cyypbz =1 )
and  a.cfsb=b.cfsb and   to_char(fyrq,'yyyy')='2017' and a.zfpb=0 and a.fybz=1 ;
-------------------------------------------------------------------------------------------

--门诊病历的书写率

 select xm,ksmc,sum(gh),sum(wx) from (
select xm,ksmc,count(a) as gh , 0 as wx from (
select c.brid, c.brxm,a.ghsj,(select count(*) from omr_bl01  where omr_bl01.jzxh=b.jzxh and omr_bl01.brid=b.brbh) as a,
(select ksmc from ms_ghks  where a.ksdm=ms_ghks.ksdm) as ksmc,
(select ygxm from gy_ygdm where a.ysdm=gy_ygdm.ygdm) as xm
from
ms_ghmx  a,
ys_mz_jzls b,
ms_brda c
where a.sbxh=b.ghxh
and  a.ghsj>to_date('2017-09-17 00:00:00','yyyy-mm-dd hh24:mi:ss')
and  a.ghsj<to_date('2017-09-24 00:00:00','yyyy-mm-dd hh24:mi:ss')
and  a.brid=c.brid
order by a,xm,brxm
)group by xm,ksmc
union all

select xm,ksmc,0 , count(*) as wx from (
select c.brid, c.brxm,a.ghsj,(select count(*) from omr_bl01  where omr_bl01.jzxh=b.jzxh and omr_bl01.brid=b.brbh) as a,
(select ksmc from ms_ghks  where a.ksdm=ms_ghks.ksdm) as ksmc,
(select ygxm from gy_ygdm where a.ysdm=gy_ygdm.ygdm) as xm
from
ms_ghmx  a,
ys_mz_jzls b,
ms_brda c
where a.sbxh=b.ghxh
and  a.ghsj>to_date('2017-09-17 00:00:00','yyyy-mm-dd hh24:mi:ss')
and  a.ghsj<to_date('2017-09-24 00:00:00','yyyy-mm-dd hh24:mi:ss')
and  a.brid=c.brid
order by a,xm,brxm
)where a=0 group by xm,ksmc) group by xm,ksmc

    

 

转载于:https://www.cnblogs.com/thomasbc/p/8081595.html