架构spring+struts1+hibernate2.5
后台从数据库查询出数据,将数据放入List<HashMap>,然后让前台解析显示
需求:根据卡种名称,状态,柜员统计
旧有写法:select sum(),count()...from table group by 卡种名称,状态,柜员
然后判断 卡种名称,状态,柜员相同时小计,小计前要将map取出并且
进行比较
优化写法
select sum(),count()...from table group by 状态,rollup(卡种名称,柜员)
相同状态下,根据卡种名称,柜员小计,
然后根据状态小计,最后总计
将map取出,添入"小计",“合计”等说明信息
很简单,伪代码说明
StringBuffer totSql = new StringBuffer();
totSql.append("select sum(cardnum)as cardnum,sum(sumamount) as sumamount, sum(deposit) as deposit,brchid,brchtype,kindid,stkid,operid,state,kindname,sername, belongAreano,stkname from(");
totSql.append(sqlBf);
totSql.append(")group by rollup(state,(belongAreano,kindname,sername,brchid,kindid,operid,brchtype,stkname,stkid )) ");//统计非分页所有情况
totSql.append(" order by state,brchtype,brchid,kindid,stkid,operid");
totSql.append("select sum(cardnum)as cardnum,sum(sumamount) as sumamount, sum(deposit) as deposit,brchid,brchtype,kindid,stkid,operid,state,kindname,sername, belongAreano,stkname from(");
totSql.append(sqlBf);
totSql.append(")group by rollup(state,(belongAreano,kindname,sername,brchid,kindid,operid,brchtype,stkname,stkid )) ");//统计非分页所有情况
totSql.append(" order by state,brchtype,brchid,kindid,stkid,operid");
转载于:https://blog.51cto.com/weipeng1986/1021530