about work at seven

happmaoo · 发表于 2013-1-19 04:07:21

-------------------------------------2008.7.23四级疾病统计-------------------------------------------------------
/**********************************************一级数据统计************************************************/
//没有加入时间
selectmc,sum(cou)ascous,yijifrom(
selectf.mc,i.cou,f.yiji
from
(selecte.sanji,h.coufromcsm_icd_jblxe,
(selectb.mc,count(a.zd)ascou,b.flid
FROMywb_mz_binglia,csm_icd_10b
wherea.zd=b.mcanda.siteid='4114210102'
groupbyb.mc)h
wheree.id=h.flid)i,csm_icd_jblxf
wheresubstring(i.sanji,1,5)=f.yiji
unionall
SELECTc.mc,count(a.zd),c.yiji
FROMywb_mz_binglia,csm_icd_jblxc
wherea.zd=c.mc
andc.yiji<>''andc.yijiisnotnullandc.erji<>''andc.erjiisnotnull
andc.sanji<>''andc.sanjiisnotnull
groupbyc.mc
unionall
selectf.mc,h.cou,f.yiji
from(
SELECTc.mc,count(a.zd)ascou,substring(c.erji,1,5)asyiji
FROMywb_mz_binglia,csm_icd_jblxc
wherea.zd=c.mc
andc.erji<>''andc.erjiisnotnull
and(c.sanji=''orc.sanjiisnull)
groupbyc.mc)h,csm_icd_jblxf
whereh.yiji=f.yiji
unionall
selectf.mc,h.cou,f.yiji
from(
SELECTc.mc,count(a.zd)ascou,substring(c.sanji,1,5)asyiji
FROMywb_mz_binglia,csm_icd_jblxc
wherea.zd=c.mc
andc.sanji<>''andc.sanjiisnotnull
groupbyc.mc)h,csm_icd_jblxf
whereh.yiji=f.yiji
)astgroupbymcorderbycousdesc
/**********************************************二级数据统计************************************************/
//根据一级找二级数据
selectmc,sum(cou)ascous,erjifrom(
selectf.mc,i.cou,f.erji
from
(selecte.sanji,h.cou
fromcsm_icd_jblxe,(selectb.mc,count(a.zd)ascou,b.flid
FROMywb_mz_binglia,csm_icd_10b
wherea.zd=b.mcanda.jzrqbetween'2008-01-01'and'2008-07-23'anda.siteid='4114210102'
groupbyb.mc)h
wheree.id=h.flid)i,csm_icd_jblxf
wheresubstring(i.sanji,1,9)=f.erji
unionall
SELECTc.mc,count(a.zd)ascou,c.erji
FROMywb_mz_binglia,csm_icd_jblxc
wherea.zd=c.mcanda.jzrqbetween'2008-01-01'and'2008-07-23'
andc.erji<>''andc.erjiisnotnull
and(c.sanji=''orc.sanjiisnull)
groupbyc.mc
unionall
selectf.mc,h.cou,f.erji
from(
SELECTc.mc,count(a.zd)ascou,substring(c.sanji,1,9)aserji
FROMywb_mz_binglia,csm_icd_jblxc
wherea.zd=c.mcanda.jzrqbetween'2008-01-01'and'2008-07-23'
andc.sanji<>''andc.sanjiisnotnull
groupbyc.mc)h,csm_icd_jblxf
whereh.erji=f.erji
)astwheresubstring(erji,1,5)='a0015'groupbymcorderbycousdesc
/**********************************************三级数据统计************************************************/
//根据二级找三级
selectmc,sum(cou)ascous,sanji,idfrom(
selecte.mc,h.cou,e.sanji,e.id
fromcsm_icd_jblxe,(selectb.mc,count(a.zd)ascou,b.flid
FROMywb_mz_binglia,csm_icd_10b
wherea.zd=b.mcanda.jzrqbetween'2008-01-01'and'2008-07-23'anda.siteid='4114210102'
groupbyb.mc)h
wheree.id=h.flidandsubstring(sanji,1,9)='a00150005'
unionall
SELECTc.mc,count(a.zd)ascou,c.id,c.sanji
FROMywb_mz_binglia,csm_icd_jblxc
wherea.zd=c.mcanda.jzrqbetween'2008-01-01'and'2008-07-23'
andc.sanji<>''andc.sanjiisnotnullandsubstring(sanji,1,9)='a00150005'
groupbyc.mc
)
astgroupbymcorderbycousdesc
/**********************************************三级数据统计************************************************/
//根据三级找四级
SELECTb.mc,count(a.zd)ascou
FROMywb_mz_binglia,csm_icd_10b
wherea.zd=b.mcanda.jzrqbetween'2008-01-01'and'2008-07-23'anda.siteid='4114210102'andb.flid='141'
groupbyb.mc
orderbycoudesc
----------------------------------------------------------------------------------------------------------------------
<1>DAO中使用returnthis.getSession().createSQLQuery(sql).list();方法执行纯sql,偶尔会有问题发生,type不支持,故改用JDBC方式统计数据,使用方法如下
/**
*根据sql查询rs返回
*/
publicResultSetfindDataByConditon(Stringsql){
ResultSetrs=null;
try{
rs=this.getSession()
.connection()
.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY)
.executeQuery(sql);
}catch(DataAccessResourceFailureExceptione){
e.printStackTrace();
}catch(HibernateExceptione){
e.printStackTrace();
}catch(IllegalStateExceptione){
e.printStackTrace();
}catch(SQLExceptione){
e.printStackTrace();
}
returnrs;
}
<2>在新的集合中加入ResultSet中数据...(threesick_rs为ResultSet对象)
new_list.add(newObject[]{threesick_rs.getString(1),threesick_rs.getString(2)});
<3>当使用纯sql查回数据带count,sum之类数据时,取其中的值...
页面中取:
<c:forEachvar="record"items="${jb_list}"varStatus="status">${record[0]}</forEach>
类中取:
for(inti=0;i<jb_list.size();i++){
Stringsums=((Object[])jb_list.get(i))[1].toString();
befsum+=Integer.parseInt(sums);
}
<4>在统计图中使用下拉列表切换时:使用js
先引入js:
<scripttype="text/javascript"src="<%=request.getContextPath()%>/js/jquery-1.2.6.pack.js"></script>
<scripttype="text/javascript">
functionchangeImageType()
{
vartjt=document.getElementById("tjtype").value;
vartjlx=document.getElementById("tjlx").value;
if(tjt=='zzt'){
$.get("<%=request.getContextPath()%>/ajax/mytj/jb/search/fourdata.do?begindate=${param.begindate}&enddate=${param.enddate}&seltype=${seltype}",{},function(data){document.getElementById("chartPhoto").src="<%=request.getContextPath()%>/view/mztj/jb/image.do?method=fourSickBar&tjlx="+tjlx;});
}
if(tjt=='bzt'){
$.get("<%=request.getContextPath()%>/ajax/mytj/jb/search/fourdata.do?begindate=${param.begindate}&enddate=${param.enddate}&seltype=${seltype}",{},function(data){document.getElementById("chartPhoto").src="<%=request.getContextPath()%>/view/mztj/jb/image.do?method=fourSickPie&tjlx="+tjlx;});
}
}
</script>
<5>使用ResultSet数据时,如果使用两次,那么在第二次取数据之前一定要将游标指回,否则数据丢失.
threesick_rs.beforeFirst();
<6>c标签格式化日期
<fmt:formatDatepattern="yyyy-MM-dd"value="${record.mzbl.jzrq}"/>

		自动登录	找回密码
密码			立即注册

about work at seven

浏览过的版块