使用subquery查找某些电影评分为5的用户id的SQL查询

Question

我正在使用JAVA（JDBC？Hive？）找到SQL的方法来拉出用户列表的评分为5的电影。我在使用子查询，但似乎总是出了问题。

null

SELECT rt.u_id
FROM rate rt
INNER JOIN movie mv ON rt.m_id = mv.m_id
WHERE mv.romance = 1 AND rt.rating = 5
HAVING COUNT(rt.m_id) = (
    SELECT m_id FROM (
        SELECT rt.m_id, COUNT(rt.m_id) AS m_count
        FROM rate rt
        INNER JOIN movie mv ON rt.m_id = mv.m_id
        WHERE mv.romance = 1 AND rt.rating = 5
        GROUP BY rt.m_id
        ORDER BY m_count DESC
        LIMIT 1
    ) AS RESULT
)   
ORDER BY rt.u_id ASC;

谢谢：）

Answer 1

您的子查询不是可以使用别名的表或列。

但是count(rt.m_id)=意味着您只能从子查询中返回一个结果，否则您将得到一个错误，所以您应该检查它

SELECT rt.u_id
FROM rate rt
INNER JOIN movie mv ON rt.m_id = mv.m_id
WHERE mv.ad = 1 AND rt.rating = 5
HAVING COUNT(rt.m_id) = (
    SELECT  COUNT(rt.m_id) AS M_COUNT
    FROM rate rt
    INNER JOIN movie mv ON rt.m_id = mv.m_id
    WHERE mv.ad = 1 AND rt.rating = 5
    GROUP BY rt.m_id
    ORDER BY rt.m_id DESC
    LIMIT 1
);