这是我的表数据:
id page_id time created
=======================================================
1 1 00:15:00 2020-11-05 09:55:54
2 1 00:25:00 2020-11-10 07:35:24
3 2 00:10:25 2020-11-06 06:15:20
这是MySql查询:
SELECT
a.* SUM (a.time) AS time
FROM
`activity` AS a
GROUP BY a.page_id
ORDER BY a.created DESC;
想要的结果是让最新的记录出现在首位,但相反,我得到了首位记录;
类似:
2 1 00:25:00 2020-11-10 07:35:24
3 2 00:10:25 2020-11-06 06:15:20
相反,我得到的是:
1 1 00:15:00 2020-11-05 09:55:54
3 2 00:10:25 2020-11-06 06:15:20
您似乎想要每个id
的最新记录,如created
所定义。如果是这样,则需要进行筛选而不是聚合。
在MySQL 8.0中,我建议使用窗口函数:
select *
from (
select a.*,
rank() over(partition by page_id order by created desc) rn
from activity a
) a
where rn = 1
order by created desc
在早期版本中,您可以使用相关的子查询进行过滤:
select a.*
from activity a
where a.created = (
select max(a1.created) from activity a1 where a1.page_id = a.page_id
)