我想对数据库表执行搜索查询。这是我的表格示例。
movies
--------------------------
ID (INT) | TITLE (VARCHAR)
--------------------------
actors
-------------------------
ID (INT) | NAME (VARCHAR)
-------------------------
directors
-------------------------
ID (INT) | NAME (VARCHAR)
-------------------------
ma
------------------------------------
ID (INT) | MOVIE (INT) | ACTOR (INT)
------------------------------------
md
---------------------------------------
ID (INT) | MOVIE (INT) | DIRECTOR (INT)
---------------------------------------
我想根据某些关键字搜索
movies。title，actors。name，directors。name。 ma表具有引用movie。movie的字段id和引用actor。actor的字段id，依此类推。我想按movie。id显示结果组。问题是我不知道如何基于表directors和actors以及基于对表ma进行引用的表md和movies来执行。抱歉，如果您认为这个问题值得，可以对其进行编辑。那么，有什么主意吗？
最佳答案
这个你什么都有
SELECT m.title, a.name AS actor, d.name AS director
FROM movies m
INNER JOIN ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.id = ma.actor_id)
INNER JOIN md ON (md.movie_id = m.id)
INNER JOIN directors d ON (d.id = md.director_id)
ORDER BY m.title
您通过电影分组的那个
SELECT m.title, GROUP_CONCAT(a.name) AS actorS, GROUP_CONCAT(d.name) AS directors
FROM movies m
INNER JOIN ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.id = ma.actor_id)
INNER JOIN md ON (md.movie_id = m.id)
INNER JOIN directors d ON (d.id = md.director_id)
GROUP BY m.id
ORDER BY m.title
[编辑]
看在上帝的份上..只需添加位置
...
m.title喜欢'KEYWORD％'或a.name喜欢'KEYWORD％'或d.name喜欢'KEYWORD％'
关于mysql - 基于MySQL选择多个表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16727717/