MySQL5.7 over partition by

获取 code 分组中 max(version) max(batch) 最大的 记录

 

```
CREATE TABLE t (
  `id` int(11) ,
  `code`  int(11) ,
  `version` int(11) ,
  `batch`  int(11) 
)

insert into t values(1, 1, 1, 1);
insert into t values(2, 1, 2, 2);
insert into t values(3, 1, 3, 3);
insert into t values(4, 2, 1, 1);
insert into t values(5, 2, 1, 1);
insert into t values(6, 3, 1, 1);
insert into t values(7, 3, 1, 3);

select id,code,version,batch
from
(select
t_tmp.code,
t_tmp.version,
t_tmp.batch,
t_tmp.id,
@rownum:=@rownum+1,
if(@pdept=t_tmp.code,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=t_tmp.code
from
(select code,id,version,batch
from t
order by code,version desc, batch desc
) t_tmp ,
(select @rownum :=0,@pdept := null ,@rank:=0) a
) a where rank=1
;

---  解释


select id,code,version,batch
from
  (
  select
    t_tmp.code,
    t_tmp.version,
    t_tmp.batch,
    t_tmp.id,
    -- 每行+1
    @rownum:=@rownum+1,
    -- 如果变量的值等于code的值 排名+1， 如果不等于就是1(@pdept记录的是上一行的code值)
    if(@pdept=t_tmp.code,@rank:=@rank+1,@rank:=1) as rank,
    -- 这个变量的值等于行中code的值
    @pdept:=t_tmp.code
  from
    (select code,id,version,batch
    from t
    order by code,version desc, batch desc
    ) t_tmp ,
  -- mysql定义变量 下面这句话，初始化下面变量的值
  (select @rownum :=0,@pdept := null ,@rank:=0) a
  ) a 
  -- 排名第一的选出来
  where rank=1;