需求:按照类别category_id分组,并对每个分类按时间date排序
注意:group by 分组后只取每组的第一条数据,这不符合要求,分组后,要求每组的数据不减少
- 数据库:
- sql:
SELECT
a.*, count(1) AS rank
FROM
test a
LEFT JOIN test b ON a.category_id = b.category_id
AND a.date <= b.date
GROUP BY
a.category_id,
a.date
ORDER BY
a.category_id,
a.date DESC
- 结果: