我的问题是“统计每年有多少部电影只有女演员”。
表架构如下:
-------------------- ----------------------- ----------------------
| Movie | | Person | | Cast |
-------------------- ------------------------ ----------------------
| MID | year | | PID | Gender | | MID | PID |
-------------------- ------------------------ ----------------------
我编写了以下MySQL查询:
SELECT m.year, COUNT(m.MID) AS Movies_Female FROM Movie m
WHERE m.MID IN ( SELECT Q.MID FROM ( SELECT m_c.MID, COUNT(m_c.PID) AS No_Actors FROM M_Cast m_c GROUP BY m_c.MID) Q
WHERE Q.No_Actors = ( SELECT COUNT(m__c.PID) FROM M_Cast m__c JOIN PERSON p
ON TRIM(p.PID) = TRIM(m__c.PID)
WHERE m__c.MID = Q.MID AND TRIM(p.Gender) = 'Female')) GROUP BY m.year
但是我得到的输出是:
year Movies_Female
0 1939 1
1 1999 1
2 2000 1
3 2009 1
4 2012 1
5 2018 1
但是当我手工检查2009年和2012年的时候，没有任何一部电影只有女性演员。 请解决我的查询出错的地方？
您可以使用不存在筛选至少有一个非女性演员的电影:
select m.year, count(*) as movies_female
from movie m
where not exists (
select 1
from cast c
inner join person p on p.pid = c.pid
where c.mid = m.mid and p.gender <> 'Female'
)
group by m.year
这是假设每部电影在cast中至少有一个条目（这似乎是一个合理的假设）。
