从表中选择A列或B列中的值

Ewan Lyle 发表于 Dev

伊万·莱尔

我正在处理电影数据库，并且有一个链接表，因此可以将电影链接到它们的续集。我已经编写了以下查询，以便可以返回链接表的任何列中的movie_id所在的续集列表，但是我想知道是否有更好的方法可以这样做，因为这似乎是一个漫长的wind回之路。打算吗？

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);

普雷迪普

不幸的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 ))