about work at seven
<div class="highlighter">[*]<span />
[*]-------------------------------------2008.7.23 四级疾病统计 -------------------------------------------------------
[*]<span />
[*]<span />/********************************************** 一级数据统计 ************************************************/<span />
[*]<span />//没有加入时间 <span />
[*]<span />
[*]select mc,sum(cou) as cous ,yiji from (
[*]<span />
[*]select f.mc,i.cou,f.yiji
[*]from
[*]<span />
[*](select e.sanji,h.cou from csm_icd_jblx e,
[*]<span />
[*](select b.mc,count(a.zd) as cou,b.flid
[*] FROM ywb_mz_bingli a,csm_icd_10 b
[*]where a.zd=b.mc and a.siteid='4114210102'<span />
[*]group by b.mc) h
[*]where e.id=h.flid) i,csm_icd_jblx f
[*]where substring(i.sanji,1,5)=f.yiji
[*]<span />
[*]union all
[*]<span />
[*]<span />
[*]SELECT c.mc,count(a.zd),c.yiji
[*] FROM ywb_mz_bingli a,csm_icd_jblx c
[*]where a.zd=c.mc
[*] and c.yiji<>'' and c.yiji is not null and c.erji<>'' and c.erji is not null
[*] and c.sanji<>'' and c.sanji is not null<span />
[*]group by c.mc
[*]union all
[*]<span />
[*]<span />
[*]select f.mc,h.cou,f.yiji
[*]from (
[*]SELECT c.mc,count(a.zd) as cou,substring(c.erji,1,5) as yiji
[*] FROM ywb_mz_bingli a,csm_icd_jblx c
[*]where a.zd=c.mc
[*] and c.erji<>'' and c.erji is not null<span />
[*] and (c.sanji='' or c.sanji is null)
[*] group by c.mc) h,csm_icd_jblx f
[*]where h.yiji=f.yiji
[*]union all
[*]<span />
[*]<span />
[*]<span />
[*]select f.mc,h.cou,f.yiji
[*]from (
[*]SELECT c.mc,count(a.zd) as cou,substring(c.sanji,1,5) as yiji
[*] FROM ywb_mz_bingli a,csm_icd_jblx c
[*]where a.zd=c.mc
[*] and c.sanji <>'' and c.sanji is not null<span />
[*] group by c.mc) h,csm_icd_jblx f
[*]where h.yiji=f.yiji
[*]) as t group by mc order by cous desc
[*]<span />
[*]<span />
[*]<span />/********************************************** 二级数据统计 ************************************************/<span />
[*]<span />//根据一级找二级数据 <span />
[*]<span />
[*]select mc,sum(cou) as cous ,erji from (
[*]<span />
[*]select f.mc,i.cou,f.erji
[*]from
[*](select e.sanji,h.cou
[*] from csm_icd_jblx e,(select b.mc,count(a.zd) as cou,b.flid
[*] FROM ywb_mz_bingli a,csm_icd_10 b
[*]where a.zd=b.mc and a.jzrq between '2008-01-01' and '2008-07-23' and a.siteid='4114210102'<span />
[*]group by b.mc) h
[*]where e.id=h.flid) i,csm_icd_jblx f
[*]where substring(i.sanji,1,9)=f.erji
[*]<span />
[*]<span />
[*]union all
[*]SELECT c.mc,count(a.zd) as cou,c.erji
[*] FROM ywb_mz_bingli a,csm_icd_jblx c
[*]where a.zd=c.mc and a.jzrq between '2008-01-01' and '2008-07-23'
[*] and c.erji<>'' and c.erji is not null<span />
[*] and (c.sanji='' or c.sanji is null)
[*] group by c.mc
[*]<span />
[*]union all
[*]select f.mc,h.cou,f.erji
[*]from (
[*]SELECT c.mc,count(a.zd) as cou,substring(c.sanji,1,9) as erji
[*] FROM ywb_mz_bingli a,csm_icd_jblx c
[*]where a.zd=c.mc and a.jzrq between '2008-01-01' and '2008-07-23'
[*] and c.sanji <>'' and c.sanji is not null<span />
[*] group by c.mc) h,csm_icd_jblx f
[*]where h.erji=f.erji
[*])as t where substring(erji,1,5)='a0015' group by mc order by cous desc
[*]<span />
[*]<span />/********************************************** 三级数据统计 ************************************************/<span />
[*]<span />//根据二级找三级 <span />
[*]select mc,sum(cou) as cous ,sanji,id from (
[*]<span />
[*]select e.mc,h.cou,e.sanji,e.id
[*] from csm_icd_jblx e,(select b.mc,count(a.zd) as cou,b.flid
[*] FROM ywb_mz_bingli a,csm_icd_10 b
[*]where a.zd=b.mc and a.jzrq between '2008-01-01' and '2008-07-23' and a.siteid='4114210102'<span />
[*]group by b.mc) h
[*]where e.id=h.flid and substring(sanji,1,9)='a00150005'<span />
[*]<span />
[*]union all
[*]<span />
[*]SELECT c.mc,count(a.zd) as cou,c.id,c.sanji
[*] FROM ywb_mz_bingli a,csm_icd_jblx c
[*]where a.zd=c.mc and a.jzrq between '2008-01-01' and '2008-07-23'
[*] and c.sanji <>'' and c.sanji is not null and substring(sanji,1,9)='a00150005'<span />
[*] group by c.mc
[*])
[*]as t group by mc order by cous desc
[*]<span />
[*]<span />/********************************************** 三级数据统计 ************************************************/<span />
[*]<span />//根据三级找四级 <span />
[*]<span />
[*]SELECT b.mc,count(a.zd) as cou
[*] FROM ywb_mz_bingli a,csm_icd_10 b
[*]where a.zd=b.mc and a.jzrq between '2008-01-01' and '2008-07-23' and a.siteid='4114210102' and b.flid='141'<span />
[*]group by b.mc
[*]order by cou desc
[*]<span />
[*]----------------------------------------------------------------------------------------------------------------------
[*]<span />
[*]<1> DAO中使用return this.getSession().createSQLQuery(sql).list();方法执行纯sql,偶尔会有问题发生,type不支持,故改用JDBC方式统计数据,使用方法如下
[*] /**
[*] * 根据sql查询rs返回
[*] */<span />
[*] public ResultSet findDataByConditon(String sql){
[*] ResultSet rs=null;
[*] try {
[*] rs=this.getSession()
[*] .connection()
[*] .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)
[*] .executeQuery(sql);
[*] } catch (DataAccessResourceFailureException e) {
[*] e.printStackTrace();
[*] } catch (HibernateException e) {
[*] e.printStackTrace();
[*] } catch (IllegalStateException e) {
[*] e.printStackTrace();
[*] } catch (SQLException e) {
[*] e.printStackTrace();
[*] }
[*] return rs;
[*] }
[*]<span />
[*]<2> 在新的集合中加入ResultSet中数据...(threesick_rs为ResultSet对象)
[*] new_list.add(new Object[]{threesick_rs.getString(1),threesick_rs.getString(2)});
[*]<span />
[*]<3> 当使用纯sql查回数据带count,sum之类数据时,取其中的值...
[*] 页面中取:
[*] <c:forEach var="record" items="${jb_list}" varStatus="status">${record}</forEach>
[*] 类中取:
[*] for(int i=0;i<jb_list.size();i++){
[*] String sums = ((Object [])jb_list.get(i)).toString();
[*] befsum+=Integer.parseInt(sums);
[*] }
[*]<4> 在统计图中使用下拉列表切换时: 使用js
[*]<span />
[*]先引入js:
[*]<span />
[*]<script type="text/javascript" src="<%=request.getContextPath()%>/js/jquery-1.2.6.pack.js"></script>
[*]<span />
[*]
[*]<script type="text/javascript">
[*]function changeImageType()
[*]{
[*] var tjt=document.getElementById("tjtype").value;
[*] var tjlx=document.getElementById("tjlx").value;
[*]<span />
[*] 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>
[*]<span />
[*]<5> 使用ResultSet数据时,如果使用两次,那么在第二次取数据之前一定要将游标指回,否则数据丢失.
[*]<span />
[*] threesick_rs.beforeFirst();
[*]
[*]<6> c标签格式化日期
[*]<span />
[*]<fmt:formatDate pattern="yyyy-MM-dd" value="${record.mzbl.jzrq}" />
页:
[1]