java - Derby 错误 "Subquery is only allowed to return a single column."

Question

调用调用此查询的方法时出现此异常:

SELECT s FROM Survey s 
WHERE s.idsurvey NOT IN 
    (SELECT DISTINCT s FROM Useranswer ua 
        JOIN ua.iduser u 
        JOIN ua.idanswer a
        JOIN a.idquestion q
        JOIN q.idsurvey s 
    WHERE 
        ua.iduser.iduser = u.iduser
        AND ua.idanswer.idanswer = a.idanswer
        AND a.idquestion.idquestion = q.idquestion
        AND q.idsurvey.idsurvey = s.idsurvey
        AND u.iduser = :iduser
    )
order by s.dateEnd

有什么帮助吗？查询似乎没问题。

Answer 1

我花了近 2 个小时试图解决这个问题。我找到了使用 native sql 的解决方案。

String query = "select s.IDSURVEY, s.DATE_END from survey s where s.IDSURVEY not IN (select distinct s.IDSURVEY from survey s join question q on s.IDSURVEY = q.IDSURVEY join answer a on q. IDQUESTION = a.IDQUESTION join useranswer ua on a.IDANSWER = ua.IDANSWER where ua.IDUSER = "+iduser+") order by s.DATE_END";

return (List<Survey>)em.createNativeQuery(query).getResultList();