1.最近工程中需要按照年,月,季度对一些记录进行统计。年,月的统计很快解决了,但是在
按照季度统计时,遇到了一些问题,不过,最终还是解决了。
2.
(1)按照年统计。
String sqlYear ="select cast(sum(money) as float),year(loadTime),count(book) from LoadEbook group by year(loadTime)";
def loadYearEbook = LoadEbook.executeQuery(sqlYear)
打印查询到的结果,发现money的小数位不对,money定义的是float类型,例如查询结果是
21.78,但是打印出来的是
所以要将查询到的结果转换下,这里将sum(money)变成cast(sum(money) as float),再次打印查询结果,这次打印正确,结果如下:
(2)按照月统计
String sqlMonth ="select cast(sum(money) as float),month(loadTime),count(book) from LoadEbook group by month(loadTime)";
def loadMonthEbook = LoadEbook.executeQuery(sqlMonth)
与按照年查询一样。
(3)按照季度查询,本来也想按照和年,月查询的方式进行查询,刚开始是这么写的:
String sqlMonth ="select cast(sum(money) as float),quarter(loadTime),count(book) from LoadEbook group by quarter(loadTime)";
def loadMonthEbook = LoadEbook.executeQuery(sqlMonth)
但是会报错,报错如下:
上网查了下,HQL: The Hibernate Query Language,发现它的Expression中不包含quarter,所以不能使用quarter。没办法,只能再找其他办法了,上网又找了下,最后通过
case when end解决了。代码如下;
String sqlQuarter = "Select cast(sum(money) as float),case when month(loadTime) in (1,2,3) then '一季度' when month(loadTime) in (4,5,6) then '二季度' when month(loadTime) in (7,8,9) then '三季度' when month(loadTime) in (10,11,12) then '四季度' end ,count(book)"+
" from LoadEbook group by case when month(loadTime) in (1,2,3) then '一季度' when month(loadTime) in (4,5,6) then '二季度' when month(loadTime) in (7,8,9) then '三季度' when month(loadTime) in (10,11,12) then '四季度' end ";def loadQuarterEbook = LoadEbook.executeQuery(sqlQuarter)
这次能够正确打印结果了。
上边就是按照年月季度查询的代码。