Mysql 查询某条记录的行号(排序后)

假设有个表,有以下数据

假设:要求按着f_total_power充电量排序,充电量相同的情况下按着创建时间降序排(排名不存在并列)

1.先按着要求排序

先按着要求排好序
select tt.f_id,tt.f_team_name,tt.f_total_power,tt.f_create_time from t_fleet_team tt where tt.f_activity_id=99 ORDER BY tt.f_total_power desc,tt.f_create_time asc

2.排序后要获取每行行号

利用中间变量@rowno计算行号
select temp.f_id,temp.f_team_name,temp.f_total_power,temp.f_create_time,(@rowno:=@rowno+1) as no from (
select tt.f_id,tt.f_team_name,tt.f_total_power,tt.f_create_time from t_fleet_team tt where tt.f_activity_id=99 ORDER BY tt.f_total_power desc,tt.f_create_time asc
)temp,(select (@rowno:=0)) tnum

3.查询某一个ID的行号

利用子查询 查询某条数据的行号
select t.no from (
select temp.f_id,temp.f_team_name,temp.f_total_power,temp.f_create_time,(@rowno:=@rowno+1) as no from (
select tt.f_id,tt.f_team_name,tt.f_total_power,tt.f_create_time from t_fleet_team tt where tt.f_activity_id=99 ORDER BY tt.f_total_power desc,tt.f_create_time asc
)temp,(select (@rowno:=0)) tnum
)t where t.f_id=3;

假设:要求按着f_total_power充电量排序,并考虑并列排名--------

select count(DISTINCT(tt.f_total_power))+1 as no from t_fleet_team tt where tt.f_total_power > (select temp.f_total_power from t_fleet_team temp where temp.f_id=3)

oracle参考链接:

https://blog.csdn.net/shaiguchun9503/article/details/82349050

https://blog.csdn.net/qq_25221835/article/details/82762416?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase