MySQL 从每个不同的Candidate_id 的最近日期的行中返回数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8611646/
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
Return the data from the rows with the most recent date of each distinct candidate_id
提问by arrogantprick
I am attempting to return the data from the rows with the most recent date of each distinct candidate_id. It is correctly returning the most recent date (the created_unix column), but not the rest of the data from the corresponding row.
我试图从具有每个不同候选 ID 的最新日期的行中返回数据。它正确返回了最近的日期（created_unix 列），但没有返回相应行中的其余数据。
SELECT candidate_id, message, max(created_unix), jobpost_id, staffuserid
FROM messages
WHERE employer_id='$employerid' AND last='company'
GROUP BY candidate_id
采纳答案by Devin Burke
You must group byeverything not using an aggregate function:
您必须group by不使用聚合函数的所有内容：
SELECT candidate_id, message, max(created_unix), jobpost_id, staffuserid
FROM messages
WHERE employer_id='$employerid' AND last='company'
GROUP BY candidate_id, message, jobpost_id, staffuserid
If your messageis different per row and you want to group by candidate_id, then you must not be using message. In that case, simply remove it from your select list and you won't need it in your group bylist. The same goes for any other field you aren't using.
如果您message的每行都不同并且您想这样做group by candidate_id，那么您一定不要使用message. 在这种情况下，只需将它从您的选择列表中删除，您的列表中就不需要它了group by。您未使用的任何其他字段也是如此。
Remember, when using aggregate functions, you must contain each field in either an aggregate function or the group by. Otherwise, SQL won't know from which row to pull the data for the row returned.
请记住，在使用聚合函数时，您必须将每个字段包含在聚合函数或group by. 否则，SQL 将不知道从哪一行提取返回行的数据。
Update:
更新：
After seeing what you're looking for, this will do the trick:
看到您要查找的内容后，这将起作用：
SELECT candidate_id, message, max(created_unix), jobpost_id, staffuserid
FROM messages
WHERE employer_id='$employerid' AND last='company' AND
created_unix = (
SELECT max(subm.created_unix)
FROM messages subm
WHERE subm.candidate_id = messages.candidate_id
)
回答by mathematical.coffee
SELECT m1.*
FROM messages as m1
LEFT OUTER JOIN messages AS m2
ON (m1.candidate_id = m2.candidate_id
AND (m1.created_unix < m2.created_unix)
WHERE m2.created_unix is NULL
AND employer_id='$employerid' AND last='company'
This joins messagesto itself on candidate_id and makes rows with picks all dates in m2that are greater than each date in m1, substituting NULLif none are greater. So you get NULLprecisely when there is no greater date within that candidate_id.
这将messages在Candidate_id 上与自身连接，并使行中的所有日期m2都大于 中的每个日期m1，NULL如果没有更大的则替换。因此，NULL当其中没有更大的日期时，您会得到准确的信息candidate_id。
