我必须创建一个电影数据库,其中一个查询涉及显示用户已“评级”的电影列表以及实际评级和流派。
此外,它必须显示该类型在用户已评分的所有电影中所占的百分比。例如:
CREATE TABLE GENRES (
MOVIEID INT NOT NULL,
GENRE VARCHAR(20),
PRIMARY KEY(MOVIEID),
FOREIGN KEY(MOVIEID) REFERENCES MOVIES(ID)
);
查询:
SELECT U.USERID, M.TITLE, U.USERRATING, G.GENRE
FROM USER_RATEDMOVIES U, MOVIES M, GENRES G
WHERE (M.ID = U.MOVIEID) AND (U.USERID LIKE "EXAMPLE") AND (G.MOVIEID = M.ID)
ORDER BY DATE_YEAR, DATE_MONTH, DATE_DAY, DATE_HOUR, DATE_MINUTE, DATE_SECOND;
这将返回我需要的所有内容,但占总数的百分比除外。那就是我迷路的地方。我是SQL的新手,正在与COUNT(*)和GROUP BY斗争。
最佳答案
您需要解决的挑战是将单位级别的数据与汇总级别的数据一起呈现。因此,请考虑使用聚合的groupby派生表(FROM
或JOIN
子句中的嵌套选择语句),这些表与先前设置的查询相关联(匹配USERID
)(使用显式联接而不是隐式联接):
SELECT U.USERID, M.TITLE, U.USERRATING, G.GENRE,
(gdT.GENRE_COUNT / udT.RATING_COUNT) AS PERCENTAGE
FROM USER_RATEDMOVIES U
INNER JOIN MOVIES M ON M.ID = U.MOVIEID
INNER JOIN GENRES G ON M.ID = G.MOVIEID
INNER JOIN
-- COUNTS BY CORRESPONDING USER AND GENRE
(SELECT subU.USERID, subG.GENRE, Count(*) AS GENRE_COUNT
FROM USER_RATEDMOVIES subU
INNER JOIN MOVIES subM ON subM.ID = subU.MOVIEID
INNER JOIN GENRES subG ON subM.ID = subG.MOVIEID
GROUP BY subU.USERID, subG.GENRE) AS gdT
ON gdT.USERID = U.USERID AND gdT.GENRE = G.GENRE
INNER JOIN
-- COUNTS BY CORRESPONDING USER
(SELECT subU.USERID, Count(*) AS RATING_COUNT
FROM USER_RATEDMOVIES subU
GROUP BY subU.USERID) AS udT
ON udT.USERID = U.USERID
WHERE U.USERID LIKE "EXAMPLE"
ORDER BY DATE_YEAR, DATE_MONTH, DATE_DAY,
DATE_HOUR, DATE_MINUTE, DATE_SECOND;