如何访问子查询中的列？

Question

我对下面的查询有问题。对于返回总结果和的子查询，我在“where子句”(where子句)中得到错误的未知列'search.term‘，我不知道如何才能正确地执行这个查询？任何帮助都是非常感谢的。

SELECT *,
  (SELECT SUM(totalResults)
   FROM (SELECT COUNT(*) as totalResults
         FROM media
         LEFT JOIN users ON users.id = media.userId
         WHERE media.title LIKE CONCAT('%', search.term, '%')
       OR media.description LIKE CONCAT('%', search.term, '%')
       OR media.tags LIKE CONCAT('%', search.term, '%')
       OR users.username LIKE search.term
         UNION ALL
         SELECT COUNT(*)
         FROM reposts
         LEFT JOIN media ON media.id = reposts.mediaId
         LEFT JOIN users ON users.id = reposts.userId
         WHERE media.title LIKE CONCAT('%', search.term, '%')
       OR media.description LIKE CONCAT('%', search.term, '%')
       OR media.tags LIKE CONCAT('%', search.term, '%')
       OR users.username LIKE search.term
         )
  t) AS totalResults,
  MAX(search.timestamp) AS searchTimestamp
  FROM search
  WHERE userId = '1'
  GROUP BY search.term
  ORDER BY searchTimestamp DESC

Answer 1

原因是双重嵌套的SELECT子查询。试试这个：

 SELECT  term,
         ( SELECT COUNT(*)
           FROM media
           LEFT JOIN users ON users.id = media.userId
           WHERE media.title LIKE CONCAT('%', search.term, '%') 
              OR media.description LIKE CONCAT('%', search.term, '%') 
              OR media.tags LIKE CONCAT('%', search.term, '%') 
              OR users.username LIKE search.term
         )
         +
         ( SELECT COUNT(*)
           FROM reposts
           LEFT JOIN media ON media.id = reposts.mediaId
           LEFT JOIN users ON users.id = reposts.userId
           WHERE media.title LIKE CONCAT('%', search.term, '%') 
              OR media.description LIKE CONCAT('%', search.term, '%') 
              OR media.tags LIKE CONCAT('%', search.term, '%') 
              OR users.username LIKE search.term
         ) AS totalResults,
         MAX(timestamp) AS searchTimestamp
FROM search
WHERE userId = '1'
GROUP BY term
ORDER BY searchTimestamp DESC ;

Sidenote：在拥有GROUP BY <columns>的同时使用SELECT *将在除<columns>之外的所有其他列中给出不确定的结果。MySQL允许这种不明确的行为，但这不是一个好的做法。