问题描述
我有一个域名
I have a domain
class InvoiceLine { String itemName BigDecimal unitCost Integer quantity }
}
我想使用.withCriteria创建一个grails闭包,它会对(unitCost *数量)进行聚合,以便最终生成sql
I would like to come up with a grails closure using .withCriteria that does an aggregation of the (unitCost * quantity) so that I end up with sql
select item_name, sum(unit_cost * quantity) from invoice_line group by item_name;
现在,我可以做的最好的是
For now, the best I could come up with is
def result = InvoiceLine.withCriteria { projections { groupProperty('itemName') sum ('quantity * unitCost') } }
不幸的是,当我跑步时,上面的代码。任何人都有任何想法我可以如何实现我的目标?任何帮助都非常感谢。
Unfortunately, grails chokes up when I run the code above. Anyone have any idea how I could achieve my objective? Any help is highly appreciated.
推荐答案
它是否需要成为标准查询? HQL在这里工作得很好:
Does it need to be a criteria query? HQL works great here:
def result = InvoiceLine.executeQuery( "select itemName, sum(unitCost * quantity) " + "from InvoiceLine " + "group by itemName")
结果将会是一个 List Object [] 其中1st元素是一个String(名称)和2nd是一个数字(总数),所以例如你可以迭代类似于
The results will be a List of Object[] where the 1st element is a String (the name) and the 2nd is a number (the sum), so for example you could iterate with something like
results.each { row -> println "Total for ${row[0]} is ${row[1]}" }
这篇关于Grails在SQL中计算字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!