在mysql表中显示每个类别中的最后2个条目 [英] display last 2 entries in each category from a mysql table

问题描述

我有一个类似以下的表格

I have a table like following

===============================================
id          |      category    |    names     |
===============================================
1           |          A       |    name1     |
2           |          A       |    name2     |
3           |          A       |    name3     |
4           |          B       |    name4     |
5           |          B       |    name5     |
6           |          B       |    name6     |
7           |          B       |    name7     |
8           |          C       |    name8     |

预期输出:name8，name7，name6，name3，name2

Expected output: name8, name7,name6,name3,name2

我希望在每个类别中显示最后2个条目吗? 有人请帮帮我.谢谢你.

I wish to display last 2 entries in each category is that possible? Someone Please help me. Thanks in advace.

推荐答案

这些类型的结果最好由其他RDBMS中的窗口函数处理，但是不幸的是Mysql没有任何窗口函数，因此有一种替代方法可以使用用户定义的变量，以为属于同一组的行分配等级

These type of results are best handled by window functions in other RDBMS but unfortunately Mysql don't have any window functions so for alternative there is a solution to use user defined variables to assign a rank for rows that belong to same group

SELECT  `id`, `category`, `names`
FROM (
SELECT *,
@r:= CASE WHEN @g = category THEN @r + 1 ELSE 1 END rownum,
@g:=category
FROM test
  CROSS JOIN(SELECT @g:=NULL ,@r:=0) t
  ORDER BY category,id desc
) c
WHERE c.rownum <=2 

以上查询将为您提供每个类别2条最近记录(基于ID)，您可以将where子句的查询的最后部分更改为任意数字，以显示每组n个结果，例如显示3条记录，然后显示WHERE c.rownum <= 3和如此

Above query will give you 2 recent records (on basis of id) per category you can change the last part of query with where clause to any number to show n results per group for example to show 3 records then WHERE c.rownum <= 3 and so on

Demo

这篇关于在mysql表中显示每个类别中的最后2个条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！