我想按计数(favorite.favorite\u id)顺序显示结果,并使用基于光标的分页。
但是,在查询中添加'having count()'子句时,我发现了arrayindexoutofboundsexception。
select
album0_.album_id as col_0_0_,
count(favoriteli2_.favorite_id) as cnt,
user1_.user_id as user_id1_8_1_,
album0_.album_id as album_id1_0_0_,
album0_.create_date as create_d2_0_0_,
album0_.modify_date as modify_d3_0_0_,
album0_.description as descript4_0_0_,
album0_.thumbnail as thumbnai5_0_0_,
album0_.title as title6_0_0_,
album0_.user_id as user_id8_0_0_,
album0_.version as version7_0_0_,
user1_.code as code2_8_1_,
user1_.introduction as introduc3_8_1_,
user1_.name as name4_8_1_,
user1_.pic as pic5_8_1_
from
album album0_
inner join
user user1_
on album0_.user_id=user1_.user_id
left outer join
favorite favoriteli2_
on album0_.album_id=favoriteli2_.album_id
group by
album0_.album_id
order by
cnt desc, album0_.album_id desc
上面的查询工作正常。
select
album0_.album_id as col_0_0_,
count(favoriteli2_.favorite_id) as cnt,
user1_.user_id as user_id1_8_1_,
album0_.album_id as album_id1_0_0_,
album0_.create_date as create_d2_0_0_,
album0_.modify_date as modify_d3_0_0_,
album0_.description as descript4_0_0_,
album0_.thumbnail as thumbnai5_0_0_,
album0_.title as title6_0_0_,
album0_.user_id as user_id8_0_0_,
album0_.version as version7_0_0_,
user1_.code as code2_8_1_,
user1_.introduction as introduc3_8_1_,
user1_.name as name4_8_1_,
user1_.pic as pic5_8_1_
from
album album0_
inner join
user user1_
on album0_.user_id=user1_.user_id
left outer join
favorite favoriteli2_
on album0_.album_id=favoriteli2_.album_id
group by
album0_.album_id
having
count(favoriteli2_.favorite_id) < 2 --this causes the problem
order by
cnt desc, album0_.album_id desc
但是这个有count()子句的方法不起作用。
下面是arrayindexoutofboundsexception发生的方法[org.h2.command.dml.select.updateagg(选择。java:542)]
/**
* Update any aggregate expressions with the query stage.
* @param columnCount number of columns
* @param stage see STAGE_RESET/STAGE_GROUP/STAGE_WINDOW in DataAnalysisOperation
*/
void updateAgg(int columnCount, int stage) {
for (int i = 0; i < columnCount; i++) {
if ((groupByExpression == null || !groupByExpression[i])
&& (groupByCopies == null || groupByCopies[i] < 0)) {
Expression expr = expressions.get(i);
expr.updateAggregate(session, stage);
}
}
}
列数=16
groupbyexpression:大小16
groupbycopies:大小15
表达式:大小16(包含15个选定列,1个比较(=count(favoriteli2.favorite\u id)<2))
我认为“groupbycopies”是错误的原因。我该怎么修?
1条答案
按热度按时间sqserrrh1#
虽然mysql/mariadb允许这样做,但我认为h2(像许多其他sql rdbms一样)希望groupby具有所有非聚合列(使用album\u id将它们全部添加到groupby中)。所以我想试试。事实上,您看到的异常是由逻辑从组中抛出的,这就是为什么我倾向于这样做的原因。