67,516
社区成员
发帖
与我相关
我的任务
分享
//暂时先用HQL嵌套解决
public void wayBillStatics() {
Session session = HibernateSessionFactoryUtil.getSessionFactory()
.getCurrentSession();
Transaction tx = session.beginTransaction();
StringBuffer ehql=new StringBuffer();
ehql.append("select distinct empl from WayBill");
Query query=session.createQuery(ehql.toString());
List<Empl> emplList=query.list();
for(Empl empl:emplList){
StringBuffer hql=new StringBuffer();
hql.append(" select new Map( ");
hql.append(" count(*) as totalNumber ");
hql.append(" ,sum(productQty) as totalProductQty ");
hql.append(" ,sum(productWeight) as totalProductWeight ");
hql.append(" ,sum(productVolume) as totalProductVolume ");
hql.append(" ,sum(productValue) as totalProductValue ");
hql.append(" ,sum(incomeCarry) as totalIncomCarry ");
hql.append(" ) ");
hql.append(" from WayBill ");
hql.append(" where valid='1' ");
hql.append(" and empl=? ");
System.out.println(hql.toString());
query=session.createQuery(hql.toString());
query.setEntity(0, empl);
List<Map> list=query.list();
for(int i=0;i<list.size();i++){
Map m=list.get(i);
System.out.println(empl.getEmpName()
+"--票数:"+m.get("totalNumber")
+"--总件数:"+m.get("totalProductQty")
+"--总重量:"+m.get("totalProductWeight")
+"--总体积:"+m.get("totalProductVolume")
+"--总价值:"+m.get("totalProductValue")
+"--总收入:"+m.get("totalIncomCarry")
);
}
}
tx.commit();
}
//运行结果:
张三--票数:2--总件数:20--总重量:200--总体积:2000--总价值:1000--总收入:800
李四--票数:1--总件数:10--总重量:100--总体积:1000--总价值:500--总收入:400
统计业务员业务量SQL:
select ManualManID,count(ManualManID) as totalNumber,sum(ProductQty) as totalProductQty,sum(ProductWeight) as totalProductWeight,sum(ProductVolume) as totalProductVolume,
sum(ProductValue) as totalProductValue,sum(IncomeCarry) as totalIncomCarry from Oms_WayBill where Valid='True'
group by ManualManID
//------------------------------------
我也要解决这问题,要用hql``
StringBuffer hql=new StringBuffer();
hql.append(" select new Map( ");
hql.append(" count(*) as totalNumber ");
hql.append(" ,sum(productQty) as totalProductQty ");
hql.append(" ,sum(productWeight) as totalProductWeight ");
hql.append(" ,sum(productVolume) as totalProductVolume ");
hql.append(" ,sum(productValue) as totalProductValue ");
hql.append(" ,sum(incomeCarry) as totalIncomCarry ");
hql.append(" ) ");
hql.append(" from WayBill ");
hql.append(" where valid='1' ");
hql.append(" group by empl.empId ");
hql.append(" order by empl.empId ");
System.out.println(hql.toString());
Query query=session.createQuery(hql.toString());
List<Map> list=query.list();
for(int i=0;i<list.size();i++){
Map m=list.get(i);
System.out.println("--票数:"+m.get("totalNumber")
+"--总件数:"+m.get("totalProductQty")
+"--总重量:"+m.get("totalProductWeight")
+"--总体积:"+m.get("totalProductVolume")
+"--总价值:"+m.get("totalProductValue")
+"--总收入:"+m.get("totalIncomCarry")
);
}
//运行结果:
--票数:2--总件数:20--总重量:200--总体积:2000--总价值:1000--总收入:800
--票数:1--总件数:10--总重量:100--总体积:1000--总价值:500--总收入:400
现在还差个业务员,高手来解决啊··