MySQL - Joins and Between

Question

列出20世纪50年代和80年代拍摄电影的所有演员。

当我只尝试时：

SELECT a.name
FROM movies AS m
JOIN castings AS c
JOIN actors AS a
ON m.id = c.movieid
AND c.actorid = a.id
WHERE (m.yr BETWEEN 1950 AND 1959)

或：

SELECT a.name
FROM movies AS m
JOIN castings AS c
JOIN actors AS a
ON m.id = c.movieid
AND c.actorid = a.id
WHERE (m.yr BETWEEN 1980 AND 1989)

我得到两个单独查询的结果。然而，当我组合这些时，我没有得到任何行。

SELECT a.name
FROM movies AS m1
JOIN movies AS m2
JOIN castings AS c
JOIN actors AS a
ON m1.id = c.movieid
AND m2.id = c.movieid
AND c.actorid = a.id
AND m1.id < m2.id
WHERE (m1.yr BETWEEN 1950 AND 1959) AND (m2.yr BETWEEN 1980 AND 1989);

我怎样才能找到我要找的名字？

Answer 1

从现有查询开始，一种直接的方法是使用聚合，并使用HAVING子句进行筛选：

SELECT a.name
FROM movies AS m
JOIN castings AS c ON m.id = c.movieid
JOIN actors AS a ON c.actorid = a.id
GROUP BY a.id, a.name
HAVING 
MAX(m.yr BETWEEN 1950 AND 1959) = 1 
AND MAX(m.yr BETWEEN 1980 AND 1989) = 1 

Answer 2

您可以在where子句中使用子查询。

SELECT 
a.name 
FROM
actors AS a 
WHERE EXISTS 
(SELECT 
1 
FROM
movies m,
castings c 
WHERE c.actorid = a.id 
AND m.id = c.movieid 
AND (m.yr BETWEEN 1950 
AND 1959) 
LIMIT 1) 
OR EXISTS
(SELECT 
1 
FROM
movies m,
castings c 
WHERE c.actorid = a.id 
AND m.id = c.movieid 
AND (m.yr BETWEEN 1980 
AND 1989) 
LIMIT 1)

或者你可以像下面这样使用Group By，同时获取电影数量：

SELECT 
a.name,
SUM(IF(m.yr BETWEEN 1950 AND 1959, 1, 0)) AS e1950,     -- Number of Movies in 1950s
SUM(IF(m.yr BETWEEN 1980 AND 1989, 1, 0)) AS e1980      -- Number of Movies in 1980s
FROM movies AS m
JOIN castings AS c
JOIN actors AS a
ON m.id = c.movieid
AND c.actorid = a.id
GROUP BY a.name HAVING e1950 > 0 AND e1980 > 0;