about work at seven

xiao_yi · 发表于 2013-1-27 04:43:00

-------------------------------------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}" />

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