列出子查询中提到的表中的列(MySQL)

Question

考虑三个表-演员、角色和电影

mysql> DESCRIBE actors;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   | PRI | 0       |       |
| first_name | varchar(100) | YES  | MUL | NULL    |       |
| last_name  | varchar(100) | YES  | MUL | NULL    |       |
| gender     | char(1)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.17 sec)

mysql> DESCRIBE roles;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| actor_id | int(11)      | NO   | PRI | NULL    |       |
| movie_id | int(11)      | NO   | PRI | NULL    |       |
| role     | varchar(100) | NO   | PRI | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESCRIBE movies;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | NO   | PRI | 0       |       |
| name      | varchar(100) | YES  | MUL | NULL    |       |
| year      | int(11)      | YES  |     | NULL    |       |
| rankscore | float        | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

下面的查询列出了电影《辛德勒的名单》中所有演员的first_name和last_name：

mysql> SELECT first_name, last_name FROM actors WHERE id IN
    -> (SELECT actor_id FROM roles WHERE movie_id IN
    -> (SELECT id FROM movies WHERE name='Schindler\'s List')
    -> ) ORDER BY first_name;

假设我想列出参与者所扮演的角色以及他们的名字和姓氏，那么获取所需结果集或输出的最佳查询是什么？可以注意到，该角色是roles表中的列，该表位于Sub查询中。

Answer 1

SELECT DISTINCT a.first_name, a.last_name, r.role 
FROM actors a, roles r, movies m 
WHERE a.id = r.actor_id  and r.movie_id  = m.id and m.name='Schindler\'s List'
ORDER BY first_name;

或者您可以使用by joins；

SELECT DISTINCT a.first_name, a.last_name, r.role 
FROM roles r 
  INNER JOIN  actors a ON a.id = r.actor_id 
  INNER JOIN movies m  ON r.movie_id  = m.id 
WHERE m.name='Schindler\'s List'
ORDER BY first_name;