Mysql如何先排序再进行分组，确保分组后留下的数据是排序第一条

帅瑞瑞

Mysql如何先排序再进行分组，确保分组后留下的数据是排序第一条 如题

解决方案

1.手写ROW_NUMBER() 然后只取第一条

select IF(@P = main_licence, 2, 1) AS RN ,
       (@P := main_licence)      AS RNAME,
       main_licence,
       id
from `danger_waybill`
having RN = 1
ORDER BY main_licence,del_update_time desc;

@P 为用户变量作用域为当前连接。
先按照车牌号排序 确定同一个车的信息都在一起,再按照更新时间排序 确认同一个车的最新数据都在第一条.
第一个字段IF(@P = main_licence, 2, 1) 当@P等于当条的车牌号就返回2,否则返回1.
第一条数据车牌号为ABC时@P=NULL 所以返回1
第二个字段(@P := main_licence)  AS RNAME 此时将@P设为当条车牌号即ABC
所以此后该判断IF(@P = main_licence, 2, 1) 车牌号=ABC的都返回2
第二个车牌号为BBC此时@P为ABC 所以返回1
即取RN=1的数据能取出每个车更新时间最新的第一条数据

GROUP_CONCAT 后 SUBSTRING_INDEX

select SUBSTRING_INDEX(GROUP_CONCAT(id order by del_update_time desc), ',', 1) as id,main_licence
from `danger_waybill`
group by main_licence;

使用GROUP_CONCAT函数将ID字段按照更新时间字段排序后拼接到一起
结果为1,2,3此时通过SUBSTRING_INDEX截取第一个逗号前的id,即更新时间最新的一条ID

先排序 子查询加Limit

select *
from (select main_licence, waybill_no, id
      from danger_waybill
      order by del_update_time desc
      limit 999999999) as t
group by main_licence;

利用limit禁止掉mysql自带的sql优化

性能测试

方法1耗时	方法2耗时	方法3耗时
7 s 608 ms	7 s 421 ms	10 s 474 ms
7 s 534 ms	7 s 118 ms	10 s 425 ms
7 s 442 ms	7 s 272 ms	10 s 368 ms

结论:方法1耗时低一些.
警告:该测试不严谨

鸣谢

第一个方法提供者 @tmdgdx
第二个方法提供者 @z746090883 @a11
第三个方法提供者 @final_vip @Piz.liu
问题解决T一下 @小豪威武

小豪威武

你排序和分组用的是同一个字段嘛

beize1226

可以先将排序后的作为一张临时表，在临时表中重新分组并使用`limit`保留数据集
新手小白观点，有错误的地方请多多指教

帅瑞瑞

小豪威武 发表于 2023-2-2 20:27
你排序和分组用的是同一个字段嘛

不是，排序是时间

帅瑞瑞

beize1226 发表于 2023-2-2 20:36
可以先将排序后的作为一张临时表，在临时表中重新分组并使用`limit`保留数据集
新手小白观点，有错误的地 ...

是每个分组都保留一条 limit应该不行吧

小豪威武

帅瑞瑞 发表于 2023-2-2 20:37
不是，排序是时间

那你有答案后 踢我一下

小豪威武

beize1226 发表于 2023-2-2 20:36
可以先将排序后的作为一张临时表，在临时表中重新分组并使用`limit`保留数据集
新手小白观点，有错误的地 ...

分组前就保留一个的话 分组还有什么意义

yezhengw111

[SQL] 纯文本查看 复制代码

SELECT  *
    FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY 分组的字段(多个用逗号隔开) ORDER BY 要排序的字段,多个用逗号隔开如: year DESC ,month ASC ) AS RowNum ,
其他字段1,
其他字段2
              FROM      表名
            ) tmp
    WHERE   RowNum = 1

tmdgdx

..................

帅瑞瑞

yezhengw111 发表于 2023-2-2 22:05
[mw_shl_code=sql,true]SELECT  *
    FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY 分组的字段 ...

我们mysql版本还不支持这几个函数