执行查询时出现错误 1241 (21000)：操作数应包含 1 列。

Question

INSERT INTO People(Track_id_Reference) 
SELECT track_id 
FROM Tracks 
WHERE track_title IN (SELECT tracktitle 
                      FROM top100 
                      WHERE artist IN (SELECT p.People_name, t.artist 
                                       FROM People AS p 
                                       RIGHT JOIN top100 AS t 
                                       ON 
                                       p.People_name=t.artist 
                                       UNION DISTINCT 
                                       SELECT p.People_name, t.artist 
                                       FROM People AS p 
                                       LEFT JOIN top100 AS t 
                                       ON 
                                       p.People_name=t.artist));

我收到的错误信息是： ERROR 1241 (21000): 操作数应该只包含1个列 子查询并集返回了2列。我该如何修复？

- Anvesh Raavi

---

显然，你可以通过只选择一列而不是两列来解决这个问题。 - DCoder

请您能否清楚地解释一下，我没有理解。 - Anvesh Raavi

Answer 1

您缺少FROM子句。

SELECT track_id 
FROM   tableName
WHERE track_title 

所以完整的查询将是：

INSERT INTO People (Track_id_Reference)
SELECT track_id
FROM                                      -- <<== add tableName here
WHERE track_title = (
                SELECT tracktitle
                FROM top100
                WHERE artist = (
                                SELECT p.People_name,
                                        t.artist
                                FROM People AS p
                                RIGHT JOIN top100 AS t
                                        ON p.People_name = t.artist

                                UNION

                                        DISTINCT
                                SELECT p.People_name,
                                        t.artist
                                FROM People AS p
                                LEFT JOIN top100 AS t
                                        ON p.People_name = t.artist
                                )
                );

另一个即将出现的问题是在子查询结果上使用等于符号“=”，比起使用“=”更安全的方法是使用“IN”，因为如果子查询返回多个值，它会抛出异常。例如：

INSERT INTO People (Track_id_Reference)
SELECT track_id
FROM                                      -- <<== add tableName here
WHERE track_title IN (
                SELECT tracktitle
                FROM top100 .............

- John Woo

---

@Xymostech：你不会因为慢而获得41k声望值。:) - DCoder

谢谢您的快速回复，我已经编辑并发布了一个新的错误信息，请问您能帮忙吗？ - Anvesh Raavi

在使用IN子句时，子查询的结果应该只有一列，例如WHERE artist IN (SELECT t.artist FROM People)。 - John Woo