mysql - 加入表后获取最小值

Question

这是我的数据库:

create table Movie (
mID smallint auto_increment primary key, 
title varchar(30), 
director varchar(20),
year int
)engine innodb;

create table Reviewer (
rID smallint auto_increment primary key, 
name varchar(30)
)engine innodb;

create table Rating (
mID smallint, 
rID smallint, 
stars int, 
ratingDate date,
constraint fk_movie foreign key (mID) references movie(mID),
constraint fk_reviewer foreign key (rID) references reviewer(rID)
)engine innodb;

insert into movie (title,director,year) values ('Lincoln','Steven Spielberg',2012),('An Unexpected Journey','Peter Jackson',2012),('Jurassic Park','Steven Spielberg',1993),('The Godfather','Francis Ford Coppola',1972), ('Fight Club','David Fincher',1999),('War Horse','Steven Spielberg',2011),('Rise of guardians','Peter Ramson',2012),('The Shawshank Redemption','Frank Darabont',1994),('Jaws','Steven Spielberg',1975),('Raging Bull','Martin Scorsese',1980);

insert into reviewer (name) values ('Bill Goodykoontz'),('David Germain'),('Christy Lemire'),('Richard Lawson'),('Marjorie Baumgarten');

insert into rating (mID, rID, stars, ratingDate) values (1,4,3,'2012-04-09'),(3,5,4,'2000-03-05'),(4,2,5,'1900-09-15'),(7,3,5,'2012-08-25'),(6,1,4,'2011-09-11'),(8,5,5,'1995-10-15'),(9,3,3,'1980-02-20');

这是 SQL Fiddle插入所有这些数据。

我必须:“对于数据库中当前最低(最少星星)的每个评分，返回评论者姓名、电影名称和星星数。”

我的代码是:

SELECT name REVIEWER, title MOVIE, stars FROM rating
INNER JOIN movie
USING(mID)
INNER JOIN reviewer
USING(rID)
GROUP BY stars
HAVING stars = min(stars);

我的输出是

REVIEWER    MOVIE   stars
Christy Lemire      Jaws    3
Bill Goodykoontz    War Horse   4
David Germain       The Godfather   5

理想输出

REVIEWER    MOVIE   stars
Christy Lemire  Jaws    3
Richard Lawson  Lincoln 3

请有人纠正我的代码。

Answer 1

你不是在找这个吗:

    SELECT name REVIEWER, title MOVIE, stars FROM rating
INNER JOIN movie    USING(mID)
INNER JOIN reviewer USING(rID)
WHERE stars = 
  (SELECT min(stars) FROM rating);

SQL Fiddle .