对查看最新条目的数据组进行排序 [英] Sorting groups of data looking at the newest entry

问题描述

我有一个包含两列 team 和 date 的表格.date 列包含条目添加到表中的日期.

I have a table with two columns team and date. The date column has the date that the entry was added to the table.

我想打印按 date DESC 排序的每个团队的最后 10 个条目.我还想按 date DESC 对这些团队条目进行排序.

I want to print the last 10 entries of each team sorted by date DESC. I also want to sort these groups of team entries by date DESC.

我尝试了很多东西，但都没有运气.它有效，但有 2 个查询，在这种情况下是不可接受的.

I tried a lot of things, but with no luck. It worked, but with 2 queries which is not acceptable in this case.

我如何通过一个查询来做到这一点?我有一种感觉，这是一个非常新手的问题.

How can I do this with a single query? I have the feeling that this is a really newbie question.

推荐答案

SELECT rows.team, rows.date FROM (
  SELECT team, date,
    IF( @prev <> team, @rownum := 1, @rownum := @rownum+1 ) AS rownum,
    @prev := team
  FROM my_table
  JOIN (SELECT @rownum := NULL, @prev := 0) AS init
  ORDER BY team, date DESC
) AS rows
WHERE rownum <= 10

我们在子查询中创建一个临时(虚拟)表，行按团队排序，日期 DESC 我们从顶部开始，为每一行提供递增的行号，每当团队更改时，我们重置行号，然后在外部查询我们过滤掉任何行号大于 10 的行.

We make a temporary (virtual) table in the sub-query with rows ordered by team, date DESC and we start from the top giving an incrementing row number to each row and whenever team changes we reset the row number, then in the outer query we filter out any row that has row number greater than 10.

这篇关于对查看最新条目的数据组进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！