带有主查询数据变量的 MySQL 子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6062975/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Subquery with main query data variable
提问by adamweeks
Ok, need a MySQL guru here. I am trying to write a query that will serve as a notification system for when someone leaves a comment on an item that you have previously commented on. The 'drinkComment' table is very simple:
好的，这里需要一个 MySQL 专家。我正在尝试编写一个查询，当有人对您之前评论过的项目发表评论时，该查询将用作通知系统。'drinkComment' 表非常简单：
commentID, userID, drinkID, datetime, comment
I've written a query that will get all of the comments on drinks that I have previously commented on (that are not mine), but it will still show comments that occurred BEFORE my comment. This is as close to what I would think would work, but it does not. Please help!
我写了一个查询，它将获取我之前评论过的所有饮料评论（不是我的评论），但它仍会显示在我发表评论之前发生的评论。这与我认为可行的方法非常接近，但事实并非如此。请帮忙！
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
采纳答案by DRapp
Why not start with a prequery of the user and all the drinks they've offered comments and as of what time (don't know if you have multiple comments per person for any given drink or not). Then, find comments from all others AFTER such of your date/time comment...
为什么不从用户的预查询和他们提供评论的所有饮料以及截止时间开始（不知道您是否每个人对任何给定的饮料有多个评论）。然后，在您的日期/时间评论之后找到所有其他人的评论......
This query should actually be faster as it is STARTING with only ONE USER's drink comments as a basis, THEN goes back to the comments table for those matching the drink ID and cutoff time.
这个查询实际上应该更快，因为它只以一个用户的饮料评论为基础开始，然后返回到那些匹配饮料 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
回答by Mr47
I think you need to relate your innermost query to the middle query by drinkID.
我认为您需要通过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
