订单表字段:id,userId,用户表字段:id
实现SQL:
SELECT COUNT(1)+1 as '排名' FROM (SELECT COUNT(o.id) AS order_count FROM rf_order o INNER JOIN rf_user u ON u.id = o.userId AND u.deleted = 'Normal' WHERE o.deleted = 'Normal')a WHERE order_count> ( SELECT COUNT(o.id) AS order_count FROM rf_order o INNER JOIN rf_user u ON u.id = o.userId AND u.deleted = 'Normal' WHERE o.deleted = 'Normal' AND u.id = 10 ) |
实现的思想,计算排名,就是计算接单量比它本人高的有几个,然后加1就是它自己的排名。
比如根据分数统计学生的排名:
select count(1)+1 as '排名'
from test
where fenshu>(select fenshu from test where name='学生名字')