sql - SQL 查询之谜

Question

我有一个我无法完成的任务。这些是要求：

获取完整的人名单（即使没有参演任何电影），显示国家、姓名和出生日期、该人作为演员参演的电影数量以及参演的​​电影数量该人曾作为编剧参与，按国家排序，按出生日期排序。

到目前为止，这是我想出的：

SELECT person_name, person_country, person_dob, COUNT (DISTINCT movie_name) WHERE role_name='Actor' OR role_name='Guionista' GROUP BY role_name
FROM movies.tb_person LEFT JOIN movies.tb_movie_person 
ON tb_person.person_id = tb_movie_person.person_id
LEFT JOIN movies.tb_role
ON tb_role.role_id = tb_movie_person.role_id;

有什么想法我哪里出错了吗？

亲切的问候

标签： sqlgroup-byleft-join

Answer 1

declare @tb_Person table(person_sid int not null, person varchar(80));
declare @tb_Person_Movie table(person_sid int not null, role_sid int not null);
declare @tb_Role table(role_sid int not null, [role] varchar(80));

insert into @tb_Role values(1,'Actor');
insert into @tb_Role values(2,'Screenwriter');

insert into @tb_Person values(1,'Al Pacino');
insert into @tb_Person values(2,'Bob deNiro');
insert into @tb_Person values(3,'Mickey Rourke');
insert into @tb_Person values(4,'Jodie Foster');

insert into @tb_Person_Movie values(1, 1);--al pacino actor
insert into @tb_Person_Movie values(2, 1);--bob deniro actor
insert into @tb_Person_Movie values(3, 2);--mickey rourke screenwriter
--jodie none

select
    p.person_sid,
    p.person,
    SUM(case when r.[role] = 'Actor' then 1 else 0 end) AsActor,
    SUM(case when r.[role] = 'Screenwriter' then 1 else 0 end) AsScreenwriter
from 
    @tb_Person p
    left join @tb_Person_Movie pm on pm.person_sid = p.person_sid
    left join @tb_Role r on pm.role_sid = r.role_sid
group by 
    p.person_sid, p.person, r.[role]