- -------------------------------------2008.7.23 四级疾病统计 -------------------------------------------------------
- /********************************************** 一级数据统计 ************************************************/
- //没有加入时间
- select mc,sum(cou) as cous ,yiji from (
- select f.mc,i.cou,f.yiji
- 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.siteid='4114210102'
- group by b.mc) h
- where e.id=h.flid) i,csm_icd_jblx f
- where substring(i.sanji,1,5)=f.yiji
- union all
- 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
- group by c.mc
- union all
- 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
- and (c.sanji='' or c.sanji is null)
- group by c.mc) h,csm_icd_jblx f
- where h.yiji=f.yiji
- union all
- 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
- group by c.mc) h,csm_icd_jblx f
- where h.yiji=f.yiji
- ) as t group by mc order by cous desc
- /********************************************** 二级数据统计 ************************************************/
- //根据一级找二级数据
- select mc,sum(cou) as cous ,erji from (
- 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'
- group by b.mc) h
- where e.id=h.flid) i,csm_icd_jblx f
- where substring(i.sanji,1,9)=f.erji
- 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
- and (c.sanji='' or c.sanji is null)
- group by c.mc
- 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
- 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
- /********************************************** 三级数据统计 ************************************************/
- //根据二级找三级
- select mc,sum(cou) as cous ,sanji,id from (
- 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'
- group by b.mc) h
- where e.id=h.flid and substring(sanji,1,9)='a00150005'
- union all
- 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'
- group by c.mc
- )
- as t group by mc order by cous desc
- /********************************************** 三级数据统计 ************************************************/
- //根据三级找四级
- 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'
- group by b.mc
- order by cou desc
- ----------------------------------------------------------------------------------------------------------------------
- <1> DAO中使用return this.getSession().createSQLQuery(sql).list();方法执行纯sql,偶尔会有问题发生,type不支持,故改用JDBC方式统计数据,使用方法如下
- /**
- * 根据sql查询rs返回
- */
- 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;
- }
- <2> 在新的集合中加入ResultSet中数据...(threesick_rs为ResultSet对象)
- new_list.add(new Object[]{threesick_rs.getString(1),threesick_rs.getString(2)});
- <3> 当使用纯sql查回数据带count,sum之类数据时,取其中的值...
- 页面中取:
- <c:forEach var="record" items="${jb_list}" varStatus="status">${record[0]}</forEach>
- 类中取:
- for(int i=0;i<jb_list.size();i++){
- String sums = ((Object [])jb_list.get(i))[1].toString();
- befsum+=Integer.parseInt(sums);
- }
- <4> 在统计图中使用下拉列表切换时: 使用js
- 先引入js:
- <script type="text/javascript" src="<%=request.getContextPath()%>/js/jquery-1.2.6.pack.js"></script>
- <script type="text/javascript">
- function changeImageType()
- {
- var tjt=document.getElementById("tjtype").value;
- var tjlx=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:formatDate pattern="yyyy-MM-dd" value="${record.mzbl.jzrq}" />
about work at seven
最新推荐文章于 2021-08-05 09:23:57 发布