需求 电影名、类别、播放次数、评分
已知的表结构
MOVIE表
MOVIE_ID
TITLE
YEAR
BUDGET
GROSS
PLOT_SUMMARY
MOVIE_GENRE表
MOVIE_ID
GENER_ID
GENRE表
GENRE_ID
NAME
MOVIE_FACT表
CUST_ID
MOVIE_ID
GENRE_ID
ACTIVITY_ID
CUST_RATING表
USERID
MOVIEID
RATING
SELECT A.MOVIE_ID,
A.TITLE,
A.GENRE,
NVL(B.COUNT,0) AS PLAY_COUNT,
NVL(C.RATING,0) AS RATING
FROM
(SELECT MOVIE.MOVIE_ID,
MOVIE.TITLE,
LISTAGG(GENRE.NAME,',') WITHIN GROUP(
ORDER BY GENRE.NAME) AS GENRE
FROM MOVIE,
MOVIE_GENRE,
GENRE
WHERE MOVIE.MOVIE_ID = MOVIE_GENRE.MOVIE_ID
AND GENRE.GENRE_ID = MOVIE_GENRE.GENRE_ID
GROUP BY MOVIE.MOVIE_ID,
MOVIE.TITLE
) A
LEFT JOIN
(SELECT MOVIE_ID,
COUNT(MOVIE_ID) AS COUNT
FROM MOVIE_FACT
WHERE ACTIVITY_ID = 4
GROUP BY MOVIE_ID
) B
ON B.MOVIE_ID = A.MOVIE_ID
LEFT JOIN
(SELECT CUST_RATING.MOVIEID AS MOVIE_ID,
COUNT(CUST_RATING.RATING) AS RATING
FROM CUST_RATING
GROUP BY CUST_RATING.MOVIEID
) C
ON A.MOVIE_ID = C.MOVIE_ID
已知的表结构
MOVIE表
MOVIE_ID
TITLE
YEAR
BUDGET
GROSS
PLOT_SUMMARY
MOVIE_GENRE表
MOVIE_ID
GENER_ID
GENRE表
GENRE_ID
NAME
MOVIE_FACT表
CUST_ID
MOVIE_ID
GENRE_ID
ACTIVITY_ID
CUST_RATING表
USERID
MOVIEID
RATING
SELECT A.MOVIE_ID,
A.TITLE,
A.GENRE,
NVL(B.COUNT,0) AS PLAY_COUNT,
NVL(C.RATING,0) AS RATING
FROM
(SELECT MOVIE.MOVIE_ID,
MOVIE.TITLE,
LISTAGG(GENRE.NAME,',') WITHIN GROUP(
ORDER BY GENRE.NAME) AS GENRE
FROM MOVIE,
MOVIE_GENRE,
GENRE
WHERE MOVIE.MOVIE_ID = MOVIE_GENRE.MOVIE_ID
AND GENRE.GENRE_ID = MOVIE_GENRE.GENRE_ID
GROUP BY MOVIE.MOVIE_ID,
MOVIE.TITLE
) A
LEFT JOIN
(SELECT MOVIE_ID,
COUNT(MOVIE_ID) AS COUNT
FROM MOVIE_FACT
WHERE ACTIVITY_ID = 4
GROUP BY MOVIE_ID
) B
ON B.MOVIE_ID = A.MOVIE_ID
LEFT JOIN
(SELECT CUST_RATING.MOVIEID AS MOVIE_ID,
COUNT(CUST_RATING.RATING) AS RATING
FROM CUST_RATING
GROUP BY CUST_RATING.MOVIEID
) C
ON A.MOVIE_ID = C.MOVIE_ID