SQLite3查询两人共同出演电影时报ambiguous column name错误求解

报错原因 #

你当前的查询逻辑中，两个子查询xx、yy都包含movie_id字段，两个子查询内连接后，结果集里会存在两个同名的movie_id列。最外层的select movie_id没有明确指定该字段来源于哪个子查询别名，因此SQLite无法判断该取哪一列的值，触发列名歧义报错。

修复方法 #

你只需要在最外层查询的movie_id前加任意一个子查询的别名前缀即可，因为你内连接的条件就是xx.movie_id = yy.movie_id，两个字段的值完全一致，任选其一就行，比如选xx.movie_id：

原逻辑修复后的完整代码 #

select xx.movie_id from (
        (select movie_id,person_id from (
            select id from people where name = "Johnny Depp") as x 
                inner join 
                    stars on x.id = stars.person_id) as xx 
                                
        inner join 
                                    
        (select movie_id,person_id from (
            select id from people where name = "Helena Bonham Carter") as y 
                inner join 
                    stars on y.id = stars.person_id) as yy 
                                    
        on xx.movie_id = yy.movie_id
                    );

更简洁的优化写法 #

你原来的SQL嵌套层级过多，可以简化为如下写法，可读性和执行效率都更高：

-- 如果需要返回电影名，可额外关联movies表
SELECT DISTINCT s1.movie_id, m.title
FROM stars s1
INNER JOIN stars s2 
    ON s1.movie_id = s2.movie_id
INNER JOIN people p1 
    ON s1.person_id = p1.id AND p1.name = 'Johnny Depp'
INNER JOIN people p2 
    ON s2.person_id = p2.id AND p2.name = 'Helena Bonham Carter'
INNER JOIN movies m 
    ON s1.movie_id = m.id;

内容的提问来源于stack exchange，提问作者Darshil