首页 文章

SQL错误：＃1242 - 子查询返回超过1行

提问于
浏览 次
2

我收到“错误：＃1242 - 子查询返回超过1行”试图执行此查询：

SELECT id FROM postcodes WHERE pcd LIKE (SELECT CONCAT(pcd,' %') FROM towns WHERE id IN (31898,12828,15771,7604))

你对这个查询有什么建议吗？

php mysql sql

4 回答

  • 1

    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);
    回复于
  • 0

    检查此查询：

    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)
    回复于
  • -2

    您必须将子查询限制为一行 . 您应该添加一些过滤器以将结果限制为一行，或者将 LIMIT 1 添加到您的子查询中将为您提供帮助 .

    回复于
  • 3

    我得到了＃1242 - Subquery返回超过1行

    SELECT
  LnDetails.EID,
  LnDetails.LnAmt,
  LnDetails.Tenure,
  LnDetails.UsedTenure,
  LnDetails.RePayAmt,
  LnDetails.Paid,
  LnDetails.OutSPay,
  LnDetails.IntRate,
  LnDetails.LnDesc,
  LnDetails.PNarration,
  LnDetails.`Status`,
  LnDetails.FStatus,
  LnDetails.AddedBy,
  CAST(LnDetails.AddedDate AS CHAR) AS AddedDate,
  LnDetails.UpdatedBy,
  LnDetails.UpdatedDate,
  LnDetails.DeletedBy,
  LnDetails.DeletedDate,
  LnDetails.AuthBy,
  CAST(LnDetails.AuthDate AS CHAR) AS AuthDate,
  LnDetails.PaidBy,
  LnDetails.PaidDate,
  LnDetails.HashKey,
  LnDetails.RepayType,
  (
  SELECT
    SName
  FROM
    LnCore
  WHERE
    (HashKey = LnDetails.LoanScheme)
) AS LoanScheme,
CAST(LnDetails.SRepayDate AS CHAR) AS SRepayDate,
CAST(LnDetails.ERepayDate AS CHAR) AS ERepayDate,
CONCAT(
  EmpTbl_1.SName,
  ' ',
  EmpTbl_1.FName,
  ' [',
  EmpTbl_1.EmpID,
  ']'
) AS FullName,
(
SELECT CONCAT
  (SName,
  ' ',
  FName) AS Expr1
FROM
  EmpTbl
WHERE
  (HashKey = LnDetails.AuthBy)
) AS AuthedBy,
EmpTbl_1.EmpType,
EmpTbl_1.Mobile,
EmpTbl_1.Department
FROM
  EmpTbl AS EmpTbl_1
INNER JOIN
  LnDetails ON EmpTbl_1.HashKey = LnDetails.EID

    从这个查询 . 请任何建议 . 谢谢 .

    回复于

相关问题