#1242 - 子查询返回超过 1 行 - mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12597620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
#1242 - Subquery returns more than 1 row - mysql
提问by Apostrofix
I am trying to make a select statement that selects the image names from a MySQL database. The table is called - pictures_archive. I am also trying to select these pictures depending on the category they have. The code is:
我正在尝试创建一个选择语句，从 MySQL 数据库中选择图像名称。该表被称为-pictures_archive。我也在尝试根据它们的类别选择这些图片。代码是：
SELECT pictures_archive_filename FROM pictures_archive
WHERE pictures_archive_id = (SELECT pictures_archive_id
FROM pictures_archive_category WHERE pictures_category_id = 9)
It gives me an "#1242 - Subquery returns more than 1 row" error. I can see why, but can't figure it out how to do it.
它给了我一个“#1242 - 子查询返回超过 1 行”的错误。我明白为什么，但不知道如何去做。
回答by John Woo
Since your subquery can return multiple values, INshould fit in youy where clause.
由于您的子查询可以返回多个值，因此IN应该适合您的 where 子句。
SELECT pictures_archive_filename
FROM pictures_archive
WHERE pictures_archive_id IN
(
SELECT pictures_archive_id
FROM pictures_archive_category
WHERE pictures_category_id = 9
)
an alternative for this is to joinboth tables which is more efficient.
对此的另一种选择是join两个表都更有效。
SELECT pictures_archive_filename
FROM pictures_archive a
INNER JOIN pictures_archive_category b
ON a.pictures_archive_id = b.pictures_archive_id
WHERE b.pictures_category_id = 9
回答by Prasanna
Use IN instead of equal (=)
使用 IN 而不是相等 (=)
SELECT pictures_archive_filename FROM pictures_archive
WHERE pictures_archive_id IN (SELECT pictures_archive_id
FROM pictures_archive_category WHERE pictures_category_id = 9)
OR if possible use a JOIN between 2 tables
或者，如果可能，在 2 个表之间使用 JOIN
回答by PermGenError
SELECT pictures_archive_filename FROM pictures_archive
WHERE pictures_archive_id = (SELECT pictures_archive_id
FROM pictures_archive_category WHERE pictures_category_id = 9 LIMIT 1)
回答by AnandPhadke
SELECT p.pictures_archive_filename FROM
pictures_archive p inner join pictures_archive_category pc
on p.pictures_archive_id = pc.pictures_archive_id
where pc.pictures_category_id=9
