jOOQ生成的玩家排名嵌套查询SQL在MySQL/MariaDB中无法运行

看起来你遇到的是MySQL衍生表无法引用外部查询表的语法限制——你的原代码里，tr3这个衍生表（from子句里的子查询）试图引用外部的tr1表，而MySQL不允许衍生表访问外部查询的列，这会直接抛出类似Unknown column 'tr1.player_id' in 'where clause'的错误。

下面给你两种可行的解决方案，优先推荐第一种，因为它更高效且代码更简洁：

方案一：使用窗口函数（MySQL 8.0+ 推荐） #

从MySQL 8.0开始支持窗口函数，用ROW_NUMBER()或RANK()给每个玩家的成绩按分数降序排名，然后筛选前3名再求和，这比相关子查询效率高太多，jOOQ也完美支持这种写法：

import org.jooq.impl.DSL._

val tr = TOUR_RESULT.as("tr")

// 第一步：给每个玩家的成绩添加排名（按分数从高到低）
val rankedScores = sql.select(
  tr.PLAYER_ID,
  tr.NSP_SCORE,
  rowNumber()
    .over(partitionBy(tr.PLAYER_ID).orderBy(tr.NSP_SCORE.desc()))
    .as("rank")
).from(tr).asTable("ranked_scores")

// 第二步：筛选排名≤3的成绩，分组求和得到每个玩家的最佳3项总分
val finalResult = sql.select(
  rankedScores.field(tr.PLAYER_ID),
  sum(rankedScores.field(tr.NSP_SCORE)).as("top3_total")
).from(rankedScores)
 .where(rankedScores.field("rank").le(3))
 .groupBy(rankedScores.field(tr.PLAYER_ID))
 .fetch()

如果你的成绩可能有并列（比如两个相同的最高分），可以把rowNumber()换成rank()，这样并列成绩会获得相同排名，避免误筛掉并列的有效成绩。

方案二：兼容旧版MySQL的相关子查询写法 #

如果你的MySQL版本低于8.0，不能用窗口函数，那需要把相关子查询放到SELECT子句里（而不是from子句的衍生表），这样就能合法引用外部的tr1表：

val tr1 = TOUR_RESULT.as("tr1")
val tr2 = TOUR_RESULT.as("tr2")

// 先筛选出每个玩家排名前3的成绩
val top3Scores = sql.select(
  tr1.PLAYER_ID,
  tr1.NSP_SCORE,
  // 子查询统计同玩家中分数≥当前成绩的数量（即排名）
  select(count())
    .from(tr2)
    .where(tr2.PLAYER_ID.eq(tr1.PLAYER_ID))
    .and(tr2.NSP_SCORE.ge(tr1.NSP_SCORE))
    .as("rank")
).from(tr1)
 .having(field("rank").le(3))
 .groupBy(tr1.PLAYER_ID, tr1.NSP_SCORE)
 .asTable("top3_scores")

// 再对筛选后的成绩分组求和
val finalResult = sql.select(
  top3Scores.field(tr1.PLAYER_ID),
  sum(top3Scores.field(tr1.NSP_SCORE)).as("top3_total")
).from(top3Scores)
 .groupBy(top3Scores.field(tr1.PLAYER_ID))
 .fetch()

注意这种写法的效率会随着数据量增大而急剧下降，因为每个tr1的记录都会触发一次子查询，所以如果能升级到MySQL 8.0，优先用方案一。

另外补充两个小细节：

• 原代码里你用java.lang.Integer接收count()的结果，其实count()返回的是Long类型，建议调整类型避免转换错误；
• 如果NSP_SCORE是小数类型（比如Double），sum的时候要注意结果的类型匹配。

内容的提问来源于stack exchange，提问作者Roman Kellner