grails的executeQuery跟hql的语法差不多,executeQuery与in一起使用:代码如下【说明 下面的是list的类型是: List<Long>】
String str="select prodId,sum(num),prodPrice from MikuMineBoxProduct where boxId in (:list) group by prodId"
def data=MikuMineBoxProduct.executeQuery(str,[list:list])
def getProdOnePeriodData(List<Long> boxIdList,List<Long> prodIdList,String flag){
def data
String str="select prodId,"
if("day".equals(flag)){
str+=" DATE_FORMAT(dateCreated,'%Y-%m-%d')"
}else if("month".equals(flag)){
str+=" DATE_FORMAT(dateCreated,'%Y-%m')"
}
str+=" ,sum(num) from MikuMineBoxProduct where boxId in (:boxIdList) and prodId in (:prodIdList) group by "
if("day".equals(flag)){
str+=" DATE_FORMAT(dateCreated,'%Y-%m-%d')"
}else if("month".equals(flag)){
str+=" DATE_FORMAT(dateCreated,'%Y-%m')"
}
data=MikuMineBoxProduct.executeQuery(str,[boxIdList:boxIdList,prodIdList:prodIdList])
return data
}
def getAllPeriodTime(String userId,String realId,String begin,String end,int sumflg){
String str="select "
if(!sumflg){
str+= "DATE_FORMAT(dateCreated,'%Y-%m-%d'),"
}
str+="IFNULL(sum(price),0)/100," +
"IFNULL(sum(case when "+dfSql+" then prepaidFee end),0)/100," +
"IFNULL(sum(case when "+dfSql+" then price end),0)/100," +
"IFNULL(sum(case when "+onlineSql+" then price end),0)/100," +
"IFNULL(sum(case when "+failSql+" then price end),0)/100," +
"IFNULL(sum(case when "+wxSql+" then price end),0)/100," +
"IFNULL(sum(case when "+alipaySql+" then price end),0)/100," +
"count(id)," +
"count(case when "+failSql+" then id end)" +
" from Trade where "+baseSql+" and dateCreated>='"+begin+"' and dateCreated<'"+end+"' "
if (userId){
str+="and wxNo="+userId
}
else if(realId){
str+="and pUserId="+Long.parseLong(realId)
}
if(!sumflg){
str+=" group by DATE_FORMAT(dateCreated,'%Y-%m-%d')"
}
def list=Trade.executeQuery(str)
println(list)
return list
}