时间戳

首页 > 解决方案 > MySQL - 连接和之间

mysql - MySQL - 连接和之间

问题描述

列出所有在 1950 年代和 1980 年代制作电影的演员。

当我专门尝试时：

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);

我怎样才能找到我正在寻找的名字？

标签： mysqlsqljoinbetween

解决方案

从您现有的查询开始，一种直接的方法是使用聚合，并使用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

推荐阅读