mysql - 从表格中选择 A 列或 B 列中的值

Question

我正在处理电影数据库，我有一个链接表，因此我可以将电影链接到它们的续集。我已经编写了以下查询，因此我可以返回续集列表，其中 movie_id 在链接表的任何列中，但是我想知道是否有更好的方法来执行此操作，因为它似乎是一个相当冗长的方法怎么办？

SELECT movie_id, movie_title FROM movies WHERE movie_id in 
    (SELECT movie_1 FROM lk_movies WHERE movie_1 = 1  or  movie_2 = 1 or  movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in 
    (SELECT movie_2 FROM lk_movies WHERE movie_1 = 1  or  movie_2 = 1 or  movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in 
    (SELECT movie_3 FROM lk_movies WHERE movie_1 = 1  or  movie_2 = 1 or  movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in 
    (SELECT movie_4 FROM lk_movies WHERE movie_1 = 1  or  movie_2 = 1 or  movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in 
    (SELECT movie_5 FROM lk_movies WHERE movie_1 = 1  or  movie_2 = 1 or  movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in 
    (SELECT movie_6 FROM lk_movies WHERE movie_1 = 1  or  movie_2 = 1 or  movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in 
    (SELECT movie_7 FROM lk_movies WHERE movie_1 = 1  or  movie_2 = 1 or  movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in 
    (SELECT movie_8 FROM lk_movies WHERE movie_1 = 1  or  movie_2 = 1 or  movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in 
    (SELECT movie_9 FROM lk_movies WHERE movie_1 = 1  or  movie_2 = 1 or  movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in 
    (SELECT movie_10 FROM lk_movies WHERE movie_1 = 1  or  movie_2 = 1 or  movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1);

Answer 1

不幸的是 Mysql 不支持 Unpivot 或 Cross Apply 运算符所以这是我想出的最好的

子查询

SELECT movie_1 
FROM   lk_movies 
WHERE  movie_1 = 1 
        OR movie_2 = 1 
        OR movie_3 = 1 
        OR movie_4 = 1 
        OR movie_5 = 1 
        OR movie_6 = 1 
        OR movie_7 = 1 
        OR movie_8 = 1 
        OR movie_9 = 1 
        OR movie_10 = 1 

可以通过反转IN操作符来重写

SELECT movie_10 
FROM   lk_movies 
WHERE  1 IN( movie_1, movie_2, movie_3, movie_4, 
             movie_5, movie_6, movie_7, movie_8, 
             movie_9, movie_10 ) 

然后使用Union all代替多个OR条件这里是完整的查询

 SELECT movie_id, 
       movie_title 
FROM   movies 
WHERE  movie_id IN (SELECT movie_1 
                    FROM   lk_movies 
                    WHERE  1 IN( movie_1, movie_2, movie_3, movie_4, 
                                 movie_5, movie_6, movie_7, movie_8, 
                                 movie_9, movie_10 ) 
                    UNION ALL 
                    SELECT movie_2 
                    FROM   lk_movies 
                    WHERE  1 IN( movie_1, movie_2, movie_3, movie_4, 
                                 movie_5, movie_6, movie_7, movie_8, 
                                 movie_9, movie_10 ) 
                    UNION ALL 
                    ...............
                    ...............
                    SELECT movie_10 
                    FROM   lk_movies 
                    WHERE  1 IN( movie_1, movie_2, movie_3, movie_4, 
                                 movie_5, movie_6, movie_7, movie_8, 
                                 movie_9, movie_10 ))