我得到"错误:#1242-子查询返回1行以上"尝试执行此查询:
SELECT id FROM postcodes WHERE pcd LIKE (SELECT CONCAT(pcd,' %') FROM towns WHERE id IN (31898,12828,15771,7604))
你对这个问题有什么建议吗?
JOIN
这两个表而不是IN
谓词,如下所示:
SELECT p.id
FROM postcodes p
INNER JOIN towns t ON p.pcd LIKE CONCAT(t.pcd,' %')
WHERE t.id IN (31898,12828,15771,7604);
检查此查询:
SELECT CONCAT(pcd,' %') FROM towns WHERE id IN (31898,12828,15771,7604)
也许它的结果不止一行。
或者可以限制子查询结果。
SELECT id FROM postcodes WHERE pcd LIKE (SELECT CONCAT(pcd,' %') FROM towns WHERE id IN (31898,12828,15771,7604) LIMIT 1)
您必须将子查询限制为一行。要么您应该添加一些过滤器以将结果限制在一行,要么将LIMIT 1
添加到子查询中即可。