Android Room Database Query 是否支持 row_number?

Question

我正在尝试在我的 android 房间数据库中运行以下 sqlite 查询，但它抱怨有几个错误。

@Query("with cte as (n" +
"  select t.exercise_name,t.band, t.reps, t.weightn" +
"  from ( n" +
"    select *,n"+      "" +
"      row_number() over (partition by band order by reps desc) rnreps,n" +
"      row_number() over (partition by band, reps order by weight desc) rnweight" +
"    from tracked_exercisesn" +
"  ) t  n" +
"  where exercise_name = :namen" +
")  n" +
"select * from cte cn" +
"where not exists (n" +
"  select 1 from cten" +
"  where band = c.band and reps > c.reps and weight >= c.weightn" +
")  n")
List<TrackedExercise> getPersonalRecords(String name);

1 就在隔板前面的支架之前"<compound operator>, FROM, GROUP, LIMIT, ORDER, WHERE or comma expected, got '('"

另一个是当我尝试编译"error: no viable alternative at input 'with cte as...."

我设法在这里获得相同的查询 小提琴

是 Room 不支持row_number() over (partition'条款还是我遗漏了什么？

编辑： 刚刚发现这个 Android Room + Window 函数看起来这个函数仅在 SQLite 3.25 中可用，而更高的 Android 只能升级到 3.19。如果有人对如何在不使用 Windows 函数的情况下创建执行相同操作的查询有任何想法，我仍然感兴趣

Answer 1

我想我使用 2 个 CTE 找到了解决问题的方法，然后加入结果

@Query("-- gets the max reps at each set of exercise_name, band, weightn" +
"with repsCTE as (n" +
"  select *,max(reps) repsn" +
"  from tracked_exercisesn" +
"  where exercise_name = :namen" +
"  group by exercise_name, band, weight ), n" +
"n" +
"-- gets the max weight at each set of exercise_name, band, repsn" +
"weightCTE as (n" +
"  select *,max(weight) weightn" +
"  from tracked_exercisesn" +
"  where exercise_name = :namen" +
"  group by exercise_name, band, reps ) n" +
"n" +
"select * n" +
"from repsCTE r join weightCTE wn" +
"on r.id = w.idn" +
"order by band,reps" )
List<TrackedExercise> getPersonalRecords(String name);

小提琴