mysql - SQL:count() 的用法 - 困惑和解释

Question

我正在使用 SQL 中的以下数据库表(这些条目只是示例):

一张表来保存有关电影的信息:

****************** film ******************
filmid (int)    |   title (string)              | prodyear (int)
-----------------------------------------------------------------
2919898         | Devil May Cry                     | 2011
7246970         | Three Men Seeking Monsters        | 2010

一张表保存有关电影参与的信息:

****************** filmparticipation ******************
partid (int)    | personid (int) | filmid (int) | parttype (string)
--------------------------------------------------------------------
17              | 17             | 52               | costume designer
85              | 70             | 69               | director`enter code here`

还有一张表来保存有关人的信息:

****************** person ******************
personid (int)  | lastname (string) | firstname (string)    | gender (char)
--------------------------------------------------------------------------------
167076          | Jones             | Anthony               | M
197             | Mitchell          | Julia                 | F

现在回答问题:

1)我尝试编写一个查询来**查找一个人参与过的电影的名称和总数。我的查询如下所示:

select 
    p.lastname, p.firstname, count(distinct f.filmid) as numberOfMovies
from
    film f, filmparticipation x, person p
where
    x.filmid = f.filmid and
    x.personid = p.personid
group by 
    p.lastname, p.firstname

我相信我在 count() 方面遇到了问题，因为它为每个元组返回 1。

2) 类似的查询，获取克里斯托弗·诺兰执导的所有电影的 filmid、参与人数和制作年份(prodyear)。我的查询如下:

select
    f.title, f.filmid, count (distinct x.partid), prodyear
from
    film f, filmparticipation x, person p, filmparticipationinfo inf
where
    p.firstname='Christopher' and
    p.lastname='Nolan' and
    x.personid = p.personid and
    x.parttype = 'director' and
    x.filmid = f.filmid
group by
    f.title, f.filmid, prodyear
order by
    prodyear asc;

同样，在结果中，每部电影的 count() 设置为 1，这是不正确的。

如果有人能解释如何正确使用count()，我们将不胜感激。

Answer 1

使用 JOIN、子查询来获取正确的计数，您的查询应该是:

a) 查找一个人参与过的电影的名称和总数

SELECT 
    p.lastname,
    p.firstname,
    (SELECT COUNT(filmid) FROM filmparticipation WHERE personid = p.personid) AS numberOfMovies
FROM
    person p
GROUP BY
    p.lastname, p.firstname

b) 获取克里斯托弗·诺兰执导的所有电影的电影编号、参与人数和制作年份(prodyear)

SELECT
    f.title,
    f.filmid,
    (SELECT COUNT(partid) FROM filmparticipation WHERE filmid = f.filmid) AS numberOfPeople,
    f.prodyear
FROM
    film f
LEFT JOIN filmparticipation x ON f.filmid = x.filmid
LEFT JOIN person p ON x.personid = p.personid
WHERE
    p.firstname = 'Christopher'
    AND p.lastname='Nolan'
    AND x.parttype = 'director'
GROUP BY
    f.title, f.filmid, f.prodyear
ORDER BY
    f.prodyear ASC;