带有主查询数据变量的 MySQL 子查询

Question

好的，这里需要一位 MySQL 专家。我正在尝试编写一个查询，当有人对您之前评论过的项目发表评论时，该查询将用作通知系统。 “drinkComment”表非常简单：

commentID, userID, drinkID, datetime, comment

我编写了一个查询，它将获取我之前评论过的饮料的所有评论（不是我的），但它仍然会显示在我发表评论之前发生的评论。这与我认为可行的最接近，但事实并非如此。请帮忙！

select @drinkID:=drinkComments.drinkID, commentID, drinkID, userID, comment, datetime 
FROM drinkComments  
WHERE `drinkID` IN 
    ( select distinct drinkID from drinkComments where drinkComments.userID = 1) 
AND drinkComments.dateTime > (
/*This gets the last date user commented on the main query's drinkID*/
select datetime FROM drinkComments WHERE drinkComments.userID = 1 AND drinkComments.drinkID = @drinkID ORDER BY datetime DESC LIMIT 1
)
ORDER BY datetime DESC

Answer 1

为什么不从用户的预查询以及他们提供评论的所有饮料以及截止时间开始（不知道您是否对任何给定的饮料每人有多个评论）。然后，在您的日期/时间评论之后查找所有其他人的评论...

此查询实际上应该更快，因为它仅以一个用户的饮料评论作为基础开始，然后返回评论表以查找与饮料匹配的评论ID和截止时间。

SELECT STRAIGHT_JOIN
      dc.*
   from 
       ( select
               drinkID,
               max( datetime ) UserID_DrinkCommentTime
            FROM 
               drinkComments 
            WHERE
               userID = 1
            group by
               drinkID ) PreQuery
       join DrinkComments dc
         on PreQuery.DrinkID = dc.DrinkID
         and dc.datetime > PreQuery.UserID_DrinkCommentTime
   order by
      dc.DateTime desc

Answer 2

我认为您需要通过 DrinkID 将最里面的查询与中间的查询相关联。

select @drinkID:=drinkComments.drinkID, commentID, drinkID, userID, comment, datetime 
FROM drinkComments  
WHERE `drinkID` IN 
    ( select distinct drinkID from drinkComments AS a where drinkComments.userID = 1) 
AND drinkComments.dateTime > (
/*This gets the last date user commented on the main query's drinkID*/
select datetime FROM drinkComments WHERE drinkComments.userID = 1 AND drinkComments.drinkID = a.drinkID ORDER BY datetime DESC LIMIT 1
)
ORDER BY datetime DESC