mysql子查询返回多于1行,子查询在mysql中返回超过1行

I am executing the following query and got the error "Subquery returns more than 1 row"

My Query is

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

Thanks for your help.

Umar

解决方案

You can fix this error by limiting the subquery to return only one row, like this:

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

(Added 'LIMIT 1')