如何从三向联接表查询中列出第一个值? [英] How do I list the first value from a three-way joined table query?

问题描述

好吧，我在解释事情上很糟糕，所以我只先给你引号和链接:

Ugh ok I'm terrible at explaining things, so I'll just give you the quotes and links first:

问题4b (在底部附近):

4b.列出所有朱莉·安德鲁斯"电影的片名和最佳男主角.

4b. List the film title and the leading actor for all of 'Julie Andrews' films.

电影(ID，标题，年份，得分，投票，导演)
演员(ID，姓名)
演员表(电影，演员或奥德)
(注意:movie.id = cast.movi​​eid，actor.id = cast.actorid)

movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
(Note: movie.id = casting.movieid, actor.id = casting.actorid)

我的回答(无效):
    SELECT title, name
      FROM casting JOIN movie
              ON casting.movieid = movie.id
      JOIN actor
              ON casting.actorid = actor.id
     WHERE name = 'Julie Andrews'
       AND ord = 1

My answer (doesn't work):
    SELECT title, name
      FROM casting JOIN movie
              ON casting.movieid = movie.id
      JOIN actor
              ON casting.actorid = actor.id
     WHERE name = 'Julie Andrews'
       AND ord = 1

这里的问题是，它希望以朱莉·安德鲁斯"(Julie Andrews)作为演员的电影的男主角名单(不一定是男主角)，但是我要做的就是将电影放到她的位置是领先者(ord = 1).

The problem here is that it wants the list of lead actors of movies with 'Julie Andrews' as an actor (who is not necessarily the lead actor), but all I'm doing with my answer is getting the movies where she is the lead (ord = 1).

在没有朱莉·安德鲁斯"的情况下，如何指定主要演员名单?我怀疑我必须对GROUP BY做些事情，但是我现在不知道该怎么办...

How do I specify the list of lead actors without 'Julie Andrews' being it? I suspect I have to do something with GROUP BY, but I can't figure out what at the moment...

编辑:我需要使用嵌套的SELECT吗?

Do I need to use a nested SELECT?

推荐答案

使用子查询可以完成此操作，但是在本教程中看来这只是在使用JOIN.以下是仅使用JOIN的方法:

There are wonderful ways of doing this with subqueries, but it appears that t this point in the tutorial you're only working with JOINs. The following is how you would do it with only JOINs:

SELECT
  movie.title,
  a2.name
FROM
  actor AS a1
  JOIN casting AS c1 ON (a1.id = c1.actorid)
  JOIN movie ON (c1.movieid = movie.id)
  JOIN casting AS c2 ON (movie.id = c2.movieid)
  JOIN actor AS a2 ON (c2.actorid = a2.id)
WHERE 
  a1.name = 'Julie Andrews'
  AND c2.ord = 1

编辑(更具描述性):

这将为我们提供一个表，其中包含朱莉·安德鲁斯(Julie Andrews)所饰演的所有电影.我将演员和演员表分别命名为a1和c1，因为现在我们已经找到了电影列表，我们必须再次将其与铸造台匹配.

This will give us a table containing all of the movies Julie Andrews acted in. I'm aliasing the actor and casting tables as a1 and c1 respectively because now that we've found a list of movies, we'll have to turn and match that against the casting table again.

SELECT
  movie.*
FROM
  actor a1
  JOIN casting c1 ON (a1.id = c1.actorid)
  JOIN movie ON (c1.movieid = movie.id)
WHERE
  a1.name = 'Julie Andrews'

现在，我们有了她演过的所有电影的列表，我们需要将其与演员表(作为c2)和演员表(作为a2)结合起来，以获取这些电影的主角列表:

Now that we have a list of all movies she acted, we need to join that against the casting table (as c2) and that to the actor table (as a2) to get the list of leading roles for these films:

SELECT
  movie.title,  -- we'll keep the movie title from our last query
  a2.name       -- and select the actor's name (from a2, which is defined below)
FROM
  actor a1                                     -- \
  JOIN casting AS c1 ON (a1.id = c1.actorid)   --  )- no changes here
  JOIN movie ON (c1.movieid = movie.id)        -- /
  JOIN casting AS c2 ON (movie.id = c2.movieid)  -- join list of JA movies to the cast
  JOIN actor AS a2 ON (c2.actorid = a2.id)  -- join cast of JA movies to the actors
WHERE 
  a1.name = 'Julie Andrews'  -- no changes
  AND c2.ord = 1    -- only select the star of the JA film

在别名中，"AS"关键字是可选的.我在上面插入了它，以帮助查询更有意义

In aliasing, the 'AS' keyword is optional. I've inserted it above to help the query make more sense

这篇关于如何从三向联接表查询中列出第一个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！