about work at seven
<div class="highlighter">[*]
[*]-------------------------------------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}</forEach>
[*]类中取:
[*]for(inti=0;i<jb_list.size();i++){
[*]Stringsums=((Object[])jb_list.get(i)).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}"/>
页:
[1]