select ghrq as rq,count(djh) as sl,max('初诊')as xm from gh_sfdj where ctbz='0' and cf='0' group by ghrq union select yf_ckdj.rq,count(yf_ckdj.djh) as sl,max('化验人数') as xm from yf_ckdj,xt_sffm where yf_ckdj.fpxm=xt_sffm.bm and xt_sffm.mc like '%化验%' group by yf_ckdj.rq unionselect rq,cast(a.sl * 100.00/b.sl as numeric(12,2)),'比率' from (select yf_ckdj.rq,count(yf_ckdj.djh) as sl,max('化验人数') as xm from yf_ckdj,xt_sffm where yf_ckdj.fpxm=xt_sffm.bm and xt_sffm.mc like '%化验%' group by yf_ckdj.rq ) a, (select ghrq as rq,count(djh) as sl,max('初诊')as xm from gh_sfdj where ctbz='0' and cf='0' group by ghrq ) b where a.rq = b.rq //不知道你表什么样,随手打的
select ghrq as rq,count(djh) as sl,max('初诊')as xm from gh_sfdj where ctbz='0' and cf='0' group by ghrq union select yf_ckdj.rq,count(yf_ckdj.djh) as sl,max('化验人数') as xm from yf_ckdj,xt_sffm where yf_ckdj.fpxm=xt_sffm.bm and xt_sffm.mc like '%化验%' group by yf_ckdj.rq union select a.rq,case when a.sl=0 then -1 else b.sl/a.sl end as sl, '化验人数/初诊' as xm from (select ghrq as rq,count(djh) as sl from gh_sfdj where ctbz='0' and cf='0' group by ghrq ) a, (select yf_ckdj.rq,count(yf_ckdj.djh) as sl from yf_ckdj,xt_sffm where yf_ckdj.fpxm=xt_sffm.bm and xt_sffm.mc like '%化验%' group by yf_ckdj.rq ) b where a.rq=b.rq
日期 数量 项目
1 1 初诊
2 3 咨询
2 2 化验人数
现在想再加一行例如是 2号
2 2/3 比率
比率=化验人数/初诊初诊是从 gh_sfdj 表出来的
化验人数是从yf_ckdj表出来的
union
select yf_ckdj.rq,count(yf_ckdj.djh) as sl,max('化验人数') as xm from yf_ckdj,xt_sffm where yf_ckdj.fpxm=xt_sffm.bm and xt_sffm.mc like '%化验%' group by yf_ckdj.rq
unionselect rq,cast(a.sl * 100.00/b.sl as numeric(12,2)),'比率'
from (select yf_ckdj.rq,count(yf_ckdj.djh) as sl,max('化验人数') as xm from yf_ckdj,xt_sffm where yf_ckdj.fpxm=xt_sffm.bm and xt_sffm.mc like '%化验%' group by yf_ckdj.rq ) a,
(select ghrq as rq,count(djh) as sl,max('初诊')as xm from gh_sfdj where ctbz='0' and cf='0' group by ghrq ) b
where a.rq = b.rq
//不知道你表什么样,随手打的
where ctbz='0' and cf='0' group by ghrq
union select yf_ckdj.rq,count(yf_ckdj.djh) as sl,max('化验人数') as xm from yf_ckdj,xt_sffm
where yf_ckdj.fpxm=xt_sffm.bm and xt_sffm.mc like '%化验%' group by yf_ckdj.rq
union select a.rq,case when a.sl=0 then -1 else b.sl/a.sl end as sl,
'化验人数/初诊' as xm
from
(select ghrq as rq,count(djh) as sl from gh_sfdj
where ctbz='0' and cf='0' group by ghrq ) a,
(select yf_ckdj.rq,count(yf_ckdj.djh) as sl from yf_ckdj,xt_sffm
where yf_ckdj.fpxm=xt_sffm.bm and xt_sffm.mc like '%化验%' group by yf_ckdj.rq ) b
where a.rq=b.rq