我有两个不同的表,其中一些记录具有相同的信息子组,但都具有不同的id值。下面是一个例子,我有一个actors
frommovies
和plays
的表。
我想查询这两个表,以便得到一对具有所有相同参与者的movie_id
,play_id
值(即对于具有相同first_name = given_name
的每个记录具有last_name = family_name
和id
)。
完成此任务的适当查询是什么?
TABLE: movie_actors
| movie_id | first_name | last_name |
|----------+------------+-----------|
| 1 | mary | johnson |
| 1 | john | smith |
| 2 | tom | anderson |
TABLE: play_actors
| play_id | given_name | family_name |
|----------+------------+-------------|
| 23 | mary | johnson |
| 23 | john | smith |
| 31 | marc | anthony |
DESIRED OUTPUT:
| movie_id | play_id |
|----------+---------|
| 1 | 23 |
最佳答案
在子查询中使用GROUP_CONCAT
将所有参与者作为一个列获取。然后基于此连接子查询。
SELECT movie_id, play_id
FROM (SELECT movie_id, GROUP_CONCAT(CONCAT(first_name, '-', last_name) ORDER BY first_name, last_name) AS actors
FROM movie_actors
GROUP BY movie_id) AS m
JOIN (SELECT play_id, GROUP_CONCAT(CONCAT(given_name, '-', family_name) ORDER BY given_name, family_name) AS actors
FROM play_actors
GROUP BY play_id) AS p
ON m.actors = p.actors
关于mysql - MySQL:在具有相同记录组的单独表中查找ID,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/31304495/