我有一个SQL作业问题，因此我不确定如何去写查询。基本上，这就是问题所在:
某研究机构要求提供所有电影系列创作者的姓名，以及他们创作的"家庭电影"电影的数量(即使他们没有创作)。该机构希望这份名单按照从多到少的顺序排列;创作最多家庭电影的创作者将在名单上名列前茅，而创作最少家庭电影的创作者将在名单上垫底。编写一个查询，提供这些信息，并包含有用的列。
下面是设置表和数据的脚本:
Create Table Genre
(
Genre_id decimal(12) NOT NULL Primary Key
, Genre_name varchar(64) NOT NULL
)
Create Table Creator
(
Creator_id decimal(12) not null Primary Key
, first_name varchar(64) NOT NULL
, last_name varchar(64) NOT NULL
)
Create Table Movie_series
(
movie_series_id decimal(12) not null Primary Key
, genre_id decimal(12) NOT NULL Foreign key references Genre(genre_id)
, creator_id decimal(12) NOT NULL Foreign key references Creator(Creator_id)
, series_name varchar(255) NOT NULL
, suggested_price decimal(8,2) NULL
)
Create Table Movie
(
Movie_id decimal(12) not null Primary Key
, movie_series_id decimal(12) NOT NULL Foreign key references Movie_series(movie_series_id)
, movie_name varchar(64) NOT NULL
, length_in_minutes decimal(4)
)
这是将数据分别插入到表中的脚本
--Inserting data to Genre Table
Insert into Genre (Genre_id, Genre_name)
values
(1, 'Fantasy'),
(2, 'Family Film'),
(3, 'Action spy')
--Inserting data to Creator Table
Insert into Creator (Creator_id, first_name, last_name)
values
(1, 'George', 'Lucas'),
(2, 'John', 'Lasseter'),
(3, 'John', 'Tolkien'),
(4, 'Bruce', 'Gellar')
--Inserting data to Movie_series Table
Insert into Movie_series (movie_series_id, genre_id, creator_id, series_name, suggested_price)
values
(1, 1, 1, 'Star Wars', 129.99),
(2, 2, 2, 'Toy Story', 22.13),
(3, 1, 3, 'Lord of the Rings', NULL),
(4, 3, 4, 'Mission Impossible', 50.00)
--Inserting data to Movie Table
Insert into Movie (Movie_id, movie_series_id, movie_name, length_in_minutes)
values
(1, 1, 'Episode I: The Phantom Menace', 136),
(2, 1, 'Episode II: Attack of the Clones', 142),
(3, 1, 'Episode III: Revenge of the Sith', 140),
(4, 1, 'Episode IV: A New Hope', 121),
(5, 2, 'Toy Story', 121),
(6, 2, 'Toy Story 2', 135),
(7, 2, 'Toy Story 3', 148),
(8, 3, 'The Lord of the Rings: The Fellowship of the Ring', 228),
(9, 3, 'The Lord of the Rings: The Two Towers', 235),
(10, 3, 'The Lord of the Rings: The Return of the King', 200),
(11, 4, 'Mission: Impossible', 110),
(12, 4, 'Mission: Impossible 2', 123),
(13, 4, 'Mission: Impossible III', 126),
(14, 4, 'Mission: Impossible - Ghost Protocol', 133),
(15, 4, 'Mission: Impossible - Rogue Nation', 131),
(16, 4, 'Mission: Impossible - Fallout', 147)
Creator - [Number of "Family Film" movies]
John Lasseter - 3
George Lucas - 0
John Tolkien - 0
Bruce Gellar - 0
但是，我不确定如何使用连接将数据和计数连接在一起。这是我尝试的查询:
select count(ms.genre_id), g.Genre_name, c.first_name + ' ' + c.last_name as [Creator]
from Movie m
inner join Movie_series ms on ms.movie_series_id = m.movie_series_id
inner join Creator c on ms.creator_id = c.Creator_id
left join Genre g on ms.genre_id = g.Genre_id and g.Genre_name = 'Family Film'
group by ms.genre_id, c.first_name, c.last_name, g.Genre_name
任何帮助都将非常感激。提前谢谢。
需要外部连接而不是内部连接。从creator开始，左接movie_series、movies和genre。通过使用左连接，您可以确保所有创建者都在结果中——左连接保留左关系中的所有行。
你只想按创作者分组，而不是按流派分组。但是，请确保在GROUP BY子句中包含creator_id，因为只使用名称列可能会合并两个实际上不同的创建者，只是共享一个名称。
使用ORDER BY子句按计数对结果进行排序。
SELECT c.first_name,
c.last_name,
count(g.genre_id)
FROM creator c
LEFT JOIN movie_series ms
ON ms.creator_id = c.creator_id
LEFT JOIN movie m
ON m.movie_series_id = ms.movie_series_id
LEFT JOIN genre g
ON g.genre_id = ms.genre_id
AND g.genre_name = 'Family Film'
GROUP BY c.creator_id,
c.first_name,
c.last_name
ORDER BY count(g.genre_id) DESC;
,db<的在小提琴
您需要将Creator作为您一直拥有的驱动行，并将Movie连接嵌套在Movie_series的左连接中。
由于始终需要Genre名称，因此需要首先有效地交叉连接这一行，然后在Movie_series连接
Select count(ms.genre_id), g.Genre_name, c.first_name + ' ' + c.last_name as [Creator]
from
Creator c
inner join Genre g on g.Genre_name = 'Family Film'
left join Movie_series ms
inner join Movie m on ms.movie_series_id = m.movie_series_id
on ms.creator_id = c.Creator_id and ms.genre_id = g.Genre_id
group by c.creator_id, c.first_name, c.last_name, g.Genre_name
order by count(ms.genre_id) desc;