从表中选择2条最新的“组/批次"记录 [英] Select the 2 latest “group/batch” records from table

问题描述

我在mysql表中有类似的数据:

I have data like in this mysql table:

id  customer_id  int_proc   inventory
1   A           1           1   
2   A           4           1
3   A           5           1
4   A           0           2
5   A           5           2
6   A           6           2
7   B           6           1       
8   B           7           1
9   B           9           1
10  B           9           2
11  B           9           2
12  C           22          1

我想从每个库存的最新2个int_proc值中获取所有数据，并且customer_id为A和B. 我的结果应该是这样的:

I want to get all data from the latest 2 int_proc values for every inventory and where the customer_id is A and B. My result should be like this:

id  customer_id  int_proc   inventory
2   A           4           1
3   A           5           1
5   A           5           2
6   A           6           2
8   B           7           1
9   B           9           1
10  B           9           2
11  B           9           2

非常感谢您的帮助.

推荐答案

您可以使用Mysql的用户定义变量，并对同一客户组中每个客户和每个库存的行进行排名，以下查询将给出2个最新的如果要获取最新的n条记录，则按库存和相同的客户组，只需将where子句更改为where t2.r <= n

You can use Mysql's user defined variables and give a rank to rows per customer and per inventory with in a same customer group,below query will give 2 latest int_proc per inventory and same customer group if you want to get latest n number of records just change where clause to where t2.r <= n

select 
t2.id,
t2.customer_id,
t2.int_proc, 
t2.inventory
from (
select t.*,
@r:= case when @g = t.customer_id
     then
          case when @sg = t.inventory 
          then  @r+1 
          else 1 end 
      else 1 end r,
@g:= t.customer_id g,
@sg:= t.inventory sg
from test t
cross join (select @g:=null,@sg:=null,@r:=null) t1
  where t.customer_id in('A','B')
order by t.customer_id,t.inventory,t.int_proc desc
  ) t2
where t2.r <= 2
order by id

Fiddle Demo

编辑重复值

如果int_proc有重复的行，则必须添加另一个子案例语句以检查重复的值并对其进行相应排名

If you have duplicated rows for the int_proc you have to add another sub case statement to check for repeated values and rank them accordingly

select 
t2.id,
t2.customer_id,
t2.inventory,
t2.int_proc
from (
select t.*,
@r:= case when @g = t.customer_id
     then
          case when @sg = t.inventory 
          then 
            case when @sr <> t.int_proc
            then  @r+1 
            else @r end 
          else 1 end 
      else 1 end r,
@g:= t.customer_id g,
@sg:= t.inventory sg,
@sr:= t.int_proc sr
from test t
cross join (select @g:=null,@sg:=null,@r:=null,@sr:=null) t1
  where t.customer_id in('A','B')
order by t.customer_id,t.inventory,t.int_proc desc
  ) t2
 where t2.r <= 2
order by id

这篇关于从表中选择2条最新的“组/批次"记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！