MySQL 左连接最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9144677/
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
LEFT JOIN on Max Value
提问by akanevsky
Suppose I have the following two tables:
假设我有以下两个表：
STUDENT
studentid lastname firstname
1 Smith John
2 Drew Nancy
STUDENT_STORY
studentid dateline storyid status
1 1328313600 10 2
1 1328313601 9 1
2 1328313602 14 2
2 1328313603 12 1
Now, I need an SQL query that would select each student along with the latest story for that student in the student story table.
现在，我需要一个 SQL 查询来选择每个学生以及学生故事表中该学生的最新故事。
I am trying this:
我正在尝试这个：
SELECT s.*, ss.*
FROM student AS s
LEFT JOIN (
SELECT *
FROM student_story
WHERE student_story.studentid = s.studentid
ORDER BY dateline DESC LIMIT 1
) AS ss ON (ss.studentid = s.studentid)
However, this query does not work. It complains about s.studentid being an unknown field in the where clause of the sub query.
但是，此查询不起作用。它抱怨 s.studentid 是子查询的 where 子句中的未知字段。
Please suggest how I can achieve what I'm trying to do.
请建议我如何实现我正在尝试做的事情。
Thanks.
谢谢。
回答by Detheroc
Try something like this:
尝试这样的事情：
SELECT
s.*,
ss.*
FROM
student AS s
LEFT JOIN
student_story AS ss
ON (ss.studentid = s.studentid)
WHERE ss.dateline = (
SELECT
MAX(dateline)
FROM
student_story AS ss2
WHERE
ss2.studentid = s.studentid
)
回答by Yahoo - Jungly
SELECT s.*, ss.*
FROM student AS s
LEFT JOIN (
SELECT * FROM student_story
ORDER BY dateline DESC LIMIT 1
)
AS ss ON (ss.studentid = s.studentid)
回答by Vyktor
This join should do it:
这个连接应该这样做：
SELECT s.*, ss.*
FROM student_story AS ss
LEFT JOIN student AS s
ON student_story.studentid = s.userid
GROUP BY ss.studentid
GROUP BYshould take last row, so last story = last row, if it won't work try to:
GROUP BY应该取最后一行，所以最后一个故事=最后一行，如果它不起作用，请尝试：
GROUP BY ss.studentid, ss.dateline DESC
I'm not sure about it, please comment with result
我不确定，请评论结果
回答by Namal
SELECT
s.sale_id,
s.created_at,
p.created_at,
DATEDIFF(p.created_at, s.created_at) AS days
FROM
pos_sales s
LEFT JOIN
pos_payments p ON p.sale_id = s.sale_id
AND
p.created_at = (SELECT
MAX(p2.created_at)
FROM
pos_payments p2
WHERE
p2.sale_id = p.sale_id)
回答by E. Villiger
Another approach is making a LEFT JOINwith a NOT EXISTS()condition. Depending on the scenario, this can give a bit more flexibility; however, it will also show duplicate results if there are two entries with the same datelineper student:
另一种方法是LEFT JOIN使用NOT EXISTS()条件创建一个。根据场景，这可以提供更多的灵活性；但是，如果dateline每个学生有两个相同的条目，它也会显示重复的结果：
SELECT s.*, ss.*
FROM student AS s
LEFT JOIN student_story AS ss ON ss.studentid = s.studentid AND NOT EXISTS
(SELECT * FROM student_story AS ss2
WHERE ss2.studentid = ss.studentid AND ss2.dateline > ss.dateline)
回答by daffy
You'll need to add an auto and unique id on each row on student_story table.
您需要在 student_story 表的每一行上添加一个自动和唯一的 id。
then you will distinguish between them. ( assume studentstory.new_id )
然后你会区分它们。（假设 studentstory.new_id ）
select s.*, ss.*
from student s
left join student_story ss on ss.studentid = s.userid
where ss.new_id = ( select ss.new_id from student s
group by dateline
order by dateline desc
limit 1
)
