MySQL-如何按值使用子查询进入IN语句 [英] MySQL - How to use subquery into IN statement by value

问题描述

问题是获取表列数据并将其用作IN函数的值列表；

The question is to get table column data and use it as a value list for IN function;

在此示例中，我创建了2个表格:电影和流派

For this example I created 2 tables: movies and genres

表电影"包含3列:id，名称和类型. 表类型"包含2列:ID和名称.

Table "movies" contains 3 columns: id, name and genre. Table "genres" contains 2 columns: id and name.

+- movies-+
|         |- movie_id - int(11) - AUTO_INCREMENT - PRIMARY
|         |- movie_name - varchar(255)
|         |- movie_genres - varchar(255)
|
|         
+- genres-+
          |- genre_id - int(11) - AUTO_INCREMENT - PRIMARY
          |- genre_name - varchar(255)

两个表都包含一些伪数据:

Both tables contain some dummy data:

+----------+------------+--------------+
| movie_id | movie_name | movie_genres |
+----------+------------+--------------+
|        1 | MOVIE 1    | 2,3,1        |
|        2 | MOVIE 2    | 2,4          |
|        3 | MOVIE 3    | 1,3          |
|        4 | MOVIE 4    | 3,4          |
+----------+------------+--------------+

+----------+------------+
| genre_id | genre_name |
+----------+------------+
|        1 | Comedy     |
|        2 | Fantasy    |
|        3 | Action     |
|        4 | Mystery    |
+----------+------------+

我的目标是获得这样的结果:

My goal is to get result like this:

+----------+------------+--------------+-----------------------+
| movie_id | movie_name | movie_genres |   movie_genre_names   |
+----------+------------+--------------+-----------------------+
|        1 | MOVIE 1    | 2,3,1        | Fantasy,Action,Comedy |
|        2 | MOVIE 2    | 2,4          | Fantasy,Mystery       |
|        3 | MOVIE 3    | 1,3          | Comedy,Action         |
|        4 | MOVIE 4    | 3,4          | Action,Mystery        |
+----------+------------+--------------+-----------------------+

我正在使用此查询，部分起作用的唯一问题是它使用IN值列表中movie_genres字段的第一个值.

I'm using this query and it's partly working only problem is that it uses the first value of the movie_genres field in the IN value list.

SELECT `m` . * , GROUP_CONCAT( `g`.`genre_name` ) AS `movie_genre_names`
FROM `genres` AS `g`
LEFT JOIN `movies` AS `m` ON ( `g`.`genre_id`
IN (
`m`.`movie_genres`
) )
WHERE `g`.`genre_id`
IN (
(
SELECT `movie_genres`
FROM `movies`
WHERE `movie_id` =1
)
)
GROUP BY 1 =1

结果与我想要的结果大不相同:

The results greatly differ from the one I want:

+----------+------------+--------------+-------------------+
| movie_id | movie_name | movie_genres | movie_genre_names |
+----------+------------+--------------+-------------------+
|        1 | MOVIE 1    | 2,3,1        | Fantasy,Fantasy   |
+----------+------------+--------------+-------------------+

对不起，如果我错过了一些数据，我是mysql的新手.

Sorry if I missed some data I'm new to mysql.

我应该使用哪个查询来获得所需的结果?

What query should I use to get the wanted results?

推荐答案

这是一个错误的设计.您应该创建一个many-to-many链接表(movie_id, genre_id)

This is a bad design. You should create a many-to-many link table (movie_id, genre_id)

但是，如果您不能更改此设计，请使用以下查询:

If you cannot change this design, however, use this query:

SELECT  movie.*
        (
        SELECT  GROUP_CONCAT(genre_name)
        FROM    genres
        WHERE   find_in_set(genre_id, movie_genres)
        ) as movie_genre_names
FROM    movies

这篇关于MySQL-如何按值使用子查询进入IN语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！