我在mysql工作,为评论、评论和电视剧制作了一个假数据库。所以我做了3个不同的表格,一个是给评论者的,一个是给评论的,一个是给系列的。
CREATE TABLE reviewers
(
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(150) NOT NULL
);
CREATE TABLE series
(
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
released_year YEAR(4),
genre VARCHAR(50)
);
CREATE TABLE reviews(
id INT AUTO_INCREMENT PRIMARY KEY,
rating DECIMAL(2,1),
series_id INT,
reviewer_id INT,
FOREIGN KEY(series_id) REFERENCES series(id),
FOREIGN KEY(reviewer_id) REFERENCES reviewers(id)
);
我想问的是,我怎样才能从每个评论员那里得到最高的评价,以及他们在哪个节目中给出的评价?
更新
我想出了这个密码
SELECT first_name,last_name,title, a.series_id,a.rating FROM
( SELECT series_id,MAX(rating) AS max FROM reviews
GROUP BY series_id ) AS b
INNER JOIN reviews AS a
ON a.series_id=b.series_id AND a.rating=b.max
INNER JOIN reviewers
ON reviewers.id=a.reviewer_id
INNER JOIN series
ON series.id=a.series_id
GROUP BY series_id;
这给了我每个系列的最高评分,谁给的评分
暂无答案!
目前还没有任何答案,快来回答吧!