【MySQL】多对多关系Jions相关运用

SELECT * FROM reviewers;
SELECT * FROM series;
SELECT * FROM reviews;


-- 不同的评分对应不同的节目，也对应不同的reviewer
-- 一共7个reviewers，每个reviewer可能对好几部电影都做了评分


-- -- 挑战1：JOIN和INNER JOIN -- --
-- JOIN：根据两个或多个表之间的相关列组合它们的行。
-- INNER JOIN：选择两个表中具有匹配值的记录。
SELECT 
    * 
FROM series
JOIN reviews
    ON series.id = reviews.series_id;


SELECT 
    *
FROM series
INNER JOIN reviews
    ON series.id = reviews.series_id;


-- -- 挑战2：每个series的平均评分 -- --
SELECT
    title,
    AVG(rating) as avg_rating
FROM series
JOIN reviews
    ON series.id = reviews.series_id
GROUP BY series.id
ORDER BY avg_rating;


-- -- 挑战3：INNER JOIN -- --
SELECT
    first_name,
    last_name,
    rating
FROM reviewers
INNER JOIN reviews
    ON reviewers.id = reviews.reviewer_id;
    
SELECT
    first_name,
    last_name,
    rating
FROM reviews
INNER JOIN reviewers
    ON reviewers.id = reviews.reviewer_id;
    
-- -- 挑战4：找出没有被评分的series -- -- 
SELECT *
FROM series
LEFT JOIN reviews
    ON series.id = reviews.series_id;
    
SELECT title AS unreviewed_series
FROM series
LEFT JOIN reviews
    ON series.id = reviews.series_id
WHERE rating IS NULL;


-- -- 挑战5：得出每种类型的series的平均分数 -- --
SELECT genre, 
       Round(Avg(rating), 2) AS avg_rating 
FROM   series 
       INNER JOIN reviews 
               ON series.id = reviews.series_id 
GROUP  BY genre; 


-- -- 挑战6：通过评分次数，授予7个reviewers等级 -- --
-- SUM, AVG, COUNT
SELECT first_name, 
       last_name, 
       Count(rating)                               AS COUNT, 
       Ifnull(Min(rating), 0)                      AS MIN, 
       Ifnull(Max(rating), 0)                      AS MAX, 
       Round(Ifnull(Avg(rating), 0), 2)            AS AVG, 
       IF(Count(rating) > 0, 'ACTIVE', 'INACTIVE') AS STATUS 
FROM   reviewers 
       LEFT JOIN reviews 
              ON reviewers.id = reviews.reviewer_id 
GROUP  BY reviewers.id; 


SELECT first_name, 
       last_name, 
       Count(rating)                    AS COUNT, 
       Ifnull(Min(rating), 0)           AS MIN, 
       Ifnull(Max(rating), 0)           AS MAX, 
       Round(Ifnull(Avg(rating), 0), 2) AS AVG, 
       CASE 
         WHEN Count(rating) >= 10 THEN 'POWER USER' 
         WHEN Count(rating) > 0 THEN 'ACTIVE' 
         ELSE 'INACTIVE' 
       end                              AS STATUS 
FROM   reviewers 
       LEFT JOIN reviews 
              ON reviewers.id = reviews.reviewer_id 
GROUP  BY reviewers.id; 


-- -- 挑战7：3个表格结合运用 -- --
SELECT * FROM
reviewers
INNER JOIN reviews 
    ON reviewers.id = reviews.reviewer_id;


SELECT 
    title,
    rating,
    CONCAT(first_name,' ', last_name) AS reviewer
FROM reviewers
INNER JOIN reviews 
    ON reviewers.id = reviews.reviewer_id
INNER JOIN series
    ON series.id = reviews.series_id
ORDER BY title;