81,092
社区成员
发帖
与我相关
我的任务
分享
{"data":[{"account":"01","product":"p1"},...]}
{"data":[{"account":"01","product":"p1","avgNum":111,"avgPrice":222},...]}
object.put("avgNum","")...
;
String sql="SELECT avg(il.UnitPrice),sum(il.Quantity)/12 FROM Invoice i LEFT JOIN InvoiceLineItem il " +
"ON i.InvoiceId=il.InvoiceId " +
"LEFT JOIN Product p ON p.ProductId=il.ProductId "+
"LEFT JOIN Account a ON i.AccountId=a.AccountId "+
"WHERE datediff(m,InvoicedOn,getDate())<=12 " +
"AND p.ProductNumber=? AND a.AccountName=? ";
...
for(int i=0;i<jArray.size();i++){
JSONObject o=jArray.getJSONObject(i);
if(o.isEmpty())
break;
ps.setString(1,o.getString("product"));
ps.setString(2,o.getString("account"));
rs=ps.executeQuery();
Map map=new HashMap<String,String>();
map.put("$$avgPrice",0);
map.put("$$avgNum",0);
while(rs.next()){
map.put("$$avgPrice", rs.getDouble(1));
map.put("$$avgNum", rs.getDouble(2));
}
o.putAll(map);
}
不知道各位有什么好的主意,集思广益,