mysql5.7 不支持开窗函数。
具体需求是, 只group by一个字段,但是其余的没有参与group by 的字段也需要返回,组内记录值取第一行就可以。
表记录如下,以group by ROVER_ID 为例子:
- 一种比较好办的情况,记录有自增主键,那么取组内最大主键值就可以
select * from main_account_relation where id in (select max(id) from main_account_relation group by ROVER_ID);
- 如果没有类似自增主键的话,如下也行
select * from(
select
@rn:= case when @rover_id = ROVER_ID then @rn+1 else 1 end as rn,
@rover_id:=ROVER_ID as rover_idn,am,COMMENT,district,CREATION_DATE
from
(select * from main_account_relation order by rover_id desc) relation,
(select @rn=0) vars
) as bb
where bb.rn = 1;