mysql - 检索其元记录包含作者 ID 具有最新日期的所有记录

Question

我有两张 table 。第一个称为subject，另一个称为comment。很明显，主题表与评论具有一对多关系。下面是这些表的架构:主题:

__________________________
| id | subject_id | title |
--------------------------
| 1  | 1          | health|
--------------------------
| 2  | 2          | sport |
---------------------------
| 3  | 5          | food  |
---------------------------

评论:

----------------------------------------
| id | user_id | subject_id | date      |
----------------------------------------
| 1  | 10      | 1          | 2018-07-04|
-----------------------------------------
| 2  | 9       | 1          | 2018-07-03|
-----------------------------------------
| 3  | 10      | 1          | 2018-07-02|
----------------------------------------

我想检索所有 user_id=10 且具有最大日期的 subject_id 表单主题表。我这种情况结果一定是这样的:只有一条记录，subject_id =1；

$query = "SELECT subject_id FROM subject AS s, comment AS c WHERE 
           s.subject_id = c.subject_id AND c.user_id = 10
           GROUP BY MAX(c.date)

但不幸的是，它根据与特定主题相关的评论数量返回 subject_id:

首先我可以选择最大日期的所有评论

从评论组中选择 * BY subject_id HAVING max(date) 未解答的问题是我如何过滤这些记录以检索 user_id = 10 的最新评论

Answer 1

如果你想使用聚合，我认为子查询将通过以下方式帮助你

select subject_id from
(
select subject_id,max(date) from subject
inner join comment on subject.subject_id=comment.subject_id
where comment.user_id=10
group by subject_id
) as T