我发现了类似的问题,但最终完全不同,没有人回答我的问题。
我使用mySQL
我有一张表movie_artist_role喜欢3张表的电影,艺术家和角色包含3个键
mo_id
ar_id
ro_id.
表格影片包含
mo_id
mo_title
桌面艺术家包含
ar_id
ar_name
表角色包含
ro_id
ro_name (actor or producer for instance)
我想显示电影列表,以及每部电影的演员和导演
最有效的方法是什么?在一个具有多个左联接的唯一查询中是否可能?
感谢
select movie.mo_title, artist.ar_name, role.ro_name
from movie_artist_role
left join movie on movie.mo_id = movie_artist_role.mo_id
left join artist on artist.ar_id = movie_artist_role.ar_id
left join role on role.ro_id = movie_artist_role.ro_id
order by movie.mo_tile, artist.ar_name
我有一个类似的电影数据库!我认为你定义的"角色"表是没有必要的——我有一个单独的"导演"表,它直接链接到"电影"表:同一部电影很少有两个导演,当有(例如科恩兄弟)时,他们往往经常合作,所以他们可以被视为一个导演。
使用左联接而不是内联接可以显示没有导演或演员链接的电影。
连接的foreign keys
之间的多个inner join
(将从所有3个表中提取所有内容)将显示每个电影、演员和导演的信息列表。
SELECT * FROM movie_artist_role mar
INNER JOIN movie m ON mar.mo_id = m.mo_id
INNER JOIN artists a ON mar.ar_id = a.ar_id
INNER JOIN role r ON mar.ro_id = r.ro_id
ORDER BY m.mo_title, r.role_name
使用WHERE
子句可以为您提供有关该电影或演员的详细信息。例如
SELECT * FROM movie_artist_role mar
INNER JOIN movie m ON mar.mo_id = m.mo_id
INNER JOIN artists a ON mar.ar_id = a.ar_id
INNER JOIN role r ON mar.ro_id = r.ro_id
WHERE a.ar_name = 'Jack Black' ORDER BY m.mo_title, r.role_name
如果你只想从特定的表中得到特定的数据,比如film
,那么。。。这与上面所做的差不多,但恰恰相反。
SELECT * FROM movie m
INNER JOIN movie_artist_role mar ON mar.mo_id = m.mo_id
INNER JOIN artists a ON mar.ar_id = a.ar_id
INNER JOIN role r ON mar.ro_id = r.ro_id
ORDER BY m.mo_title, r.role_name
或artists
。。。
SELECT * FROM artists a
INNER JOIN movie_artist_role mar ON mar.ar_id = a.ar_id
INNER JOIN movie m ON mar.mo_id = m.mo_id
INNER JOIN role r ON mar.ro_id = r.ro_id
ORDER BY m.mo_title, r.role_name
等等
LEFT JOIN应该可以正常工作。
SELECT m.mo_title AS movie_title,
(SELECT group_concat(aIn.ar_name)
FROM movie_artist_role AS marIn
LEFT JOIN role AS rIn ON rIn.ro_id = marIn.ro_id
LEFT JOIN artist AS aIn ON aIn.ar_id = marIn.ao_id
WHERE rIn.ro_name = 'producer'
AND marIn.mo_id = m.id ) AS producers,
(SELECT group_concat(aIn.ar_name)
FROM movie_artist_role AS marIn
LEFT JOIN role AS rIn ON rIn.ro_id = marIn.ro_id
LEFT JOIN artist AS aIn ON aIn.ar_id = marIn.ao_id
WHERE rIn.ro_name = 'actor'
AND marIn.mo_id = m.id ) AS actors
FROM movie AS m
这是未经测试的,但应该返回以下行:
movie_title | producers | actors
-----------------------------------------------------------
Star Treck JJ Abrams Chris Pine, Simon Pegg, Zachery