mysql-joins和between

zzoitvuj 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(376)

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

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

sql mysql Join Between

来源：https://stackoverflow.com/questions/63077802/mysql-joins-and-between

2条答案

按热度按时间

2eafrhcq1#

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

赞(0）回复(0）举报 2021-07-24

kyxcudwk2#

可以在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;

赞(0）回复(0）举报 2021-07-24

我来回答

mysql-joins和between

2条答案

相关问题

热门标签

最新问答