即使有任何 MySQL 错误 1242

Question

我开始使用 OpenGeoDB 查找邮政编码和城市名称。我的 MySQL 查询如下所示:

SELECT      `d`.`text_val` AS `city`, (
    SELECT  `text_val`
    FROM    `geodb_textdata`
    WHERE   `loc_id` = ANY (
        SELECT  `loc_id`
        FROM    `geodb_textdata`
        WHERE   `text_val` LIKE '08%'
    )
        AND `text_type` = '500300000'
) AS `plz`, `c`.`lat`, `c`.`lon`
FROM        `geodb_textdata`    AS  `d`
LEFT JOIN   `geodb_coordinates` AS  `c` USING   (`loc_id`)
WHERE       `loc_id` = ANY (
    SELECT  `loc_id`
    FROM    `geodb_textdata`
    WHERE   `text_val` LIKE '08%'
)
    AND `text_type` = '500100000'

如果我使用完整的邮政编码或城市名称运行此查询，效果很好，但是使用上面的代码，我得到错误代码

#1242 - Subquery returns more than 1 row

我在使用 Google 时发现，我应该在 = 和子查询之间使用 ANY。您知道如何修复我的代码吗？先感谢您。 :)

Answer 1

我怀疑这个子查询返回超过 1 行:

(
SELECT  `text_val`
FROM    `geodb_textdata`
WHERE   `loc_id` = ANY (
    SELECT  `loc_id`
    FROM    `geodb_textdata`
    WHERE   `text_val` LIKE '08%'
)
    AND `text_type` = '500300000'
) AS `plz`

更新: 未经测试，但请尝试此查询。

SELECT      `d`.`text_val` AS `city`, `d2`.`text_val` AS `plz`, `c`.`lat`, `c`.`lon`
FROM        `geodb_textdata`    AS  `d`
LEFT JOIN  `geodb_textdata`    AS  `d2` USING (`loc_id`)
LEFT JOIN   `geodb_coordinates` AS  `c` USING   (`loc_id`)
WHERE       `d`.`text_val` LIKE '08%' AND `d`.`text_type` = '500100000'
AND         `d2`.`text_val` LIKE '08%' AND `d2`.`text_type` = '500300000'