mysql - JQL 中的翻译内连接

Question

我的 JQL 中的 INNER JOIN 存在问题。我想要做的是检索所有对一个用户 Z 进行评分且平均值 >= 2 的用户。用户必须回答 3 个问题，这将决定该用户 Z 的最终评分。有 2 个表，用户和分数。

Table User    Table Score
Id_user       Id_score    Id_rated    Id_rater    Id_question    Score
1             1           1           3           1               1
2             2           1           3           2               0
3             3           1           3           3               1
              4           1           2           1               0
              5           1           2           2               0
              6           1           2           3               0

例如，我只想要用户 2。这是我将查询转换为 JQL 时出现的错误:

[32, 184] The join association path is not a valid expression.

我的查询在 MySQL Workbench 中运行良好

SELECT *
FROM User
INNER JOIN (
    SELECT ID_RATER, (AVG(Score.score)*5) as AvgScore
    FROM Score
    WHERE ID_RATED=1751 
    AND (
        SELECT (AVG(Score.score) * 3) 
        FROM Score)
    GROUP BY ID_RATER
) TabAvg
ON TabAvg.AvgScore >= 2
AND USER.ID=TabAvg.ID_RATER

转换为 JQL:

String sql = 
                    "SELECT U "
                    + "FROM User U "
                    + "INNER JOIN "
                    + "("
                        + "SELECT S.id_rater, (AVG(S.Score) * 3) AS AvgScore "
                        + "FROM Score S "
                        + "WHERE S.id_rated= :id_rated"
                        + "AND "
                        + "("
                            + "SELECT (AVG(S2.score) * 3) "
                            + "FROM Score S2"
                        + ") "
                        + "GROUP BY S.id_rater"
                    + ") TabAvg "
                    + "ON TabAvg.AvgScore >= 2 "
                    + "AND U.id_user = TabAvg.id_rater";

Answer 1

好吧，我还是不知道问题出在哪里

EntityManager.createQuery(sql)

但为了绕过这个，我使用了

EntityManager.createNativeQuery(sql)

只需将我的工作 SQL 复制/粘贴到我的代码中即可。