将信息按年月日季等不同种类排序

switch (flag) {
case 1://按年
for(int i=timesint1[0];i<=timesint2[0];i++){
TjResultBean tjResultBean=new TjResultBean();
sqlsmtj="select sum(num) nums from smtj where  year="+i;
sqlUseryxs="select count(id) nums  from userinfo where  year="+i+" and zt=2";
sqlUseryjg="select count(id) nums  from userinfo where  year="+i+" and zt=3";
tjResultBean.setBdnum(getnum(commonDAO.getResultSet(sqlsmtj)));
tjResultBean.setXsnum(getnum(commonDAO.getResultSet(sqlUseryxs)));
tjResultBean.setJgnum(getnum(commonDAO.getResultSet(sqlUseryjg)));
tjResultBean.setYear(i);
tjResultBean.setJi(0);
tjResultBean.setMonth(0);
tjResultBean.setDay(0);
if(!tjResultBean.getBdnum().equals(0)){
tjResultBean.setXs(String.format("%1.2f",(new Float(tjResultBean.getXsnum())/(new Float(tjResultBean.getBdnum())))));
tjResultBean.setJg(String.format("%1.2f",(new Float(tjResultBean.getJgnum())/(new Float(tjResultBean.getBdnum())))));
}else{
tjResultBean.setXs("0");
tjResultBean.setJg("0");
}
list.add(tjResultBean);
}
break;
case 2:
sql="SELECT sum(num) num,year ,ji from smtj where  DATEDIFF(bdrq,'"+lsre+"')>=0 and DATEDIFF(bdrq,'"+lore+"')<=0   group by year,ji";
rs=commonDAO.getResultSet(sql);
if(rs!=null){
while(rs.next()){
int sum=rs.getInt("num");
int year=rs.getInt("year");
int ji=rs.getInt("ji");
sqlUseryxs="select count(id) nums  from userinfo where  year="+year+" and ji="+ji+" and zt=2";
sqlUseryjg="select count(id) nums  from userinfo where  year="+year+" and ji="+ji+" and zt=3";
TjResultBean tjResultBean=new TjResultBean();
tjResultBean.setBdnum(sum);
tjResultBean.setXsnum(getnum(commonDAO.getResultSet(sqlUseryxs)));
tjResultBean.setJgnum(getnum(commonDAO.getResultSet(sqlUseryjg)));
tjResultBean.setYear(year);
tjResultBean.setJi(ji);
tjResultBean.setMonth(0);
tjResultBean.setDay(0);
if(!tjResultBean.getBdnum().equals(0)){
tjResultBean.setXs(String.format("%1.2f",(new Float(tjResultBean.getXsnum())/(new Float(tjResultBean.getBdnum())))));
tjResultBean.setJg(String.format("%1.2f",(new Float(tjResultBean.getJgnum())/(new Float(tjResultBean.getBdnum())))));
}else{
tjResultBean.setXs("0");
tjResultBean.setJg("0");
}
list.add(tjResultBean);
}
}
break;
case 3://按月
sql="SELECT sum(num) num,year ,month from smtj where  DATEDIFF(bdrq,'"+lsre+"')>=0 and DATEDIFF(bdrq,'"+lore+"')<=0   group by year,month";
rs=commonDAO.getResultSet(sql);
if(rs!=null){
while(rs.next()){
int sum=rs.getInt("num");
int year=rs.getInt("year");
int month=rs.getInt("month");
sqlUseryxs="select count(id) nums  from userinfo where  year="+year+" and month="+month+" and zt=2";
sqlUseryjg="select count(id) nums  from userinfo where  year="+year+" and month="+month+" and zt=3";
TjResultBean tjResultBean=new TjResultBean();
tjResultBean.setBdnum(sum);
tjResultBean.setXsnum(getnum(commonDAO.getResultSet(sqlUseryxs)));
tjResultBean.setJgnum(getnum(commonDAO.getResultSet(sqlUseryjg)));
tjResultBean.setYear(year);
tjResultBean.setMonth(month);
tjResultBean.setJi(0);
tjResultBean.setDay(0);
if(!tjResultBean.getBdnum().equals(0)){
tjResultBean.setXs(String.format("%1.2f",(new Float(tjResultBean.getXsnum())/(new Float(tjResultBean.getBdnum())))));
tjResultBean.setJg(String.format("%1.2f",(new Float(tjResultBean.getJgnum())/(new Float(tjResultBean.getBdnum())))));
}else{
tjResultBean.setXs("0");
tjResultBean.setJg("0");
}
list.add(tjResultBean);
}
}
break;
case 4:
sql="SELECT sum(num) num,year ,month,day from smtj where  DATEDIFF(bdrq,'"+lsre+"')>=0 and DATEDIFF(bdrq,'"+lore+"')<=0   group by year,month,day";
rs=commonDAO.getResultSet(sql);
if(rs!=null){
while(rs.next()){
int sum=rs.getInt("num");
int year=rs.getInt("year");
int month=rs.getInt("month");
int day=rs.getInt("day");
sqlUseryxs="select count(id) nums  from userinfo where  year="+year+" and month="+month+" and day="+day+" and zt=2";
sqlUseryjg="select count(id) nums  from userinfo where  year="+year+" and month="+month+" and day="+day+" and zt=3";
TjResultBean tjResultBean=new TjResultBean();
tjResultBean.setBdnum(sum);
tjResultBean.setXsnum(getnum(commonDAO.getResultSet(sqlUseryxs)));
tjResultBean.setJgnum(getnum(commonDAO.getResultSet(sqlUseryjg)));
tjResultBean.setYear(year);
tjResultBean.setMonth(month);
tjResultBean.setJi(0);
tjResultBean.setDay(day);
if(!tjResultBean.getBdnum().equals(0)){
tjResultBean.setXs(String.format("%1.2f",(new Float(tjResultBean.getXsnum())/(new Float(tjResultBean.getBdnum())))));
tjResultBean.setJg(String.format("%1.2f",(new Float(tjResultBean.getJgnum())/(new Float(tjResultBean.getBdnum())))));
}else{
tjResultBean.setXs("0");
tjResultBean.setJg("0");
}
list.add(tjResultBean);
}
}
break;
default:
break;
}
即先在统计表groupb by后，然后进行根据由数据的项再去信息表中取得