MySQL-连接表,仅检索最大ID [英] MySQL - Join tables, retrieve only Max ID
问题描述
我在其他帖子上也看到了类似问题的解决方案,但是在将其应用于我的特定问题时遇到了问题.
I've seen solutions for something similar on other posts, but I've been having an issue applying it to my specific problem.
这是我的初次加入:
SELECT service_note_task, comment_id, comment FROM service_note_task LEFT JOIN service_note_task_comments ON service_note_task.service_note_task_id = service_note_task_comments.service_note_task_id;
这将导致:
+-----------------------------+------------+--------------+
| service_note_task | comment_id | comment |
+-----------------------------+------------+--------------+
| This is service note task 3 | 25 | Comment |
| This is service note task 3 | 26 | Comment Blah |
| This is service note task 3 | 36 | aaa |
| This is service note task 2 | 13 | Awesome comm |
| This is service note task 1 | 12 | Cool Comm |
+-----------------------------+------------+--------------+
但是对于每个service_note_task,我实际上只需要一行代表具有最高comment_id的注释,就像这样:
But for each service_note_task, I really only need one row representing the comment with the highest comment_id, like this:
+-----------------------------+------------+--------------+
| service_note_task | comment_id | comment |
+-----------------------------+------------+--------------+
| This is service note task 3 | 36 | aaa |
| This is service note task 2 | 13 | Awesome comm |
| This is service note task 1 | 12 | Cool Comm |
+-----------------------------+------------+--------------+
我认为我可以在子选择语句中使用MAX来缩小结果的范围.如何将其合并到语句中以获得这些结果?
I figure I could use MAX in a sub-select statement to narrow down the results as I want them. How can I incorporate that into my statement to get these results?
推荐答案
作为参考,这称为"groupwise-maximum"
For reference, this is known as "groupwise-maximum"
http://dev. mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
这篇关于MySQL-连接表,仅检索最大ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!