子查询返回在MySQL

问题描述：

我执行下面的查询超过1行，并得到了错误“子查询返回多个1行”子查询返回在MySQL

我的查询是

SELECT pkTicketID, 
     TicketReplyDateAdded, 
     TIMESTAMPDIFF(DAY, TicketReplyDateAdded, now()) as NumberOfDays 
    FROM tickets as T 
    LEFT JOIN ticket_replies as TR ON T.fkTicketReplyID=TR.pkTicketReplyID 
    WHERE 1 AND T.fkEmployeeID = '4' 
    AND (SELECT TIMESTAMPDIFF(DAY, TicketReplyDateAdded, now()) as NumberOfDays 
      FROM tickets as T 
      LEFT JOIN ticket_replies as TR 
       ON T.fkTicketReplyID=TR.pkTicketReplyID 
     ) = 7 
    AND T.TicketStatus = 'Replied' 
    ORDER BY pkTicketReplyID DESC

感谢您的帮助。欧麦尔

答

您可以通过限制子查询只返回一行，类似这样的修正此错误：

SELECT pkTicketID,TicketReplyDateAdded, 
     TIMESTAMPDIFF(DAY, TicketReplyDateAdded,now()) as NumberOfDays 
FROM tickets as T 
     LEFT JOIN ticket_replies as TR 
     ON T.fkTicketReplyID=TR.pkTicketReplyID 
WHERE 1 
AND T.fkEmployeeID = '4' 
AND (SELECT TIMESTAMPDIFF(DAY, TicketReplyDateAdded, now()) as NumberOfDays 
     FROM tickets as T LEFT JOIN ticket_replies as TR 
     ON T.fkTicketReplyID=TR.pkTicketReplyID 
     LIMIT 1) = 7 
AND  T.TicketStatus = 'Replied' 
ORDER BY pkTicketReplyID DESC

（增加了“LIMIT 1”）

答

好了，这是因为你的子查询返回不止一行

zerkms救援

PS：这是真的的“相关”栏右侧类似的问题很多。

答

一种方法是增加LIMIT 1到您的子查询。另一个是确定你的子查询是否应该能够返回多于一行，如果不是，则修复它。

子查询返回在MySQL

相关推荐